Unanswered: Multiple Selects: Keep DB Connection open?
I'm using MS Access to talk to an Informix database, but am running into problems running multiple SELECTs using pass-through mode.
Here's what the beginning of my query looks like. Notice the INTO TEMP
clause at the end that creates temp tables for later use in the query:
file_date = '10/20/04'
INTO TEMP tmp_all;
...After this query, imagine several other similar SELECTs - all in one big
Some people call these batch queries, some call them cascading queries. Not
sure what the official term for this is. However, I do know that I can run
multiple SELECTs using other SQL programs that have batch processing
capabilities, but not in Access 97. The reason for this is because Access
terminates the database connection after the first SELECT query completes,
which also drops the TEMP tables that come into play later in the query.
If that all made sense, my question is this: Does anyone know a workaround
with VBA to make multiple SELECTs work in Access 97? Or perhaps more simply, to hold the dabase connection open long enough for Access to run through my entire query and process all SELECTs?