How to handle changing drive letters in table references?

[securitywyrm]

SOLUTION FOUND: https://www.itworld.com/article/2694...n-windows.html . If it's looking in the Y drive which doesn't exist for things in the S drive, I just create a "Net use S: Y:" to create a phantom Y drive that takes it to the correct location.

I'm trying to build a database for people both at home (telework) and at the office to use. However there's a big issue with the location of the data people are pulling from. For those at the office, the location is Y:\Education\Databases . For those at home, it's S:\Education\Databases . As I built this at work, the database won't work for those at home because it's looking for the Y drive.


Current options
1. Create a separate version of the forms file for home and work. This would be very difficult to maintain version control, because I effectively have to double my work when I do upgrades.


2. Find a way to have the linked files search multiple locations
3. Have the linked file location involve "currentpath" somehow and keep all the forms in the same folder.

Advice would be appreciated!

[aytee111]

NEVER use drive letters, use the full path.

[securitywyrm]

What do you mean by "full path"? So y:\Education\databases\fiscaldata.acdb ?

[aytee111]

Find out what "y" represents:\\network drive name\folder1\...\Education\Databases

[JoeM]

Drive letters are really just shortcuts to the network file path. You can assign any drive letter to any network path. He is saying to use the network path.
If you look at the drive in Windows Explorer, it will show you what the full network path is.

[securitywyrm]

Drive letters are really just shortcuts to the network file path. You can assign any drive letter to any network path. He is saying to use the network path.
If you look at the drive in Windows Explorer, it will show you what the full network path is.

When I created the links I navigated the file structure to the relevant file and double clicked. Where would I go to put in the 'network path'?

[aytee111]

In File Explorer, the path is usually listed next to the letter. Open a second File Explorer and type in the path as it is written and navigate to your folder. Then copy the path as it is.

[June7]

'Full path' is also called UNC - Universal Naming Convention - path.

I use a VBA procedure to rename links with UNC path.

Public Sub NormalizeTableLinks()
'Relink tables with UNC pathing
'Use the variables provided to do the manipulations desiredDim td As TableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String
'replace the following strings as needed
strOld = "old path"
strNew = "new path"
Set db = CurrentDb
For Each td In db.TableDefs
    If InStr(td.Connect, strOld) > 0 Then
        Debug.Print td.Name
        Debug.Print "Old Link: " & td.Connect
        td.Connect = Replace(td.Connect, strOld, strNew)
        td.RefreshLink
        Debug.Print "New Link: " & td.Connect
    End If
Next td
db.TableDefs.Refresh
End Sub

[Huddle]

'Full path' is also called UNC - Universal Naming Convention - path.

I use a VBA procedure to rename links with UNC path.

Public Sub NormalizeTableLinks()
'Relink tables with UNC pathing
'Use the variables provided to do the manipulations desiredDim td As TableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String
'replace the following strings as needed
strOld = "old path"
strNew = "new path"
Set db = CurrentDb
For Each td In db.TableDefs
    If InStr(td.Connect, strOld) > 0 Then
        Debug.Print td.Name
        Debug.Print "Old Link: " & td.Connect
        td.Connect = Replace(td.Connect, strOld, strNew)
        td.RefreshLink
        Debug.Print "New Link: " & td.Connect
    End If
Next td
db.TableDefs.Refresh
End Sub

In your VBA is "old link" and "new link" the actually paths or is that how the script reads. I'm not VBA savey and usually just copy and paste the script.

[JoeM]

You would substitute your actual paths in where "old path" and "new path" are shown in the code.

[June7]

Paths will have the following structure:

oldPath would be the old drive letter reference, like: "C:\folders path here\dbname.accdb"

newPath would be the UNC reference, like: "\\servername\folders path here\dbname.accdb"

Again, substitute with real names as needed. Review posts 3 and 4. A drive letter mapping can represent a location deep in the file structure so it is not a direct replacement of "C:" with "\\servername". Have to define the 'full path' after the servername.