refreshing linked table in Access 2000

The attached code is what I am using to refresh my linked tables.
My question:
Should catDB.ActiveConnection = the connection string to the database containing the linked table or should it be the database connection object?

Also tblLink.Properties("Jet OLEDB:Link Datasource").Value I have seen different examples setting this to different things. What should it be set for in my case?

Sub RefreshClientLinkedTables() Dim catDB As ADOX.Catalog Dim tblLink As ADOX.Table ' Open a Catalog object on the database in which to refresh links. catDB = New ADOX.Catalog catDB.ActiveConnection = OLEConn 'connectionString For Each tblLink In catDB.Tables ' Check to make sure table is a linked table. If tblLink.Type = "LINK" Then tblLink.Properties("Jet OLEDB:Link Datasource").Value = True 'SQLConn.ConnectionString End If Next catDB = Nothing tblLink = Nothing End Sub

MSDN:
"An easy way to determine the Jet connection string required to link an external data source is to create a linked table by using the Access user interface (File menu, Get External Data submenu, Link Tables command). Then open the linked table in Design view and display the Table Properties dialog box (View menu, Properties command). The Description property of a linked table shows the connection string used to link the table. You can display a long connection string by clicking in the Description property and then pressing SHIFT+F2."

You might want to run the code from within Access first, to get it running and see the datasource strings. Note that if your linking to another mdb, its enough with the physical path.

' This code needs a reference to Microsoft ADO Ext.

Sub CheckLinks

Dim ax As New ADOX.Catalog
Set ax.ActiveConnection = CurrentProject.Connection

Dim td As ADOX.Table

For Each td In ax.Tables
If td.Type = "Link" Then
Debug.Print td.Properties("Jet OLEDB:Link Datasource")
End If
Next

End Sub

0

NevSoFlyAuthor Commented: 2008-02-01

I'm guessing that the workstation ID is the ID of the system that the client app is on.or server?

The client app is using an Access DB. How would I find the workstation ID for the client system?

Also the server is SQL Server 2000 so I would need a password. Could you shoe me the syntax for that?

Lastly the string that I copied included the name of the linked table in the Access DB. Do I need to name every table? By the looks of the original code it seems like the code is stepping thru all the tables.

Here is the string that I got from the properties command:
ODBC;Description=connects to COFSE.REVs;DRIVER=SQL Server;SERVER=FREEDOM-TEST;APP=Microsoft Office XP;WSID=X2100-2864;DATABASE=COFSE;Trusted_Connection=Yes;TABLE=dbo.tblASSEMBLY

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

For Each tblLink In catDB.Tables
' Check to make sure table is a linked table.
If tblLink.Type = "LINK" Then
tblLink.Properties("Jet OLEDB:Link Datasource") = OLEConn
End If
Next

Set catDB = Nothing
Set tblLink = Nothing

End Sub

Hope it works this time!

0

NevSoFlyAuthor Commented: 2008-02-01

I can believe that this is so difficult. The line
catDB.ActiveConnection = connectionString
gives an error:
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

I also had to change:
tblLink.Properties("Jet OLEDB:Link Datasource") = OLEConn
to:
tblLink.Properties("Jet OLEDB:Link Datasource").value = OLEConn
because it gave an readonly error.

Yes I have, more than once. But there are always small differences. One current project I am working on use an mdb without security and a SQL Server 2005 with trusted security. There I run refresh from within VBA, but to help you out I tried activating it from vb.net also.

Did you try to run the refresh-routine from within Access VBA? Set the catDb.Activeconnetion = CurrentProject.Connection.