Results 1 to 11 of 11

Thread: Relink Tables Programmatically

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2008
    Posts
    46

    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

  2. #2
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Relink Tables Programmatically

    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*

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2008
    Posts
    46

    Re: Relink Tables Programmatically

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

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

  4. #4
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Relink Tables Programmatically

    Quote Originally Posted by Zephaneas View Post
    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

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2008
    Posts
    46

    Re: Relink Tables Programmatically

    Thank you

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2008
    Posts
    46

    Re: Relink Tables Programmatically

    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

  7. #7
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Relink Tables Programmatically

    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.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2008
    Posts
    46

    Re: Relink Tables Programmatically

    Quote Originally Posted by Ecniv View Post
    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"

  9. #9
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Relink Tables Programmatically

    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.

  10. #10

    Thread Starter
    Member
    Join Date
    Oct 2008
    Posts
    46

    Re: Relink Tables Programmatically

    Thanks!

  11. #11
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Relink Tables Programmatically

    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
    do not put off till tomorrow what you can put off forever

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width