Q: SQL Pass through query no "done" alert box?

[Mark]

I'm using Access 2003 against a Teradata database. I have a SQL Pass through
query that's basically running a create table statement that takes several
minutes to process.

Thing is, when I run the query, after asking me to log in, there's nothing
telling me that it's done. The query works (I checked in the Teradata db),
but no alert box in Access saying "query finished".

Any way to get an alert box to pop up when the query is done? I can't put
the query in a macro, or put it in code -- the user taking over won't use
those.

Thanks!
-Mark

 

[Van T. Dinh]

Pass-Through Queries are processed by the BE database engine so I *guess*
for efficiency, Access does not wait for the process to finish if it doesn't
expect a return value.

Perhaps, you can re-code the SQL (or better, wrote an SP) to return some
check value (after CREATE TABLE statement) so that Access expects a return
value and waits for it. You can then use the return value to put up a
MsgBox indicating completion.

 

[Mark]

Thanks Van,

The interesting is that the database seems to freeze until the query is
complete. I would think Access would run along just fine since the BE DB
engine is doing all the work. All that happens is I lose the mouse icon. It
reappears when the query is finished.

I'll try your suggestion of using a stored procedure or something so the
query returns something to Access.

Thanks again,
-Mark

 

[Van T. Dinh]

Yes, I did notice that Access seems to "freeze" while the BE database engine
is processing the Pass-Through Query. I guess Access waits for an error or
OK msg from the BE but the implemetation of Pass-Through in Access does not
provide a completion message at the end.

I don't know about Teradata but several minutes seems to be a long long time
for database engine to create the Table structure ... On my MS SQL Server
2000, it usually takes only a few seconds (100+ Tables, 20+ Views , 50+ SPs,
....). Are you adding data after Table creation in the script also???

 

[Rick Brandt]

Thanks Van,

The interesting is that the database seems to freeze until the query
is complete. I would think Access would run along just fine since the
BE DB engine is doing all the work. All that happens is I lose the
mouse icon. It reappears when the query is finished.

I'll try your suggestion of using a stored procedure or something so
the query returns something to Access.

Passthrough queries are certainly processed synchronously. If you have a
line of code that runs the query followed by a line of code that displays a
message you won't get the message until the query completes. Why not just
use a MsgBox that says "completed"? If there is an error that will be
reported and your error handler can deal with that.

 

[Van T. Dinh]

I think it does gets a "completed" signal from the BE but it is possible
that the Microsoft Access GUI developers decided that a msg "Completed" is
unnecessary and decided not to show it.

If you got help for TeraData, you minght want to search for "Stored
Procedure with Output Parameters". At least, that the term used in MS SQL
2000.

--
HTH
Van T. Dinh
MVP (Access)

It's a "create table as [query]". The select query is the killer.

I'll see about the workaround you suggested. But what I'm still confused
about is how does Access know the query is still processing and when it
stops? Does the BE database give some signal when it's done? If so, that's
pretty mean of Access not to let the end user know! My grant select
passthrough query finishes immediately, but the create table query takes
minutes to complete, so I know Access can somehow "sense" when it's
complete.

Thanks,
-Mark

Yes, I did notice that Access seems to "freeze" while the BE database
engine
is processing the Pass-Through Query. I guess Access waits for an error
or
OK msg from the BE but the implemetation of Pass-Through in Access does
not
provide a completion message at the end.

I don't know about Teradata but several minutes seems to be a long long
time
for database engine to create the Table structure ... On my MS SQL Server
2000, it usually takes only a few seconds (100+ Tables, 20+ Views , 50+
SPs,
....). Are you adding data after Table creation in the script also???