Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Pass through qry - Part 2

Hi Experts

Firstly thanks to all the excellent feedback given so far...

I am open a second part to a pervious question: Which expert Jim has given invaluble feedback on


See reply back ID:39467816 (the question i accepted with points)

Based on the experts feed back i am stuck on the following;

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: (point 2 look wasy option?)

1. Rebuild the string from scratch

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

Code I have so far:-

Option Compare Database

Public Function GetQueryLinks()
On Error GoTo Err_GetQueryLinks

    Dim qdf As DAO.QueryDef
    Dim strConn As String
      strConn = "ODBC;Driver=(Apple_Oranges);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

        'Refresh QueryDef
    Set qdf = Nothing
    Exit Function

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

End Function

Open in new window

Avatar of route217
Flag of United Kingdom of Great Britain and Northern Ireland image


I have tried to amend line 9:


strConn = "ODBC;Driver=(Apple_Oranges);UID=MyUID;PWD=MyPass;SERVER=

strConn = "ODBC;Driver=(Apple_Oranges);UID=MyUID;PWD=MyPass;SERVER=TheServer;"

And no luck
I am guessing my problem lie with the fact i need to tell it - it;s a new connection etc....
Avatar of Gustav Brock
It should probably read:

strConn = "ODBC;Driver=(Apple_Oranges);UID=MyUID;PWD=MyPass;SERVER=

Two EE tips route217

You are better off editing your question rather that creating a new post for an afterthought.
Questions with 0 replies draw greater attention, and eventually the admins will intervene.

To draw the attention of the experts who helped you with Pt1, simply post a comment and link to that question, mentioning that there is a follow up question (they will receive an email).
Hi Expert

Point taken....from Eirman....

Thanks for the heads up...and apologies for any inconvience
Hi /gustav

I am still getting connection to ODBC failed error message.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
still get the same error message...

just a quick point do we not need to tell it that it's a new connect? do we need to use ado....
not sure what to do /gustav.....
You can try with the IP address:

strConn = "ODBC;Driver=(Apple_Oranges);UID=MyUID;PWD=MyPass;SERVER=<ip address>,34567;"

ok gustva...

i have discover that the sql connect might be 64 bit and i am on 32 bit.........would that have an impact on the connection....

if i was to added

'Dim StrConn As ADODB.Connection
    'Set StrConn = New ADODB.Connection

to the above vba how would i accomendate or am i wasting my time...
You found out, I can see. Great!

nope. ....I have asked the question on the sql board...totally lost on this one. .
Let us know how you proceed.

stuck...can u suggest something please