Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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,

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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 <!mskb=228935>Microsoft Knowledge Base Article 228935<!/mskb> and <!mskb=327557>Microsoft Knowledge Base Article 327557<!/mskb>. 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?
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •