Relinking with ADO and ADOX on a different site

Eddie Antar
Eddie Antar used Ask the Experts™
on
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.

RelinkingADOIssue.jpg
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
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.

Author

Commented:
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 TsioumprisSoftware & Systems Engineer

Commented:
I think the info of the database is here :strDBLinkSourceTransTrack

Author

Commented:
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 )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
What line throws that error?
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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
Distinguished Expert 2017

Commented:
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.
Hi all,

I never really received workable solution here, so I'm closing the question. For those interested, I had to wind up just switching  to DAO as I needed to accomplish this quickly.

In any case, I want to thank all of you that hopped on the questions!

Thanks again,
Eddie
Distinguished Expert 2017

Commented:
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial