asked on 9/5/2013

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

Last Comment

9/6/2013

9/5/2013

ASKER

ok fixed the above...

gettting a run time error 0

reversed error...

Jim Dettman (EE MVE)

9/5/2013

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.

route217

9/5/2013

ASKER

Hi Jim

my external table is sql....one

route217

9/5/2013

ASKER

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)

9/5/2013

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.

route217

9/5/2013

ASKER

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

Jim Dettman (EE MVE)

9/5/2013

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

route217

9/5/2013

ASKER

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)

9/5/2013

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

Jim.

route217

9/5/2013

ASKER

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.

route217

9/5/2013

ASKER

sorry for posting the same message 4 time...

Jim Dettman (EE MVE)

9/5/2013

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.

route217

9/5/2013

ASKER

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

route217

9/5/2013

ASKER

point 1. above message...change the link table linking..exiting...would this work???

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

9/5/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

route217

9/5/2013

ASKER

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

Jim Dettman (EE MVE)

9/5/2013

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

route217

9/5/2013

ASKER

jim..

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

route217

9/6/2013

ASKER

Jim

posted second part to question...https://www.experts-exchange.com/questions/28232414/Pass-through-qry-Part-2.html

route217

9/6/2013

ASKER

hi jim

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