QueryDef.Connect

[AndyV]

I am trying to change the connect string for a series of Pass Through queries in a database, so that the end user can choose between Prod and Test DA Sources.

I believe that the connect string is read only for an existing query, so I am capturing the Prod and Test connect strings, the SQL from the query, and the query name as variables, deleting and recreating the query, looping through the qdefs collection and trying to set the connect string of the newly created query based upon the choice of the user.

I have 2 problems:

1) As you delete and recreate the first query, the number of query defs in the collection increases, so you end up looping round the same query over and over and
2) It seems to set the connect string to the same as the deleted query, irrespective of what is contained in the connect string variable used.

Any help would be appreciated.

Andy

 

[AndyV]

Have found he answer on another orum. Thanks

 

[chergh]

You could add a link to where your question was answered so other users with the same/similar issue can benefit.

 

[AndyV]

Really simple actually, here is the code that I used using an InputBox to prompt for either prod or test source systems (defaulting to production):

Do While strResponse = ""

strResponse = InputBox("Please input source db2 system, Production or Test", "Agency Data", "Production")
If strResponse Like "Prod" & "*" Then
strConnect = "ODBC;DSN=PAPBLLDB;UID=avasey;PWD=g3n3s1s1;DBALIAS=PAPBLLDB;" 'Prod
Else
If strResponse Like "Test" & "*" Then
strConnect = "ODBC;DSN=TAPBLLDB;UID=avasey;PWD=g3n3s1s1;DBALIAS=TAPBLLDB;" 'Dev
Else
strResponse = ""
End If
End If
Loop
i = 0
Set cDb = CurrentDb()
Do While i < (cDb.QueryDefs.Count)
Set qDef = cDb.QueryDefs(i)
'Only change SQL Passthrough queries
If qDef.Type = 112 Then
qDef.Connect = strConnect
End If
'Go to next query definition
i = i + 1
Loop