Refresh Links (ADO) (2000 (9.4.4119 SR-1))

Topic

New Reply

I am trying to refresh the links on some tables with the following code using ADO.

Public Function RefreshLinks(vChangedSourceFile As String) As Boolean
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog

‘ Open a Catalog object on the database in which to refresh links.
catDB.ActiveConnection = CurrentProject.Connection
catDB.Tables.Refresh

For Each tblLink In catDB.Tables
‘ Check to make sure table is a linked table.
If tblLink.Type = “LINK” And Left(tblLink.Name, 2) = “dt” Then
tblLink.Properties(“Jet OLEDB:Link Datasource”) = vChangedSourceFile
tblLink.Properties(“Jet OLEDB:Create Link”) = True
If Err Then
RefreshLinks = False
End If
End If
Next tblLink

Set catDB = Nothing
RefreshLinks = True

End Function

Running the function generates the following error message:

Run-time error ‘-2147217887 (80040e21)’

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

When I check the table properties, only the first ‘dt’ table was modifed?

Any clues/guesses as to why the code does not loop through all of the tables?

Thanks,

Reply | Quote

Replies

You’re trying to change you links to point to a different back end, right? Check each table to see if the “Jet OLEDB:Link Datasource” is an empty string. If not, it’s a linked table. You’re hitting an ADO error in there someplace, so you need to check the errors collection of the connection object. The easiest way is to declare an ADODB.Errors object and an ADODB.Error object in your declarations and then test for a Count >0 on the errors collection. Try this:

Dim errCurr as ADODB.Error
Dim ADOErrors as ADODB.Errors

Set ADOErrors = Cat.ActiveConnection.Errors

Then in your error handler, you can loop through them using the For each errCurr in ADOErrors syntax. Just make sure you issue an ADOErrors.Clear after then end of the For-Next loops because there is no automatic reset on ADO errors.

Reply | Quote

When I used the error collection, I got the same error message as before.

I know that all of the ‘dt’ tables are linked because I manually created the links.

Reply | Quote

There are apparently several issues that can cause the error message you are getting – in particular look as PRB: “Multiple-step OLE DB operation generated errors” When Opening ADO Connection for one case. See also Microsoft Knowledge Base Article 228935 and Microsoft Knowledge Base Article 327557. One important factor is which version of ADO you are using – in addition the kind of source makes a different. I suspect you are using a Jet backend based on your previous posts, but is that really the case?

Reply | Quote

Both the Front and Back ends are Access (SP-3).

The ADO Library is 2.1

The ADOX library is 2.7

MDAC version 2.7

Reply | Quote

You’re using the wrong ADO library. If your MDAC is 2.7, then use the ADO 2.7 library as well, not 2.1. The 2.1 setting is the default in Access 2000, but you should use the latest version installed, especially if you’re also using the ADOX library. ADO 2.1 had some problems and shortcomings that were fixed in 2.5 and later.

Reply | Quote