Programmatically change the connection of a linked table in ms access

Question

I have already referenced other pages for my problem but I still can't get this to work. I feel a bit slow given that I have three examples below and still can't figure this out.

Changing linked table location programatically

Linked table ms access 2010 change connection string

Update an Access linked table to use a UNC path

Here is the code that I am using:

Dim tdf As TableDef
Dim db As Database
Set db = CurrentDb
Set tdf = db.TableDefs("DeviceListT")
tdf.Connect = "ODBC;DATABASE=" & CurrentProject.path _
                               & "\HarmonicProfileDatabase_be.accdb"
tdf.RefreshLink

The problem is that when I run it a window pops up.

I am not exactly sure what I am supposed to do with that nor do I want it to pop up in the first place as I will be giving the ms access files to someone else and they won't know what to do with this window either.

Answer 1

You are using SQL Server references but linking MS Access. For MS Access, you do not need an ODBC link, just refer to DATABASE:

DBFile = CurrentProject.path & "\HarmonicProfileDatabase_be.accdb
''Check the file exists
strFile = Dir(DBFile)
If strFile <> "" Then
    With CurrentDb
        For Each tdf In .TableDefs
            ''Check that this is a linked table
            ''It can be useful to use table of tables instead
            If tdf.Connect Like "*HarmonicProfileDatabase_be.accdb*" Then
                tdf.Connect = ";DATABASE=" & DBFile 
                tdf.RefreshLink
            End If
        Next
    End With
    MsgBox "Link HarmonicProfileDatabase_be.accdb" 
Else
    MsgBox "Problem"
End If

You could also use:

 sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & DBFile & ";Jet OLEDB:Database Password=pw;"