Linked Tables and UNC

[JoeM]

I have an automated database. It is usually run off of a scheduler, but sometimes I need to kick it off manually from my computer.

My issue is that the server that the scheduled job runs from has different drive mappings than my personal computer. This is creating an issue with many linked tables I have set up (this is NOT a split database set-up, I have links to 15 different databases). 14 of these databases are found in the same path (these are various .dbf files), and the last one resides in a different folder (this is another .accdb Access database).

If I could set up these links to use UNC, everything would work fine. But alas, that does not appear to work. Access seems intent on assigning a drive letter to each one. Does anyone know how I can accomplish this?

I have been searching around the web, but most of what I have found refers to split Access databases (so every table is linked to a single known database name), or require user interaction at run-time (prompts), which wouldn't work for an automated job. I'll keep searching, but if anyone can help point me in the right direction, it would be greatly appreciated!

Thanks

[pbaldy]

How are you linking? I just tested and had no problem linking with a UNC path (2007). If you browse through a mapped drive, Access will link it that way. You probably have to browse through Network Places or type in the path.

[June7]

I use VBA to assign UNC path to table links. Review http://forums.aspfree.com/microsoft-...ue-323364.html

[JoeM]

How are you linking? I just tested and had no problem linking with a UNC path (2007). If you browse through a mapped drive, Access will link it that way. You probably have to browse through Network Places or type in the path.

I was doing it through the Linked Table Manager. I had thought the same thing, and had tried going through "Network Places", but for whatever reason, it didn't seem to stick. I suspect that I must have done something wrong, because I tried again, and it worked the second time.
I place the blame on "Monday"...

I use VBA to assign UNC path to table links. Review http://forums.aspfree.com/microsoft-...ue-323364.html

June, thanks for your reply too. It is good to see how to do that too, as I may have a need for that someday.

Thanks all!

[pbaldy]

Dang Mondays!

[June7]

Link in post 3 is dead. Here is the code it presented:

Public Sub NormalizeTableLinks()'Relink tables with UNC pathing
'Use the variables provided to do the manipulations desired


Dim td As tableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String


'change Excel sheet connection string to enable data edit
''strOld = "IMEX=2"
''strNew = "IMEX=0"


'replace the following strings as needed
strOld = "C:\"
strNew = "\\YourURL\path"


Set db = CurrentDb
For Each td In db.TableDefs
    If td.Connect <> "" Then
        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
    End If
Next td
db.TableDefs.Refresh


End Sub