Deciding on a Pass-Thru Query is usually based on query
performance. A query that should run fast may take forever. Using the SQL Server Profiler will confirm whether or not ODBC is inefficiently translating
a SQL Server query -- instead of a single request, thousand of requests
get posted to SQL Server. (Missing indexes will also cause poor performance.)
A Pass-Thru Query can be written as a stored procedure in a SQL Server database.
Here is a simple Delete query written as a stored procedure.
The first difference between MS Access & SQL Server is permissions.
The stored procedure will need an appropriate execute permissions setting to work
from MS Access through ODBC.
Next, on the MS Access side, write a Pass-Thru Query something like the following:
Parameters follow the stored procedure & must abide by SQL Server syntax, hence a
date is wrapped in single quotes.
The ODBC connection for the Pass-Thru Query can be set by viewing the query in design view
then viewing the properties & setting the ODBC connection.
If you did everthing correctly so far, the Pass-Thru Query will work from MS Access
when you double click the query in the database view.
It is, however, unusual to run a Pass-Thru Query with the same parameter every time.
So I use VBA to set the parameters as follows:
Oddity #1:
The table against which my Delete query was running was 100K records. When I
tested the Pass-Thru Query from the database view, it ran in less than a second.
When run from the VBA Query.Execute, it hung the computer. Why would there be
any difference between execution of a query from the database view or from VBA?
Not clear to me. However, after indexing the DateFld, the VBA ran just as fast
as the database view execution.