Pass through qry error message

Hi Epxerts

I am running the following vba to check my DSN connections....and getting the following error message

3305 (invaild connection string in pass-through qry) in procedure  GetQueryLinks of module bas TableLink


Is ther ea way to change the vba code to replace all old dsn connection with a new one a spoecify if not....how can i trace which one to replace.....


Code:

Public Function GetQueryLinks()
On Error GoTo Err_GetQueryLinks

    Dim qdf As DAO.QueryDef
    Dim strConn As String
    
    'strConn = "'ODBC;Driver={Microsoft ODBC for Oracle};UID=MyUID;PWD=MyPass;SERVER=TheServer;"
        For Each qdf In CurrentDb.QueryDefs
        If qdf.Connect <> "" Then
            qdf.Connect = strConn
            Debug.Print qdf.Connect
        End If
    Next
    
        'Refresh QueryDef
    'CurrentDb.QueryDefs.Refresh
    
Exit_GetQueryLinks:
    Set qdf = Nothing
    Exit Function

Err_GetQueryLinks:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure GetQueryLinks of Module basTableLinks"
    Resume Exit_GetQueryLinks

End Function

Open in new window

route217JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

route217JuniorAuthor Commented:
ok fixed the above...

gettting a run time error 0

reversed error...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In this line:

  'strConn = "'ODBC;Driver={Microsoft ODBC for Oracle};UID=MyUID;PWD=MyPass;SERVER

should be:

strConn = "ODBC;Driver={Microsoft ODBC for Oracle};UID=MyUID;PWD=MyPass;SERVER"

Your other problem is this:

If qdf.Connect <> "" Then


 Your assuming automatically that any external table is an Oracle one.   I would suggest adding a check to look for a specific driver, Server name, DSN or something to decide if you really want to update that tabledefs connect property.

Jim.
0
route217JuniorAuthor Commented:
Hi Jim

my external table is sql....one
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

route217JuniorAuthor Commented:
Jim

my main problem is: i cannot run any of the queries.....etc.....how do i over come this

the error message i am getting is

ODBC -- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Invaild object name 'apples_extact_tbl_oranges'. (#208)
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think what your problem is, is that one of your existing querydefs already has an invalid SQL statement.

When you hit the error, do:

 ? qdf.Name

 In the debug window (Ctrl/G) and it will give you the name of the querydef that is causing the problem.

 Changing the connect property alone would not normally give you that kind of error message.

Jim.
0
route217JuniorAuthor Commented:
Hi Jim

sorry for sounding stuiped on my part....i am getting the error message when in queries...and try and run any qry...

so how would i check ?qdf.name.....
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<ODBC -- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Invaild object name 'apples_extact_tbl_oranges'. (#208) >>

 I'm getting lost here...

Your question title is "Pass through qry error message", your question mentions "I am running the following vba to check my DSN connections....", which I can't see where your using any, and you code is only working with connect properties of querydefs.

 So let's start over:

1. Create a new linked table to the ''apples_extact_tbl_oranges'' through Access.   Can you open and read the table?

2. Now in the debug window, do:

 ?  CurrentDB().tabledefs("apples_extact_tbl_oranges").Connect

 and hit return.  Then cut and paste the output here.

Then we'll move onto the querydefs if we need to.

Jim.
0
route217JuniorAuthor Commented:
Hi jim

one more question i know the server name and the database name...can we not just write some vba code to change all existing connect with a newer connect...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
See my last comment please.  let's take it one step at a time.

Jim.
0
route217JuniorAuthor Commented:
Apologies!! Apoogies!!!

1. Create a new linked table to the ''apples_extact_tbl_oranges'' through Access.   Can you open and read the table? - Yes

2. Now in the debug window, do:

 ?  CurrentDB().tabledefs("apples_extact_tbl_oranges").Connect

 and cut and paste here - NAME SHAVE BEEN CHANGED

?CurrentDB().tabledefs("apples_extact_tbl_oranges").ConnectODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes
ODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes
ODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes
ODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes
ODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes
ODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes
ODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes



Then we'll move onto the querydefs if we need to.
0
route217JuniorAuthor Commented:
sorry for posting the same message 4 time...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, so now we know what the connect string should look like:

ODBC;Description=Banana;DRIVER=SQL Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;UID=1166100;DATABASE=Banana;Trusted_Connection=Yes

and there is no DSN involved.  This is a straight connection string to SQL specifying the server, user, etc.

Now what is it that you want to accomplish exactly?

1. Change the existing table linking?
2. Change all the query connects?

etc.

and what is it that we want to change?   Server?  User?  etc.

Jim.
0
route217JuniorAuthor Commented:
Hi Jim

Now what is it that you want to accomplish exactly? - connect the tables so all the qry's work

1. Change the existing table linking? - not sure on this one
2. Change all the query connects? - yes..

etc.

and what is it that we want to change?   Server?  User?  etc

Basically in a nutshell i want the linked tables to connect to the qry and produce an output...

as you have point out berfore  - one step at a time - like this approach

not sure what i have done work.....
0
route217JuniorAuthor Commented:
point 1. above message...change the link table linking..exiting...would this work???
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<point 1. above message...change the link table linking..exiting...would this work??? >>

 Yes and no.  Pass through queries would need to be changed as well because they don't rely on local linked tables.

 TableDefs() and QueryDefs() both have a connect property and the code you posted at the start is basically what you need.   You were pretty much on the mark with this already.

 You just need to add to it a bit.   For this loop:

       For Each qdf In CurrentDb.QueryDefs
        If qdf.Connect <> "" Then

            qdf.Connect = strConn
            Debug.Print qdf.Connect

        End If

        Next

 Rather then saying if it has a connect string, then replace it, you need to be a bit more selective.

 Use InStr() to search the connect string, say a server name.  

  Once you know this is a string you want to change, you have two choices:

1. Rebuild the string from scratch

2. Replace each of the parts (like the server name) using InStr(), Left(), Mid(), etc.

You know what the new connect string should look like, so it's just a matter of going from old to new using one of the above methods.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
route217JuniorAuthor Commented:
Thanks Jim

for the excellent feedback...what I had above can from another question so if its not to rude what's the complete vba code..
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<for the excellent feedback...what I had above can from another question so if its not to rude what's the complete vba code.. >>

 I was little vague because I'm still not quite sure exactly what it is your trying to change in the connect string.

 You have the outline of the routine already.   You just need to add a few lines of code to:

a. make sure you do want to change the current querydef.

b. change it, either by replacing the string entirely or replacing just a portion of it (like the server name).

  I can't get any more specific unless you tell me exactly what it is your doing.

  Your like 90% of the way there....

Jim.
0
route217JuniorAuthor Commented:
jim..

let me glue the vba code together. ..based on ur feedback. .
0
route217JuniorAuthor Commented:
0
route217JuniorAuthor Commented:
hi jim

sorry for asking having a nightmare on this question can u assist???
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.