Relink Tables Programmatically

I'm working on an Access 365 app for a client. At their office their tables are linked to a back end database. When I copy the app to my PC I need to relink the tables to the DB on my PC.

I have written this code:

Code:

---

Public Sub LinkTablesLocal()

    Dim computerName As String
    computerName = Environ("computername")

Stop

    If computerName = "MY_PC" Then
   
        Dim databaseFile As String
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim tableName As String
        Set dbs = CurrentDb()
       
        databaseFile = CurrentProject.Path & "\MyApp.accdb"
       
        For Each tdf In dbs.TableDefs
       
            If Len(tdf.Connect) > 1 Then
           
                If tdf.Connect <> ";DATABASE=" & databaseFile Then
                   
                    If Left(tdf.Connect, 4) <> "ODBC" Then
                   
                        tableName = tdf.Name
                        dbs.TableDefs(tableName).Connect = ";DATABASE=" & databaseFile
                        dbs.TableDefs(tableName).RefreshLink
                       
                    End If
                   
                End If
               
            End If
           
        Next tdf

    End If

End Sub

---

The code fails on the RefreshLink command with "The Microsoft Access database engine cannot find the input table or query 'tblAudit'. Make sure it exists and that its name is spelled correctly."

The table 'tblAudit' does exist in the database.

First, is this the right way to do this? If so, what's wrong here?

Many thanks

My approach is to DoCmd.DeleteObject acTable, Table
first.

and then relink using CurrentDb.TableDefs.Append CurrentDb.CreateTableDef(TableName, 0, RemoteTableName, ODBC)

I don't use refreshlink *shrugs*

What does My approach is to DoCmd.DeleteObject acTable, Table do? Delete the table??

If so, why would I want to delete the table?

Quote:

---

Originally Posted by Zephaneas

What does My approach is to DoCmd.DeleteObject acTable, Table do? Delete the table??

If so, why would I want to delete the table?

---

https://answers.microsoft.com/en-us/...c-68b599b31bf5

Thank you

Can you please provide an example of relinking? I know very little about VBA programming (I'm a C#.Net guy) and I need to get this working. I tried plugging in the command you provided but it doesn't work. I really am coding blind here.

Thanks

I think your inital code should work except that the path has to be the path the computer uses not the nice one that windows says.. ie if you have any spaces in the path I dont think it would work...

Easy to test though.. if you have a dev folder on your c drive, it should connect to that.

Use the break point debugging (on the column to the left of the code, click once, a ball should appear and the line highlights). When you execute the code pauses at that breakpoint and you can use the immediates window (ctrl+g) or hover over the variables to confirm that values are correct.

Quote:

---

Originally Posted by Ecniv

I think your inital code should work except that the path has to be the path the computer uses not the nice one that windows says.. ie if you have any spaces in the path I dont think it would work...

Easy to test though.. if you have a dev folder on your c drive, it should connect to that.

Use the break point debugging (on the column to the left of the code, click once, a ball should appear and the line highlights). When you execute the code pauses at that breakpoint and you can use the immediates window (ctrl+g) or hover over the variables to confirm that values are correct.

---

The path I'm using is the actual path, including the database file - "E:\Projects\MyApp\MyDb.accdb"

here's a couple routines from my Access Library.

Code:

---


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function TableExists(ByVal TableName As String) As Boolean
'  Does a Table exist?
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    TableExists = Not IsNull(DLookup("Name", "MSysObjects", "Name='" & TableName & "' AND Type IN(1,4,6)"))
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function LinkTable( _
    ByVal ODBC As String, _
    ByVal TableName As String, _
    Optional RemoteTableName As String) As Boolean
'  Create a linked Table
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If TableExists(TableName) Then Exit Function
    If LenB(RemoteTableName) = 0 Then RemoteTableName = TableName
    CurrentDb.TableDefs.Append CurrentDb.CreateTableDef(TableName, 0, RemoteTableName, ODBC)
    LinkTable = TableExists(TableName)
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function UnLinkTable(ByVal Table As String)
'  Drop a table link
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If TableExists(Table) Then
        If LenB(CurrentDb.TableDefs(Table).Connect) Then
            DoCmd.DeleteObject acTable, Table
        End If
    End If
End Function

---

ODBC is the connection string. (ODBC is because I typically link to SQL Server tables)

I'm not sure why your original function isn't working.
My best guess is that you're not providing a correct connection string pointing to your file,
and that's why it's really breaking on the RefreshLink.

Thanks!

i succeeded in replicating the error
have a database with a table say: 'table1'
create a link to that table (in the same database)
rename that link to 'table1'
the original 'table1' will now be deleted
so now there is a linked table 'table1' , that links to the no longer existing 'table1'

did this in access 2003