Option Compare Database
Option Explicit

Public Function LinkODBCTables()

Dim dbODBC As Database
Dim tdTables As TableDef
Dim tdfAccess As TableDef

Dim connectLength As Integer
Dim myConnectStr As String
Dim localTabName As String

connectLength = Len(CurrentDb.TableDefs(0).Connect)

If (connectLength > 1) Then
  myConnectStr = Left(CurrentDb.TableDefs(0).Connect, connectLength)
End If

On Error GoTo ODBC_Error
Set dbODBC = DBEngine.Workspaces(0).OpenDatabase("", 0, False, myConnectStr)


If Not (dbODBC Is Nothing) Then
  On Error Resume Next
  For Each tdTables In dbODBC.TableDefs
    'local table name is not allowed to contain "."
    localTabName = Replace(tdTables.Name, ".", "_")
    Set tdfAccess = CurrentDb.CreateTableDef(localTabName, dbAttachSavePWD, tdTables.Name)
    tdfAccess.Connect = dbODBC.Connect
    CurrentDb.TableDefs.Append tdfAccess
  Next tdTables
End If
    
'Application.SetOption "Show Hidden Objects", True
'Application.SetOption "Show Hidden Objects", False
CurrentDb.TableDefs.Refresh
Application.RefreshDatabaseWindow

ODBC_Exit:
    If Not (dbODBC Is Nothing) Then
        dbODBC.Close
        Set dbODBC = Nothing
    End If
    Exit Function

ODBC_Error:
    MsgBox Err.Description
    Resume ODBC_Exit
End Function



Public Function UnlinkODBCTables()

Dim tdTables As TableDef

For Each tdTables In CurrentDb.TableDefs
  If (tdTables.Attributes And dbAttachedODBC) <> 0 Then
    CurrentDb.TableDefs.Delete tdTables.Name
  End If
Next tdTables

End Function