Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2004
    Posts
    19

    Unhappy 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:

    SELECT
    account_number,
    state_abbrv,
    service_status,
    FROM
    accounts_table
    WHERE
    file_date = '10/20/04'
    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 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?
    Last edited by simsjr; 10-25-04 at 14:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •