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

Link:- https://www.experts-exchange.com/questions/28231292/Pass-through-qry-error-message.html?anchorAnswerId=39469611#a39469611

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

        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

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

ASKER

I have tried to amend line 9:

To

strConn = "ODBC;Driver=(Apple_Oranges);UID=MyUID;PWD=MyPass;SERVER=
Server;SERVER=ABCDr349DT84769.global.hireme.com,34567;"

From
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=
Server;SERVER=ABCDr349DT84769.global.hireme.com;Port=34567;"

/gustav
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.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
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;"

/gustav
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....
GUstav

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!

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

/gustav
stuck...can u suggest something please