Hello,
I have an Access 03 front end with linked tables via ODBC to SQL Server 2000.
My objective is to have the processsing for my INSERT/UPDATE queries occur on the SQL Server side and not access,
since performance is better this way. The INSERT/UPDATE queries are wrapped in Pass-Through queries on the Access Side
and make use of SQL Server's Global Temp tables exclusively. All of these queries are successfully sent from Access
to SQL Server and give expected results.
The problem that I am having is I am not sure how to drop the Global temp tables. I THOUGHT I could execute in VBA like such...
However either "invalid argument" or "Cannot execute a select query" (Last I checked a drop query isn't a select!
) exceptions are thrown when I try to execute the QueryDef.
Keep in mind I am able to successfully execute all the other Pass-Through queries with no problem, it just is the drop tables!
The SQL for "WELL_RUN_##DROP_TABLE1" looks like
QUESTION.
Is there a way to send SQL Server 2000 a Drop Table command via Access?
Thanks for whatever assistance you may be able to provide
P.S. And no, unfortunately I am not allowed to make a stored procedure...
I have an Access 03 front end with linked tables via ODBC to SQL Server 2000.
My objective is to have the processsing for my INSERT/UPDATE queries occur on the SQL Server side and not access,
since performance is better this way. The INSERT/UPDATE queries are wrapped in Pass-Through queries on the Access Side
and make use of SQL Server's Global Temp tables exclusively. All of these queries are successfully sent from Access
to SQL Server and give expected results.
The problem that I am having is I am not sure how to drop the Global temp tables. I THOUGHT I could execute in VBA like such...
Code:
Dim db As DAO.Database
Dim connectedQRY As DAO.QueryDef
Dim qryPlaceHolder As DAO.QueryDef
'WELL_RUN_##DROP_TABLE1 contains ODBC Connection string
Set db = CurrentDb()
Set connectedQRY = db.QueryDefs("WELL_RUN_##DROP_TABLE1")
connectedQRY.Execute dbExecDirect '<---- Run-time Error '3001': Invalid Argument
'connectedQRY.Execute <---- Run-Time Error '3064': Cannot execute a select query
'The next Pass-Through query is just a container for an SQL Server query, and don't have ODBC connection string.
Set qryPlaceHolder = db.QueryDefs("WELL_RUN_##DROP_TABLE2")
connectedQRY.SQL = qryPlaceHolder.SQL
connectedQRY.Execute dbSQLPassThrough

Keep in mind I am able to successfully execute all the other Pass-Through queries with no problem, it just is the drop tables!
The SQL for "WELL_RUN_##DROP_TABLE1" looks like
Code:
USE [rhs_report]
DROP TABLE ##tmpRunningTime;
Is there a way to send SQL Server 2000 a Drop Table command via Access?
Thanks for whatever assistance you may be able to provide
P.S. And no, unfortunately I am not allowed to make a stored procedure...
