Setting ODBC Connect String property programmatically 1

[MLZ]

Hi,

Is there a way I can set the ODBC Connect STring property of a sql pass-thru query at startup of the application, or before the user executes the sql-pass thru query ?

Background:
Currently, I have over 10 reports whose recordsources are sql pass thru queries. Presently, when the user test the reports, I manually set the ODBC string behind each of the pass thru queries to use the test database. And then when they pass the test and are ready for production installation, I had to go through each again and manually change the ODBC Connect string to assign the Production database.

Problem:
I'd like to automate the setting of the ODBC connection string for our pass-thru queries, so that when we need to switch from test to production environment, there is less chance for committing manual error.

Please advise.

Thanks.

 

[JonFer]

I believe you need to set the Connection property of the query:

Set qd1 = db1.QueryDefs(QueryName)
strODBCConnect = "ODBC;..."
qd1.Properties("Connect&quot = strODBCConnect

If you have a lot of queries to change, I would put the query names in a table with 3 fields:

QueryName, TestOrProd, ODBCString

Write a query that pulls the records from this table, open this query as a recordset in a subroutine, and loop through each record to set the Connect property. This way you can see all of the ODBC connect strings and if something changes it is easy to update in one place.

John

 

[MLZ]

Thanks John. Your solution worked !

--mlz