Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Unanswered: Another ADO Question

    I'm running Access 2002 and the following code compiles without a problem. However, when I try to call the routine when opening a form, I get the following error msg:
    Runtime Error 91: Object Variable or With Block Variable Not Set

    When the procedure hits the following line of code

    tdf.Properties("Jet OLEDB:Link Datasource") = strBackendPath

    What's wrong with the code? I've researched this in all my reference books and I don't see anything different in any of them.

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo RefreshLink_Err

    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.Table
    Dim strBackendPath As String
    Dim strTemp As String

    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tdf = cat.Tables("EHS-Complaints")
    strTemp = tdf.Columns(0).Name
    Exit Sub

    RefreshLink_Err:
    strBackendPath = [backendpath]
    tdf.Properties("Jet OLEDB:Link Datasource") = strBackendPath
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tdf = cat.Tables("EHS-Complaints")
    Resume

    End Sub

    Terry H

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,

    Can you please confirm whether the error is being thrown before or after the variable 'tdf' is set.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Another ADO Question

    When I trouble shoot the code, I put a line in with a msg box to make sure that the strBackendPath variable was Assigned properly

    msbox "Backend Path: " & strBackendPath
    tdf.Properties("Jet OLEDB:Link Datasource") = strBackendPath

    When I do this the code runs and gives me the value stored in the strbackend path variable and then stops on the next line of code

    tdf.Properties("Jet OLEDB:Link Datasource") = strBackendPath

    Also when the code is interupted, this line is highlighted in yellow so I think the problem is there.

    Terry H

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Terry,

    It appears that tdf is not being set properly from this line:

    Set tdf = cat.Tables("EHS-Complaints")

    Consequently, this causes the error to be thrown, which in turn is trying to reference the variable tdf, which caused the error to be thrown in the first place.

    Therefore, ensure that the variable tdf is set correctly.
    Last edited by r123456; 11-15-04 at 13:23.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Another ADO Question

    I retyped the line of code assigned the tdf variable and the procedure worked fine. The retyped line of code looks exactly the same, but something must have been different.

    Thanks, the routine works now.

Posting Permissions

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