ODBC Connect Str (Pass-Through Query)

[S3066]

Hi Group -- does anyone know how to initialize the ODBC connection stored in an Access session? I have a series of pass-through queries, and I need to establish a different ODBC connection for each. Just hard-coding the ODBC Connect Str property in the query's properties doesn't work because somehow the initial ODBC connection is saved in the Access session, preventing my subsequent ODBC connection attempts from working.

Currently my workaround is to "x" out of Access completely, then run the other pass-through queries. Any ideas on how I might reset the ODBC connection without having to start a completely new Access session? I'm sure there is a VB way to do it, but I was wondering if there is a non-VB way also. Any ideas are appreciated as always ~ Thanks

 

[lameid]

Wow it has been a long while since I ODBC'd to multiple data sources but it just worked with the connection saved.

Have you tried looking for updated ODBC drivers?

Do you have to log in each time you connect? Is it to the same server with different login accounts?

 

[S3066]

Thanks for your response. Here's my situation in somewhat more detail: I'm using the same single ODBC driver in these multiple pass-through queries. What's different is the parameters specified in each pass-through query.

In one query, I need to specify the parameter as simply "ODBC;" because it is a date-driven query, and the ODBC connection bombs if I try to hard-code additional parameters (something quirky with the particular ODBC driver I'm using; bug to be fixed in a future release).

In another query, I want to specify the ODBC Connect Str more fully as "ODBC; etc. etc."

Again, it's the same ODBC driver, just a different connect string. Access seems confused at times by these different parameter requests depending on the order in which I do them. My workaround has been to "X" out of Access and re-run the queries that didn't run.

 

[SkipVought]

How about posting the SQL for all your queries and explain the problems with each.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[S3066]

All the SQL consists of is {call query.QueryName}, where QueryName is the name of a stored procedure. The other settings are in the ODBC Connect Str.

Maybe another way to approach this problem is, if anyone has a way in VBA to RESET or initialize the ODBC connection established in an Access session from a pass-through query.

 

[SkipVought]

The SQL in your stored procedures, please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I should have asked, the CODE in your stored procedures, please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}