Setup a linked table in Access for a path which do

Question

How can we setup a linked table where the path doesn't exist yet?

We need to setup a linked table in Access for eventual use in a customer environment which doesn't have MS Access installed (the Access database is simply utilised as a backend via Jet).

We can use Jet & DDL on the customer's site to create/edit/drop tables in their .mdb but as far as I can tell you can't create linked tables using DDL.

In theory we can create the linked table here (where we have Access) and specify the "invalid" path using VBA like so (assume Kilimanjaro is the customer's server which doesn't exist on our network):

Function SetLinkedTablePath(tableName As String)
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    cdb.TableDefs(tableName).Connect = ";DATABASE=\\Kilimanjaro\Foo-Data.mdb"
    cdb.TableDefs(tableName).RefreshLink
    Set cdb = Nothing
End Function

However the .RefreshLink line attempts to validate the path so fails on our network.

If we comment out the .RefreshLink line and run it it executes fine, but checking Linked Table Manager in Access the new path hasn't taken.

Any solution to this? We'd prefer not to use a mapped drive.

Answer 1

In the end we took a spare virtual machine on our network and temporarily renamed it to match our client's server (call it \\Kilimanjaro for example). Then we could create the linked table without any problem at all.

It's unsatisfactory and inelegant. But I'm going to accept this answer as it was our solution but if anyone ever comes up with a neater trick, I'd love to hear it.

Answer 2

If you don't have an actual server named Kilimanjaro on your local network then you could edit the hosts file on your machine to "fool" it into thinking that there is one. I just tried this with Access 2010 and it worked for me:

I opened a Windows command prompt "as Administrator" and issued the command

notepad C:\Windows\System32\drivers\etc\hosts

I added a line at the end of the file like so

192.168.1.109   Kilimanjaro

where 192.168.1.109 is the IPv4 address of my local test server (whose real name is something different). I saved the changes and exited Notepad.

Then in Access I was able to tweak the .Connect property of a linked table to point to \\Kilimanjaro like so:

Dim cdb As DAO.Database, tbd As DAO.TableDef
Set cdb = CurrentDb
Set tbd = cdb.TableDefs("TableX")
tbd.Connect = ";DATABASE=\\Kilimanjaro\Public\Database1.accdb"
tbd.RefreshLink
Set tbd = Nothing
Set cdb = Nothing

Since there really was a file named Database1.accdb on that UNC path Access had no problem with it, and I could open the linked table in Datasheet view as usual.