Table Re-Linking in Runtime Giving Error

[connort1234]

I have created code to relink my database to the back end from a form. The form has a field linked to a table that stores the back end file path, it uses a browse button to locate the browse button and then a Re-Link button to relink the tables to the back end at the new location.

The code works perfectly in Access and I can relink tables no problem, but when I open the database in access runtime (on another computer) and try run the code, it gives a runtime error and closes the database. The back end sits on a server and I am trying to get the tables to relink to the back end from a runtime version of access.

This is the code I used:

Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim NewPathname As String

Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs

NewPathname = DFirst("[BackEndFilePath]", "Settings")

For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
        Tdf.Connect = ";DATABASE=" & NewPathname
        Tdf.RefreshLink
    End If
Next

MsgBox "All the application tables have been relinked to the database at " & NewPathname & " Thank you...", vbInformation, "Tables ReLinked"

 

[Cronk]

Nothing appears obviously wrong in your code. The best method is to add error handling - see http://allenbrowne.com/ser-23a.html

In the error handling code, add

debug.print error

The error message might give a clue as to the issue.

 

[sneuberg]

We've use the basJStreetAccessRelinker for this. This has error handlers in it that might tell you what your problem is or if it works without giving you an error you could just use this in place of what you got.

 

[connort1234]

Thank you sneuberg, the code you gave me works in runtime, everything is working now

 

[informer]

If you want to query a back end database with a Sql script go here http://www.utteraccess.com/forum/index.php?showtopic=2037433&view=findpost&p=2601558

 

[connort1234]

It seems to be working now but I get errors that one or two out of the 9 tables in the database weren't relinked and are still connected to the previous back end, however the rest are relinked no problem. Any ideas as to why this is happening?

 

[sneuberg]

In every case where that's happened to me using the basJStreetAccessRelinker it was because the tables were missing in the backend I was relinking too. Or more precisely there were link tables in the frontend that didn't exists in the backend. Were some tables deleted or renamed? Do you get the same results if you use the Link Table Manager?

 

[sneuberg]

On the other hand if you mean that it's not relinking tables that actually exist in the previous backend it's because it doesn't relink tables with good links. To get it to relink them you can make them bad links by renaming the folder the previous backend is in or renaming the previous backend database.