1

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.

Improve this question
9
  • Why don't you delete the linked table and recreate it instead of relying on RefreshLink? Commented Feb 12, 2014 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 Commented Feb 13, 2014 at 0: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. microsoftaccessexpert.com/Microsoft-Access-Code-LinkTable.aspx Commented Feb 13, 2014 at 2: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 Commented Feb 13, 2014 at 9: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. stackoverflow.com/questions/3994956/… Commented Feb 13, 2014 at 9:41

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Gord, your answer looks like a neat solution and maybe i was doing something dumb but i couldn't get it to work
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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.