troubleshooting Question

Pass through qry - Part 2

Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft Access
16 Comments1 Solution520 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 16 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros