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:- http://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

route217JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

route217JuniorAuthor Commented:
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
0
route217JuniorAuthor Commented:
I am guessing my problem lie with the fact i need to tell it - it;s a new connection etc....
0
Gustav BrockCIOCommented:
It should probably read:

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

/gustav
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

EirmanChief Operations ManagerCommented:
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).
0
route217JuniorAuthor Commented:
Hi Expert

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

Thanks for the heads up...and apologies for any inconvience
0
route217JuniorAuthor Commented:
Hi /gustav

I am still getting connection to ODBC failed error message.
0
Gustav BrockCIOCommented:
It should probably read:

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

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
route217JuniorAuthor Commented:
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....
???
0
route217JuniorAuthor Commented:
not sure what to do /gustav.....
0
Gustav BrockCIOCommented:
You can try with the IP address:

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

/gustav
0
route217JuniorAuthor Commented:
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....
0
route217JuniorAuthor Commented:
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...
0
Gustav BrockCIOCommented:
You found out, I can see. Great!

/gustav
0
route217JuniorAuthor Commented:
nope. ....I have asked the question on the sql board...totally lost on this one. .
0
Gustav BrockCIOCommented:
Oh.
Let us know how you proceed.

/gustav
0
route217JuniorAuthor Commented:
stuck...can u suggest something please
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.