How to Keep DB Connection Open For Multiple SELECTs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 97 kills my pass-through query after the first SELECT statement. I
have multiple select statements in my complete query (which also creates temp
tables at the database side).

Here's what the beginning of my query looks like. Notice the INTO TEMP
clause at the end:

SELECT
account_number,
state_abbrv,
service_status,
FROM
accounts_table
WHERE
file_date = '10/20/04' AND
INTO TEMP tmp_all;

....After this query, imagine several other similar SELECTs - all in one big
SQL query.

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 the 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?
 
hi,
I think your INTO clause is suppost to be part of the
SELECT clause.
SELECT INTO.....
Does that query run without error????
 
This is pure SQL fed directly to an Informix database via a pass-through
query in Access. For Informix, the syntax is correct. The problem is that
Access is killing the ODBC connection after the end of the FIRST SQL
statement (shown below). This is actually a limitation with Access itself, as
it seems unable to process batch queries.

However, I'm hoping that there might be at least some VBA scripting that
might keep the database connection open long enough for the remainder of the
SQLs to run.
 
hi again
There is vb script that might do it.
docmd.runsql
you might have to break up your batch query and run each
query individually each one invoked by the docmd.runsql
command. look it up in access help.
 
Looking into it! Thanks! :)

[email protected] said:
hi again
There is vb script that might do it.
docmd.runsql
you might have to break up your batch query and run each
query individually each one invoked by the docmd.runsql
command. look it up in access help.
Click to expand...
 
Back
Top