Automatically re-linking tables (VBA)

[Monardo]

Hello

This is not a question on how to, but more about your expert opinion.

As you all know tables are linked using absolute path and if you change back/front-end location one needs to re-link the table manually. I have been searching around on how to do it automatically when both files are in the same directory and I did find some solutions, which were somewhat complicated.

And then I realized that I have a very simple solution. What I do is on every start of the front-end I delete all linked tables (On open event of a Main Menu form) and then create link again using something like this:

Private Sub Form_Open

Dim CurPath As String
CurPath = CurrentProject.Path

DoCmd.DeleteObject acTable, "t_table"
DoCmd.TransferDatabase acLink, "Microsoft Access", CurPath & "\BackEnd.mdb", acTable, "t_table", "t_table"

End Sub

It works very well and I have been using this for my own purposes for long.

Now the question: Do you foresee any problems with this solution?

I ask this because I am planning to implement this in somebody else's project and was surprised that have never seen this simple solution offered by others elsewhere.

 

[namliam]

Deleting and creating the linked tables may can and eventually will cause issues.

The best/better solution is to refresh the link to the new location, leaving the table itself in place but only replacing the actual linking information. I have that code lingering on the forum someplace, also have it in many a DB though at the moment I dont really have access to them (left my repository DB on my USB Drive @ work

I can post some of the code tomorow if you like, though you can probably find some simular sample with some googling.

 

[spikepl]

Open a query referencing deleted tables and you'll have lots of "fun".

I agree with namliam, and here is relink code
http://www.access-programmers.co.uk/forums/showthread.php?t=227871

 

[Monardo]

Thanks for reply namliam

Could you elaborate what kind of issues will appear.

Just to clarify, I am not deleting actual tables with data in back-end. I am deleting only links in front-end.