Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Microsoft Access

Avatar of undefined
Last Comment
route217

8/22/2022 - Mon
ASKER
route217

ok fixed the above...

gettting a run time error 0

reversed error...
Jim Dettman (EE MVE)

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.
ASKER
route217

Hi Jim

my external table is sql....one
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
route217

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)
Jim Dettman (EE MVE)

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.
ASKER
route217

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.....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

<<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.
ASKER
route217

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...
Jim Dettman (EE MVE)

See my last comment please.  let's take it one step at a time.

Jim.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
route217

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.
ASKER
route217

sorry for posting the same message 4 time...
Jim Dettman (EE MVE)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
route217

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.....
ASKER
route217

point 1. above message...change the link table linking..exiting...would this work???
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
route217

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..
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim Dettman (EE MVE)

<<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.
ASKER
route217

jim..

let me glue the vba code together. ..based on ur feedback. .
ASKER
route217

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
route217

hi jim

sorry for asking having a nightmare on this question can u assist???