I thought I would update with what I came up with. Unfortunately I couldn't find anything non-programmic, so it involves getting the hands a little dirty.
Note: The table needs to have been created already using a DSN.
- In Access hit Ctrl-G to open a Microsoft Visual Basic editor.
- In the Project window (on the upper left side) right-click on Access9db and choose Insert -> Module
- Click on the newly created module and enter the following code into the empty code section:
Code:
Sub ChangeConnectionString
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
'Loops through list of tables
For Each Tdf In Tdfs
'Criteria for selecting which table
'to change the connection string of
If Tdf.SourceTableName <> "" Then
Tdf.Connect = 'INSERT YOUR CONNECTION STRING HERE
Tdf.RefreshLink 'Saves the changes made
End If
Next
End Sub
- In the Immediate window below type the function name (ChangeConnectionString) and hit enter
- Verify in Access that changes have been made by hovering over the linked table to see the connection string.
Based on this example: Change source of linked tables - Access World Forums