Cascading SQL: Maintain Database Connection

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

Guest

Anyone know how to make Access maintain a Pass Through connection so that my
SQL can process several SELECT clauses?
 
simsjr said:
Anyone know how to make Access maintain a Pass Through connection so that my
SQL can process several SELECT clauses?
Click to expand...

Assuming we are talking SQL Server, then you can execute multiple
Transact-SQL statements by simply assigning the entire T-SQL batch to the
SQL property of your pass-through query.

However, why you would want to do this with SELECT queries is beyond me,
unless all but the last one are actually make-table queries. If you want to
run several SELECT queries in order to return several separate recordsets to
Access, then run them as a series of pass-through queries. There is no need
to worry about the overhead of creating a series of connections, because SQL
Server caches them.
 
I'm actually using an ODBC connection to an Informix database. Basically,
the problem is that Access kills the query after the first SQL statement, so
it won't string the queries together.

The queries that occur later in my SQL rely on SELECT INTO statements from
before. So that's my sitch.

I guess I'm asking how I can do a transaction type of query without using
SQL Server. I think the only way is to write a module that leaves the query
open long enough for all my statements to run.
 
simsjr said:
I'm actually using an ODBC connection to an Informix database. Basically,
the problem is that Access kills the query after the first SQL statement, so
it won't string the queries together.

The queries that occur later in my SQL rely on SELECT INTO statements from
before. So that's my sitch.

I guess I'm asking how I can do a transaction type of query without using
SQL Server. I think the only way is to write a module that leaves the query
open long enough for all my statements to run.
Click to expand...

If you are truly using a pass-through query, then Access is certainly NOT
killing the SQL batch (UNLESS it is simply timing out: the default ODBC
timeout is one minute, see below). The whole point of a pass-through query
is that it is written in the database engine's native SQL dialect. Access
is incapable of understanding this dialect, and hence makes no attempt at
parsing or executing it: it simply passes the whole thing to the database
engine (in your case the Informix server), and waits for it to complete
(successfully or otherwise).

So, in your case, it seems that there are three possibilities:

1. You are not using a pass-through query at all. Have you set the
query's Connect property? If not, then what you have is a Jet query, not a
passthrough query. However, contrary to your belief, you can still
transaction-protect a series of Jet query executions using Jet transactions,
even if the queries are against linked ODBC tables (assuming that the
database engine and ODBC driver support this, which I imagine they probably
do). However, in my experience this only works with DAO: if you attempt to
use Jet transactions with ADO, you can throw away your stopwatch and instead
use a calendar to time your updates.

2. Your query is falling victim to an ODBC timeout. See the following
article, and many previous discussions in Google Groups:

http://support.microsoft.com/default.aspx?scid=kb;en-us;208386

3. Informix is killing your SQL batch because there is something wrong
with it. Have you checked that the SQL batch works OK using whatever
interactive SQL tool that Informix offers?

Further thoughts:

- Does Informix support stored procedures? If so, can your series of SQL
statements be written as a stored procedure?
- I have no idea what you mean by "write a module that leaves the query
open long enough for all my statements to run", nor how you would begin to
do such a thing.
- If all this still doesn't help, could you post your code please. That
is probably the easiest way of clarifying exactly what it is you are trying
to do.
 
Back
Top