vba - Update an Access linked table to use a UNC path

Question

I have an Access 2010 database A.mdb with a list of tables, one of which is a linked table, linked from another Access database B.mdb on the same server. These databases are on a development machine xxx.xxx.xxx.xxx, which is mapped on my computer as R://, and they are afterwards published online on a yyy.yyy.yyy.yyy server.

If I want to work on the database locally, I need to change the link to the table. But if I change it via filesystem (using the "Linked Table Manager"), the link becomes R://.... and when I look at the ASP page that requests those data, it is broken because the path is wrong. Also, if I change the link locally, it won't work on the online server.

Is there a way to change the link "programmatically"? That is, without using the Linked Table Manager?

I searched for an answer, but I am not that expert, I just understood that I have to write a "Module"? "Macro"?

Answer 1

Table links can be UNC paths. For example, say I have a linked table pointing to a database on \192.168.1.2Public which is mapped to drive P:. If I launch the VBA editor (Alt+F11), open the Immediate Window (Ctrl+G) and type...

?CurrentDB.TableDefs("remoteTable").Connect

...it will return...

;DATABASE=P:B.accdb

...because I pointed to drive P: when I created the link.

Now if I create and run the VBA function...

Function linkToUnc()
Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.TableDefs("remoteTable").Connect = ";DATABASE=\192.168.1.2PublicB.accdb"
cdb.TableDefs("remoteTable").RefreshLink
Set cdb = Nothing
End Function

...the link is now a UNC path.

By the way, you can create UNC links in the Linked Table Manager if you browse to "Network", "machine name", "share name", but that will give you the machine name (in my case \PICOPublicB.accdb).