Link To ODBC Table Using VBA 1

[rgbanse]

I'm lookng for some help in linking to an ODBC table using Access.

I'm using the following for non ODBC tables:

Set mylink = _
ApplicationDb.CreateTableDef(DataDb.TableDefs(X).Name)

'Set the connect and SourceTableName properties
mylink.Connect = ";DATABASE=" & Path
mylink.SourceTableName = DataDb.TableDefs(X).Name
'Append the TableDef object to the Tabledefs collection
ApplicationDb.TableDefs.Append mylink

I need help with ODBC
thx in advance
RGB

 

[bustell]

Is it necessary to create it through code? Why do you not want to create the linked table with the new button on the tables tab of the database window?
Pat B

 

[rgbanse]

Pat,
We're about to change out our server. We have a few hundred databases and will need to relink them to the new server. Thus the desire to automate. We did settle on the code below with additional code to access databases within specific directories

Function ReLinkODBC()
Dim ThisDatabase As Database
Dim LinkedOdbcTable As TableDef
Dim LocalOdbcTable As TableDef

On Error GoTo ErrorHandler

'Cycle through the database tables refreshing their links
Set ThisDatabase = CurrentDb
For Each LocalOdbcTable In ThisDatabase.TableDefs
If (LocalOdbcTable.Attributes And dbAttachedODBC) Then
Set LinkedOdbcTable = ThisDatabase.TableDefs(LocalOdbcTable.Name)

LinkedOdbcTable.Connect = "ODBC;DSN=SECURE _ PRODUCTION;DB=xxxxx_db;HOST=RS6K2;SERV=10100; _ SRVR=xxxxx_prod_tcp;PRO=onsoctcp;UID=******;PWD=*****"
LinkedOdbcTable.RefreshLink
End If
Next LocalOdbcTable

Exit_ReLinkODBC:
Set ThisDatabase = Nothing
Set LinkedOdbcTable = Nothing
Set LocalOdbcTable = Nothing
Exit Function

ErrorHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "An error has occured in procedure ReLinkODBC"
Resume Exit_ReLinkODBC
End Function

RGB

 

[bustell]

Hmmmmm..... Hundreds of databases would be a good reason to automate. Glad to hear you have code that works and thanks for posting it. It may help someone else.

Just as a note, (and I hope you do not experience this problem) I have on occasion had problems with Access refreshing links. I actually had to destroy and recreate the link.
Pat B

 

[rgbanse]

Pat,
We have had the occasion to see "Invalid Call Or Procedure" when using the link manager. We would have to kill and recreate the links. The above method seems to fix whatever was causing the problem. We tested quite a few Db's that gave the error message above, and after running the code, Link Manager was working as designed. ??
thx for the response
RGB