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/Microsoft/Development/MS_Access/Q_28231292.html#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

Microsoft Access

Avatar of undefined
Last Comment
route217

8/22/2022 - Mon
ASKER
route217

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

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

Hi Expert

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

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

Hi /gustav

I am still getting connection to ODBC failed error message.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Gustav Brock

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

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

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
route217

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

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

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

stuck...can u suggest something please
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck