scripting linked table manager

[galorin]

I'm needing to program my app to connect to one of 2 MySQL databases, either a remote database (Master) or localhost (Slave, no writes possible)

Most everything is done via ADODB, but I have various fields that pull data as a RowSource property, etc that would be dependant on ODBC calls made against linked tables. Report generation so far is also heavily dependant on the linked tables, and not ADODB recordsets. (Don't really know how to base the reports off anything else, not good with reports)

I've got about 30 linked tables (Don't know if I need to have that many linked, as not all are used as rowsources). ~half are views but Access doesn't care. If I understand correctly, these tables would need to be linked to one of two System DSN's, depending upon what I was connecting to.

So, how can I, in VBA, add, update, or modify where linked table references should point to?

I did find the following online but I don't know what to set NewPathName to in order to test it out. It may not even deal with linked tables for all I know.

Public Sub RelinkTables(NewPathname As String)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Set Dbs = CurrentDb
    Set Tdfs = Dbs.TableDefs
[COLOR=green]'Loop through the tables collection[/color]
       For Each Tdf In Tdfs
        If Tdf.SourceTableName <> "" Then [COLOR=green]'If the table source is other than a base table[/color]
            Tdf.Connect = ";DATABASE=" & NewPathname [COLOR=green]'Set the new source[/color]
            Tdf.RefreshLink [COLOR=green]'Refresh the link[/color]
        End If
    Next [COLOR=green]'Goto next table[/color]
End Sub

 

[Remou]

NewPathName would be the full path to the database to connect, for example:

C:\Docs\tt.mdb

 

[galorin]

Ah, well, that aint incredibly useful I don't think. A bit more research has shown that I need to do something like ODBC;DSN=localdb for each one of them... but it looks like the connection string for each of my tables is different, some have options, some list servers, some have descriptions, not all entirely useful, and probably all not necessary. But what should it be set to in orto connect to an ODBC database?