Relink tables to ODBC source

[jmeckley]

I need to be able to relink the tables in my database. I have done this before way back when, but I can't remember everything. I have the following code in place on a button right now (testing). I also need to pass a username and password to the data source, I believe I add it to the TBL.Properties where the DSN and database name is, but I'm not sure.

I also can't remember the difference between the DSN name and the Database name.

With this code a I get an error that says the connection failed.

I am using Access XP on a Windows XP machine. I don't think that would matter, but it may change the code.
------------------------
Option Compare Database

Private Sub cmdReLink_Click()
On Error GoTo Link_Err
Dim CNN As ADODB.Connection
Dim CAT As ADOX.Catalog
Dim TBL As ADOX.Table

Set CNN = CurrentProject.Connection
Set CAT = New ADOX.Catalog
CAT.ActiveConnection = CNN

For Each TBL In CAT.Tables
If TBL.Type = "PASS-THROUGH" Then
TBL.Properties("Jet OLEDB:Link Provider String&quot = "ODBC;DSN=REOPEN7, Database=REOPEN7;"
End If
Next TBL

Exit_Link:
Exit Sub

Link_Err:
MsgBox Err.Description
Resume Exit_Link

End Sub
------------------------ Thank you for your assistance

Jason Meckley
Database Analyst
WITF

 

[AvGuy]

Try this link:

http://www.mvps.org/access/tables/tbl0012.htm

AvGuy