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

route217

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

route217

ASKER

I am guessing my problem lie with the fact i need to tell it - it;s a new connection etc....

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

Eirman

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

route217

ASKER

Hi Expert

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

Thanks for the heads up...and apologies for any inconvience

route217

ASKER

Hi /gustav

I am still getting connection to ODBC failed error message.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

route217

ASKER

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

route217

ASKER

not sure what to do /gustav.....

Gustav Brock

You can try with the IP address:

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

/gustav

route217

ASKER

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

route217

ASKER

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

Gustav Brock

You found out, I can see. Great!

/gustav

route217

ASKER

nope. ....I have asked the question on the sql board...totally lost on this one. .

Gustav Brock

Oh.
Let us know how you proceed.

/gustav

route217

ASKER

stuck...can u suggest something please