Setup a linked table in Access for a path which doesn't exist yet?

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.

Why don't you delete the linked table and recreate it instead of relying on RefreshLink? — ta.speot.is, Feb 12 '14 at 21:58
@ta.speot.is so how do we recreate it with a path which doesn't yet exist ... that's what I'm asking — hawbsl, Feb 13 '14 at 00:52
I am simply remarking if you are editing an existing linked table in place and `RefreshLink` is not working then you should try deleting the linked table and recreating it, neither of which involve a call to `RefreshLink`. http://www.microsoftaccessexpert.com/Microsoft-Access-Code-LinkTable.aspx — ta.speot.is, Feb 13 '14 at 02:34
@ta.speot.is i've tested the code you link to. admittedly the code you link to doesn't use `.RefreshLinks` but it just as surely tries to validate the path on the line `myDB.TableDefs.Append` and fails to create the linked table if the path doesn't exist — hawbsl, Feb 13 '14 at 09:26
Well if it's critical to do this then perhaps you could mess around with the system tables and create/edit your linked table there. No guarantee it will work, though. http://stackoverflow.com/questions/3994956/meaning-of-msysobjects-values-32758-32757-and-3-microsoft-access — ta.speot.is, Feb 13 '14 at 09:41
I am somewhat puzzled in that you will have to write code to link the tables, unless you intend to be there for every change, so why not start with a small link form if the link is not found? For example http://stackoverflow.com/questions/4242063/refreshing-linked-tables-in-access/4244586#4244586 — Fionnuala, Feb 13 '14 at 18:14
@Remou there's no Access on the target machine and never will be ... so i have no VBA on the target machine. unless i am being dumb your answer involves executing VBA on the target machine — hawbsl, Feb 19 '14 at 09:54
In that case, what are the tables linked to? It looks like you are linking in MS Access. — Fionnuala, Feb 19 '14 at 10:16
@Remou Access tables linking to other Access tables but with no Access front end. Front end is an application which uses JET to pull the data from the .mdb files. — hawbsl, Feb 19 '14 at 10:19

Gord Thompson · Answer 1 · 2014-02-13T09:28:17.007

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.

Gord, your answer looks like a neat solution and maybe i was doing something dumb but i couldn't get it to work — hawbsl, Feb 19 '14 at 09:51

score 1 · Accepted Answer · answered Feb 19 '14 at 10:00

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.

Setup a linked table in Access for a path which doesn't exist yet?

2 Answers2

Linked