asked on

Relinking with ADO and ADOX on a different site

Hey Experts,

I have an app that has a function used for relinking on my client's server. All these parameters are stored in variable, and the backend is password protected. This function was originally developed using ADO on a non password protected backend, but now the backend is protected. I'm trying to redo the function so that it works with the password.

Here's the section of code:

Set catDB = New ADOX.Catalog
' Open a catalog on the database in which to refresh links.
catDB.ActiveConnection = CurrentProject.Connection



For Each tblLink In catDB.Tables
    ' Check to make sure table is a linked table.
    If tblLink.Type = "LINK" Then
    
        If InStr(tblLink.Properties("Jet OLEDB:Link Datasource"), strTransTrack_mdb) > 0 Then
           tblLink.Properties("Jet OLEDB:Link Provider String") = "MS Access;" & strTransTrackPWD & "DATABASE=" & strDBLinkSourceTransTrack & "\" & strTransTrack_mdb
           'tblLink.Properties("Jet OLEDB:Create Link") = True
        ElseIf InStr(tblLink.Properties("Jet OLEDB:Link Datasource"), strStep_mdb) Then
           tblLink.Properties("Jet OLEDB:Link Provider String") = "MS Access;" & strStepPWD & "DATABASE=" & strDBLinksourceStep & "\" & strStep_mdb
           'tblLink.Properties("Jet OLEDB:Create Link") = True
        End If

    End If
Next

Open in new window

On my local machine this works fine. But on the server I'm getting an error message saying the that what I'm trying to link to is NOT a valid path. Here's the error message.

The problem is... the error message is showing me the path from my LOCAL machine, NOT the path I'm trying to set it to. It's showing me the CURRENT path, which is not what I'm trying to link to. I'm trying to relink to G:\Step_Tables.mdb, which is a valid path.

What am I missing in the ADO relink?

Any help would be greatly appreciated.

John Tsioumpris

Start simple and put a breakpoint on the start of your code..and work your way by debugging each line...probably you are seeing it from the wrong angle.

Eddie Antar

ASKER

Hi John, thanks for your reply. I AM stepping through it. But not seeing why I'm getting this error message. Do you see anything in the code???

Thanks
Eddie

John Tsioumpris

I think the info of the database is here :strDBLinkSourceTransTrack

Eddie Antar

ASKER

I'm not getting what your saying. Yes that's where the source is, it's indicating the source on the SERVER. But I'm not even getting there. The error message is telling me that I have an incorrect path, and the path is showing is to my local machine, which is where the table WAS linked.

How do I reset the link?

Thanks,
Eddie

Scott McDaniel (EE MVE )

What line throws that error?

PatHartman

I AM stepping through it. But not seeing why I'm getting this error message.

Are you examining all the variables to be certain that they are what you think they are supposed to be?

I'm having a vague memory of encountering a similar problem but I can't quite bring it into focus. I would change from ADO to DAO and hope to get past the problem that way. Don't forget to disambiguate ALL ADO/DAO objects in their Dim statements and depending on what version of Access you are using, you might need to also include a reference to DAO. Since the BE is Jet, DAO is more efficient anyway and so is a better choice for data access.

Eddie Antar

ASKER

Hi all,

Thanks for your responses.

Scott, the line that throws the error message is this one:
tblLink.Properties("Jet OLEDB:Link Provider String") = "MS Access;" & strStepPWD & "DATABASE=" & strDBLinksourceStep & "\" & strStep_mdb. What the error message throws up a "Not a valid path" is indeed Not a Valid Path. But why isn't it letting me assign a new Provier String to the ADOX table def?

I've looked at the variables several times. If there's an error in the variables, I don't see it. The variable for strDBLinkSourcesStep is pointing exactly where I want it to point.

Pat, I've actually created a DAO version of the function and that version works fine.

But before I close the question, I'm still interested in if anyone knows how to resolve this for ADO? I'm sure this is something that might come up in other applications.

The big question for me is why is tblLink.Properties("Jet OLEDB:Link Provider String") trying to reference the old Database Source when I'm trying to store a new one. It's like a assignment statement for a variable assigned a 0 throwing an error when I assign it a 1, telling me that the 0 is invalid. If I'm wrong on this, please let me know.

Thanks again,
Eddie

PatHartman

I never adopted ADO myself back around 2000 when MS was pushing it. I stuck with DAO and quite possibly ran into this issue with an app created by someone else that I had to modify. I'm going to guess that if you change the link on your PC to link to the server before you move the database, the problem will go away and you can leave the ADO code.

ASKER CERTIFIED SOLUTION

Eddie Antar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

I'm having a vague memory of encountering a similar problem but I can't quite bring it into focus. I would change from ADO to DAO and hope to get past the problem that way. Don't forget to disambiguate ALL ADO/DAO objects in their Dim statements and depending on what version of Access you are using, you might need to also include a reference to DAO. Since the BE is Jet, DAO is more efficient anyway and so is a better choice for data access.

Just because you don't like the solution doesn't mean that you didn't get one here.

Eddie Antar

ASKER

Thank you all for the time you took with this problem.