Refreshing Linked Tables

[aziz rasul]

I have a FE db which links to temporary BE db. However when I have created working code, I want to programmatically link to the LIVE BE db and then relink back to the temporary BE db.

I have copied the following code from this forum and experimenting with a couple of Nwind.mdb tables, but get a debug error 3420 i.e. 'Object invalid or no longer set' on the tbldef.Connect = ";DATABASE=" & dbPath line?

Public Sub reLinkTables(dbPath As String)

    Dim tbldef As DAO.TableDef
    Dim tblName As Variant
    Dim arrTables As Variant
    
    arrTables = Array("Employees", "Shippers")
    
    For Each tblName In arrTables
        Set tbldef = CurrentDb.TableDefs(tblName)
        tbldef.Connect = ";DATABASE=" & dbPath
        tbldef.RefreshLink
    Next tblName
    
End Sub

 

[Bodisathva]

make sure you dynamically build the dbPath statement, i.e. your My Documents folder is not the same location as the next user's My Documents folder. Also watch for differences in mapped drives, I'd reccomend using the UNC address for network assets instead of drive letters.

 

[aziz rasul]

So if it's the C drive, what would be the UNC path. It says Local Disk against the C and D drives?

 

[Bodisathva]

"Local" drives do not change, nor do you need UNCs, only Network Drives.

 

[aziz rasul]

So why am I getting the error?

 

[Bodisathva]

So why am I getting the error?

I get this error between users. The local repository location, in my case, is within the user's My Documents folder, which exists in C:\Documents and Settings\USERNAME\My Documents ...the USERNAME portion must be assembled prior to relinking the tables on application startup.

Essentially the error leads me to believe that either the table doesn't exist in the databse (relinking is not the same as linking the table the first time) or that there is a problem with the path statement.

 

[WayneRyan]

aziz,

This should be closer:

Public Sub reLinkTables(dbPath As String)

Dim tbldef As DAO.TableDef

For Each tbldef In CurrentDb.Tabledefs
   If Len(tbldef.connect) > 0 Then
      tbldef.Connect = ";DATABASE=" & dbPath
      tbldef.RefreshLink
   End If
   Next tbldef
End Sub

Wayne

 

[aziz rasul]

Many thanks Wayne. That did the trick.