Reconnect MDB Table via VBA

[Monsora83]

Hi all,

I searched the old threads and found this from 2009. I am trying to change the source connection of a linked table through vba. This one is currently set up for an ODBC connection, but the whole tdf.connect part to change which MDB database to use on a network is right over my head.

Dim myDB As Database, tdf As TableDef
Set myDB = CurrentDb
Set tdf = myDB.TableDefs("dbo_Categories") 
tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=(local);DATABASE=Northwind;Trusted_Connection=Yes"
tdf.RefreshLink

I'll keep on looking around and post an answer if I find one.

Thanks

 

[Monsora83]

Hi all,

I searched the old threads and found this from 2009. I am trying to change the source connection of a linked table through vba. This one is currently set up for an ODBC connection, but the whole tdf.connect part to change which MDB database to use on a network is right over my head.

Dim myDB As Database, tdf As TableDef
Set myDB = CurrentDb
Set tdf = myDB.TableDefs("dbo_Categories") 
tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=(local);DATABASE=Northwind;Trusted_Connection=Yes"
tdf.RefreshLink

I'll keep on looking around and post an answer if I find one.

Thanks

I found something that works.

Private Sub Command53_Click()
   Dim dbsTemp As Database
   Dim strMenu As String
   Dim strInput As String
   ' Open a Microsoft Jet database to which you will link
   ' a table.
   Set dbsTemp = CurrentDb
   ' Call the ConnectOutput procedure. The third argument
   ' will be used as the Connect string, and the fourth
   ' argument will be used as the SourceTableName.
         ConnectOutput dbsTemp, _
            "NewLinkedTableName", _
            ";DATABASE=c:\File.mdb", _
            "TableToBeLinkedFrom"
End Sub
Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)
   Dim tdfLinked As TableDef
   ' Create a new TableDef, set its Connect and
   ' SourceTableName properties based on the passed
   ' arguments, and append it to the TableDefs collection.
   Set tdfLinked = dbsTemp.CreateTableDef(strTable)
   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable
   dbsTemp.TableDefs.Append tdfLinked
End Sub