Variable in SQL Where Statement in “Pass Through†Query

[Brad]

Just yesterday, I started to experiment with a Pass Through Query to obtain
data from a remote SQL-Server Database via ODBC

I have one simple Pass Though Query that has the following Where Statement.

Where OrderDate > '09/01/2009'

This works nicely.

Now I want to go to the next step and use a variable in the Where Statement.

I have spent quite a bit of time researching this and experimenting, but no
success.

I would really appreciate an explanation and/or example of how to do this.

Thanks in advance for your assistance.


Brad

 

[Banana]

Unfortunately, there is no support for parameters in a pass through query.

One of typical approach is to access the QueryDef that contains the
definition for the pass-through query and alter the SQL property at runtime.

For example:

With CurrentDb.QueryDefs("MyPTQ")
.SQL = ".... WHERE OrderDate > '" & _
MyVariable & _
"'...;"
<Open/Execute the query in whatever method you need>
End With

HTH.

 

[Brad]

Banana,

Thanks for the help.

I am fairly new to Access and I am not familiar with QueryDefs. I have
started to do some digging into them.

I want to make sure that I grasp what you said in your reply. It sounds
like there is not a direct way to include a variable in a SQL “Whereâ€
statement for a Pass-Though Query. However, by using a QueryDef, a
work-around is possible. Is this right?

Thanks again,
Brad

 

[Banana]

Yes, you got it.

QueryDef is basically how VBA refers to those saved queries you create
and store in .mdb.

Just so we're clear, we have to use VBA (maybe macros, too but I've
never used them so don't know that for a fact) to change the SQL
property of a QueryDef object.

Let's consider three possible cases:

1) We want to execute a pass-through query performing some kind of
update operations modifying a table on the backend. This could be INSERT
INTO, UPDATE, or DELETE, but also can be a call into stored procedure
that doesn't return any records. In VBA, it would look like this:

With CurrentDb.QueryDefs("MyPassThroughQuery")
.SQL = "INSERT INTO MyTable (MyColumn) VALUES (" & MyVariable & ");"
.Execute
End With

Of course, the SQL property can be anything besides INSERT INTO and you
can use more than variables as needed.

2) If we want to bind the query to a report, we may change the SQL
property then open report:

With CurrentDb.QueryDefs("MyPassThroughQuery")
.SQL = "INSERT INTO MyTable (MyColumn) VALUES (" & MyVariable & ");"
End With

DoCmd.OpenReport "MyReport" ...

You could put the code into a button's click event so it's automated.
This can be used in conjunction with a form to hold the required parameters.

3) You want to read the result from a Pass-Through Query on a form,
knowing that it's not updatable but sufficient for your purposes. On the
form's Open event, you can do this:

With CurrentDb.QueryDefs("MyPassThroughQuery")
.SQL = "INSERT INTO MyTable (MyColumn) VALUES (" & MyVariable & ");"
Set Me.Recordset = .OpenRecordset
End With


Now, to be sure, there are few other techniques available to us including:

1) Using a temporary querydef rather than a actual query, and using it
for all passthrough queries:

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.CreateQueryDef("",,"<SQL Statement>")

qdf.Connect = <Connection string>

2) Saving all passthrough query as "template" and executing with a
replace upon the temporary query def rather than re-writing the whole
SQL statement.

TempQdf.SQL = Replace(SavedQdf.SQL, "@ReplaceThis@", MyVariable)

and then couple more I couldn't think of. I'm sure that if you google
for "Dynamic Pass Through AccessQuery" or maybe "variables in Pass
Through Access Query", you should be able to find several literature
discussing this point.

I hope this helps.

 

[Brad]

Banana,

GOOD GOOD GOOD!!!!

Got it working.

Thanks for your assistance, I appreciate it.

Brad

 

[Brad]

June7, John, John

GOOD GOOD GOOD!!!!

Got it working.

Thanks for your assistance, I appreciate it.

Brad

 

[Albert D. Kallal]

Just yesterday, I started to experiment with a Pass Through Query to
obtain
data from a remote SQL-Server Database via ODBC

You have some good feedback here.

Keep in mind that for a standard form, or a report based on a linked table
can consume a where clause and ONLY those records meeting the criteria will
be pulled from sql server.

So, in many cases, you gain nothing by using a pass-through query in place
of linked table.

So, you could go:

strWhere = "InvoiceDate = #09/01/2009#"

docmd.OpenForm "frmInvoices",,,strWhere

Or you can use a report with:

docmd.OpenReport "rptInvoices",acViewPreview,,strWhere

 

[Brad]

Albert,

Thanks for your ideas. I was not aware of these options.

I am truly amazed by how helpful people are on this forum.

Sincerely,
Brad

 

[David W. Fenton]

Yes, you got it.

QueryDef is basically how VBA refers to those saved queries you
create and store in .mdb.

Just so we're clear, we have to use VBA (maybe macros, too but
I've never used them so don't know that for a fact) to change the
SQL property of a QueryDef object.

Let's consider three possible cases:

1) We want to execute a pass-through query performing some kind of
update operations modifying a table on the backend. This could be
INSERT INTO, UPDATE, or DELETE, but also can be a call into stored
procedure that doesn't return any records. In VBA, it would look
like this:

With CurrentDb.QueryDefs("MyPassThroughQuery")
.SQL = "INSERT INTO MyTable (MyColumn) VALUES (" & MyVariable
& ");" .Execute
End With

Of course, the SQL property can be anything besides INSERT INTO
and you can use more than variables as needed.

You don't need a saved QueryDef to do this -- you just have to
include dbSQLPassThrough in the options:

strSQL = "INSERT INTO MyTable (Field) VALUES (" & Variable & ");"
CurrentDB.Execute strSQL, dbSQLPassThrough

I don't believe in editing saved QueryDefs any more than absolutely
necessary, and this is one case where it's completely unnecessary
(except if for some reason you want the saved QueryDef to persist,
or, e.g., if you're using it more than one place at once, such as in
a main report and a subreport -- though that's something of an
artificial construct, seems to me).

2) If we want to bind the query to a report, we may change the SQL
property then open report:

With CurrentDb.QueryDefs("MyPassThroughQuery")
.SQL = "INSERT INTO MyTable (MyColumn) VALUES (" & MyVariable
& ");"
End With

DoCmd.OpenReport "MyReport" ...

You could put the code into a button's click event so it's
automated. This can be used in conjunction with a form to hold the
required parameters.

While this is the only way to use a passthrough as a recordsource
for a report or form when you want the processing of the WHERE
clause to all be done by the server, I would say that the number of
cases where you *need* to do that is very, very small.

A saved passthrough QueryDef without any WHERE clause can be used in
the recordsource of a form or query and construct the WHERE clause
on the fly. In the vast majority of cases, this is going to work
just fine -- Jet will hand off the criteria to the server and all
will be done server-side. It's hard for me to imagine a situation
where the WHERE clause *must* be untouched by Jet, since Jet almost
never touches the WHERE clause in the first place. The only
exception is if your criteria have to use server-side functions,
e.g., T-SQL functions when using SQL Server. This seems like a very
rare situation to me, and I'm not sure that a saved passthrough
QueryDef is necessarily the best way to handle it.

3) You want to read the result from a Pass-Through Query on a
form, knowing that it's not updatable but sufficient for your
purposes. On the form's Open event, you can do this:

With CurrentDb.QueryDefs("MyPassThroughQuery")
.SQL = "INSERT INTO MyTable (MyColumn) VALUES (" & MyVariable
& ");" Set Me.Recordset = .OpenRecordset
End With

As with #1, you don't need a saved QueryDef for this purpose.

 

[Brad]

David,

Thanks for your ideas on QueryDefs. I am still in the “learning phase†with
Access so I appreciate the help that people like you have provided in this
forum.

Here is what I am trying to do… I want to use Access 2007 to retrieve a
subset of rows from a SQL-Server database that resides at the firm that hosts
our website. For example, one SQL-Server table has 30,000 rows and I want to
pull down about 100 rows (the orders for one day). I do not need to update
any data. I simply want to read the data from SQL-Server and get it into
Access where we can work with it easily.

You shared an example like this…
strSQL = "INSERT INTO MyTable (Field) VALUES (" & Variable & ");"
CurrentDB.Execute strSQL, dbSQLPassThrough

Here is a sample of the SQL that I am using to read one day’s worth of orders.
Select * from Orders where Order_Date = “09/06/09â€

Would it be possible to use the pass-through option that you illustrated
with SQL like mine? I am confused about where I would look for the data if I
have not used a Query.

Thanks again for your assistance.


Brad

 

[Banana]

You don't need a saved QueryDef to do this -- you just have to
include dbSQLPassThrough in the options:

strSQL = "INSERT INTO MyTable (Field) VALUES (" & Variable & ");"
CurrentDB.Execute strSQL, dbSQLPassThrough

Excellent. I never really liked the idea of editing a saved querydef
everytime it was run. I will have to add it to the toolbox.

That said, I had a quick look at the help, and I don't see any
specifications for the connection string in the Execute method. I would
venture to guess (without testing) that the above is only good if we
have a linked table to work with in first place. I imagine that will be
the norm for most cases so shouldn't be a much of restriction.

However in some cases where we may have a statement that includes
non-linked tables or call into stored procedures, I think we're stuck
with a saved querydef, or at least using a temporary querydef. But then
again, in those situations, I'd be already using ADO anyway.

 

[John W. Vinson]

Here is what I am trying to do… I want to use Access 2007 to retrieve a
subset of rows from a SQL-Server database that resides at the firm that hosts
our website. For example, one SQL-Server table has 30,000 rows and I want to
pull down about 100 rows (the orders for one day). I do not need to update
any data. I simply want to read the data from SQL-Server and get it into
Access where we can work with it easily.

It's certainly not *essential* to use a PassThrough query for this. An ODBC
link to a properly indexed SQL database would have NO problem whatsoever with
this; a passthrough might be necessary if you were invoking stored procedures
or needing to do DDL queries or the like.

Try setting up an ODBC connection, using File... Get External Data... Link...
ODBC Databases and just create a linked table. A query in native Access format

Select * from Orders where Order_Date = Date()

or

Select * from Orders where Order_Date = #09/06/09#

should return your day's orders pretty much instantly. If you want to copy
these records to a local table (say to update the local copy without updating
the SQL data) make this an Append query.

 

[Sylvain Lafontaine]

To my knowledge, dbSQLPassThrough should only apply (or be used with) to
database objects that have been opened against a SQL-Server; so it cannot be
used for CurrentDB because this is standard JET database. You can get a
full example at http://support.microsoft.com/kb/184749

In your case, instead of saving a querydef object - which would lead to
increase in the size of the database file before the next compaction, you
should create an unnamed querydef object but only when you realy need to use
a passthrough query (for those queries that are too complex to JET or for
calling a stored procedure) because these are read-only. When a query
against one or more linked table is too complexe for JET, it will revert to
do a table scan on the sql-server, with a concomitant (bif) drop of the
overall performance. You can easily see when it's the case by using the
SQL-Server Profiler to see exactly what's going on between JET and
SQL-Server.

For an exemple of creating an unnamed querydef object, see
http://support.microsoft.com/kb/232493

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[David W. Fenton]

I simply want to read the data from SQL-Server and get it into
Access where we can work with it easily.

You shared an example like thisƒ Ý
strSQL = "INSERT INTO MyTable (Field) VALUES (" & Variable & ");"
CurrentDB.Execute strSQL, dbSQLPassThrough

Executing that SQL as a passthrough won't do anything in your Access
database -- it will insert values into MyTable on the *server*.

I don't think you need a passthrough at all, John Vinson explains in
another reply.

 

[David W. Fenton]

Excellent. I never really liked the idea of editing a saved
querydef everytime it was run. I will have to add it to the
toolbox.

That said, I had a quick look at the help, and I don't see any
specifications for the connection string in the Execute method.

I hadn't thought about that.

I would
venture to guess (without testing) that the above is only good if
we have a linked table to work with in first place. I imagine that
will be the norm for most cases so shouldn't be a much of
restriction.

I have to admit I'm kind of confused about the interaction between
linked tables and passthroughs. In my zeal to recommend parameters
on passthroughs, I was testing with a linked table (where you could
declare the parameters like you normally would, convert the query to
passthrough, and it would work fine), and did find that it was
impossible to do with the corresponding connect string.

I don't know what would happen if one came up with a passthrough
that used the server's dialect for declaring parameters and saved
that as a passthrough.

However in some cases where we may have a statement that includes
non-linked tables or call into stored procedures, I think we're
stuck with a saved querydef, or at least using a temporary
querydef. But then again, in those situations, I'd be already
using ADO anyway.

I don't know why one would need to rewrite the saved QueryDef just
to poke a WHERE clause into it -- just use the QueryDef in a SELECT
statement with a WHERE clause. The circumstances where that's not
going to work are very, very few.

On the other hand, the only reason to use a passthrough would be
when you have something that Jet doesn't handle well and needs to be
passed off to the server. But the one thing that Jet *does* handle
well, seems to me, is handing off WHERE clauses for execution on the
server, so I still don't know what the justification for putting the
WHERE clause inside your passthrough would be.

 

[David W. Fenton]

To my knowledge, dbSQLPassThrough should only apply (or be used
with) to database objects that have been opened against a
SQL-Server;

The article you cite does not say that.

so it cannot be
used for CurrentDB because this is standard JET database. You can
get a full example at http://support.microsoft.com/kb/184749

If your CurrentDB has linked tables, it will work. ODBC in Access is
entirely handled by Jet, whether or not you're going through a
linked table or by opening the database by initializing a DAO
database object with DBEngine.OpenDatabase operating on an ODBC
connect string.

 

[Banana]

I have to admit I'm kind of confused about the interaction between

linked tables and passthroughs. In my zeal to recommend parameters
on passthroughs, I was testing with a linked table (where you could
declare the parameters like you normally would, convert the query to
passthrough, and it would work fine), and did find that it was
impossible to do with the corresponding connect string.

Indeed, for most of cases, we can do just as well with good plain old
saved parameter queries or WhereCondition, as you said, Jet is good at
handing off the Where component. Of course, we have to be very careful
with how we phrase the part. For example:

WHERE Year(SomeDate) = '1992'

Would force a client-side evaluation because Year() function expects a
numeric argument, not a string argument. There's really no indication
that client-side evaluation will be done until we go to the server and
look at the log and noticing that Jet is requesting for two columns, the
key and the "SomeData" column but there's no Year function being passed
via ODBC even though ODBC can understand and pass the Year() to backend.
Since there's no clear indication inside the Access environment, I can
easily see how many people would write queries that would force
unnecessary evaluation and never knowing about it until one day users
complain of it being too slow.

I really, really wish that Microsoft would wise up and enable support
for parameters inside pass-through queries, which would also facilicate
us to use the server's native functions. We can pass in few of SQL
functions and use Jet's functions for the SELECT clause with no ill
effect, but it's a guessing game, really. I'd rather not guess. Then
when we consider the fact that ADO can provide us with updatable,
bind-able recordset using native SQL dialect, I don't see why Microsoft
can't implement it via the UI as well. C'est la vie.

I don't know what would happen if one came up with a passthrough
that used the server's dialect for declaring parameters and saved
that as a passthrough.

Well, I know that it's possible to pass multiple statements in a single
passthrough query, providing that multiple statements is supported &
enabled by the backend & the ODBC driver. That said (and without
testing), we'd still have to paste in the literal values because if the
parameters aren't given a default value, we get an error, and we
certainly won't get that little parameter input box.

On the other hand, the only reason to use a passthrough would be
when you have something that Jet doesn't handle well and needs to be
passed off to the server. But the one thing that Jet *does* handle
well, seems to me, is handing off WHERE clauses for execution on the
server, so I still don't know what the justification for putting the
WHERE clause inside your passthrough would be.

Off the top of my head, I would say by far the best justification would
be the ability to use server's native functions that is not available in
Jet.

But as I alluded to earlier, it's more likely that I'd be using ADO
because passthrough's nonupdatability severely limits its value as a
bind-able recordset for forms, though it may be more useful for reports.
For some reasons, though, it's seldom that I do any reports inside
Access. It may be just a chance that the clients I've worked for wants
their reports in Excel.

But I'm just glad that there's a way to avoid rewriting saved querydef.
I can only hope Microsoft eventually get around to fix the shortcomings
with the passthrough queries.

 

[David W. Fenton]

Indeed, for most of cases, we can do just as well with good plain
old saved parameter queries or WhereCondition, as you said, Jet is
good at handing off the Where component. Of course, we have to be
very careful with how we phrase the part. For example:

WHERE Year(SomeDate) = '1992'

Would force a client-side evaluation because Year() function
expects a numeric argument, not a string argument.

Well, I wouldn't even write that WHERE clause in Access with a Jet
back end -- it won't use indexes, so I'd instead use BETWEEN
#1/1/1992# and #12/31/1992#. That won't cause a table scan when sent
to a server, either.

There's really no indication
that client-side evaluation will be done until we go to the server
and look at the log and noticing that Jet is requesting for two
columns, the key and the "SomeData" column but there's no Year
function being passed via ODBC even though ODBC can understand and
pass the Year() to backend.

To me, anything that won't use an index in Access is something you
won't want to send to a server. And this follows one of the
principles I've been using in designing Access apps for a long time:

If you design an efficient Access/Jet app, it will upsize easily,
because the same things that make for an efficient client/server app
also make an Access/Jet app more efficient.

Since there's no clear indication inside the Access environment, I
can easily see how many people would write queries that would
force unnecessary evaluation and never knowing about it until one
day users complain of it being too slow.

I think that once a serrer back end is in the mix, the skill level
has already exceeded that of the novice/intermediate Access user. I
know that Microsoft would like to think that's not the case, but it
is, so far as I can see.

I really, really wish that Microsoft would wise up and enable
support for parameters inside pass-through queries, which would
also facilicate us to use the server's native functions. We can
pass in few of SQL functions and use Jet's functions for the
SELECT clause with no ill effect, but it's a guessing game,
really. I'd rather not guess.

I think their response would be "stored procedure", but, of course,
that can't be bound to a form/report.

Then
when we consider the fact that ADO can provide us with updatable,
bind-able recordset using native SQL dialect, I don't see why
Microsoft can't implement it via the UI as well. C'est la vie.

The whole ADO thing in Access was never very successfully
implemented. There are just too many cases where you think you might
get an ADO recordset but instead get a DAO one. Now, that doesn't
upset *me*, because I don't *want* ADO recordsets, but for those who
want to use the capabilities of ADO, it can cause real frustration.

Well, I know that it's possible to pass multiple statements in a
single passthrough query, providing that multiple statements is
supported & enabled by the backend & the ODBC driver. That said
(and without testing), we'd still have to paste in the literal
values because if the parameters aren't given a default value, we
get an error, and we certainly won't get that little parameter
input box.

Yes, I guess so.

But again, I can't think of situations where:

1. you've written good index-using criteria,

AND

2. it can't be done by simply applying your WHERE clause to the
passthrough itself, e.g.:

SELECT MyPassThrough.* FROM MyPassThrough WHERE ...

There seems to me to be little advantage to having parameters
defined in the passthrough query itself -- if you want the "joy" of
parameters, you can easily define them in the SELECT statement
above, and Jet will process them efficiently *so long as you've
followed rule #1*.

Off the top of my head, I would say by far the best justification
would be the ability to use server's native functions that is not
available in Jet.

Examples of those would be....?

Again, it seems to me that most passthroughs are just client-side
views, and properly ought to be views on the server in the first
place. Is see the role of passthroughs and views as efficiently
processing a SELECT for consumption by the client application(s).
Criteria applied to that results should be easily handled by Jet
passing it off to the server.

The only disadvantage of views, of course, is that you have to link
to them lika a table if you want maximum ease of use. Another
approach, though, would be to create a saved QueryDef with connect
string that returns the rows of the server-side view, but at that
point, you've perhaps written almost as much SQL as it would take to
create the passthrough.

But I'd still vote for most passthroughs belong as views on the
server, regardless of ease of use issues in compared to the
passthrough. My reason is simply that it's better to have
server-side processing defined *server-side* insofar as that is
possible. Putting that same SQL in the application front end seems
to me to be a problematic thing to do.

[]

But I'm just glad that there's a way to avoid rewriting saved
querydef. I can only hope Microsoft eventually get around to fix
the shortcomings with the passthrough queries.

I think passthroughs are mostly of use to those who don't have
administrative permissions on the server, or for use with servers
that don't have views (e.g., MySQL before version 5).

 

[Banana]

Apologies; I somehow missed the reply. Better late than never, I suppose.

Well, I wouldn't even write that WHERE clause in Access with a Jet
back end -- it won't use indexes, so I'd instead use BETWEEN
#1/1/1992# and #12/31/1992#. That won't cause a table scan when sent
to a server, either.

Well, my point was basically that it's easy to make mistakes in writing
queries that would cause unneeded evaluations and make extra mistake of
blaming Access for what was my own mistake. You are correct that the
Year() would be a poor way to find all dates within single year, but
this does not mean we shouldn't use functions at all, especially if we
can keep the function to one side of the expression and the indexed
column on the other side. Furthermore, don't forget that even with a
Year(..) = 1992, the table scan is done on server's side, whereas
Year(..) = '1992' forces table scan on client's side which is always slower.

I think that once a serrer back end is in the mix, the skill level
has already exceeded that of the novice/intermediate Access user. I
know that Microsoft would like to think that's not the case, but it
is, so far as I can see.

Unfortunately, my experience has been that it's too easy to link tables
to ODBC sources and many people posts questions about why their queries
aren't working the way and most of them has never read the whitepaper on
Jet/ODBC connectivity.

I think their response would be "stored procedure", but, of course,
that can't be bound to a form/report.

Well, when using ADO, we can indeed bind the resultset of stored
procedure to a form and still have it updatable, providing we follow
same set of rules we have to follow for writing queries for a form's
recordsource (e.g. one-one correspondence between a row in the resultset
of a sproc and the base table). With reports, it's not as big concern
because we don't really need updatability.

The whole ADO thing in Access was never very successfully
implemented. There are just too many cases where you think you might
get an ADO recordset but instead get a DAO one. Now, that doesn't
upset *me*, because I don't *want* ADO recordsets, but for those who
want to use the capabilities of ADO, it can cause real frustration.

Hmmm, I find it hard to believe we would get a DAO object when we
expected ADO object. While several Access objects indeed support both
DAO and ADO recordsets, it's fairly consistent in what model it actually
uses. Anything you create via Access UI is going to be a DAO object. In
VBA, you could create either DAO or ADO object and bind it to the form.
It's only in the case where you've bound a form to ADO recordset would
you get a ADO recordset from the form's recordset. That's usually
specific enough so I think the behavior is fairly consistent.

But again, I can't think of situations where:

1. you've written good index-using criteria,

As mentioned you still can use functions and indexes, provided you keep
function evaluations on one side and the indexed column on the other
side of evaluation.

An example is when we want to get all records from a many-side table
based on a say, MAX() value from another table.

WHERE AColumn > (SELECT MAX(OtherColumn) FROM OtherTable)

This of course assumes the both original and other table are on the
backend. There also may be differences in how backend optimizes the
query and we want to capitalize on this to help the query run efficient
as possible so there may be cases where it's preferable to use backend's
version of functions even though it may be supported & understood by
Jet/ODBC.

2. it can't be done by simply applying your WHERE clause to the
passthrough itself, e.g.:

SELECT MyPassThrough.* FROM MyPassThrough WHERE ...

There seems to me to be little advantage to having parameters
defined in the passthrough query itself -- if you want the "joy" of
parameters, you can easily define them in the SELECT statement
above, and Jet will process them efficiently *so long as you've
followed rule #1*.

Hmm. Interesting idea. I actually never though to overlay a parameter
query on top of a passthrough query, but that's two objects for one
function, and I'm loath to create several objects. This could get messy
pretty fast enough, even if the performance difference was minimal.
Thanks for the idea, though.

Again, it seems to me that most passthroughs are just client-side
views, and properly ought to be views on the server in the first
place. Is see the role of passthroughs and views as efficiently
processing a SELECT for consumption by the client application(s).
Criteria applied to that results should be easily handled by Jet
passing it off to the server.

Good point. I've found that I seldom use passthrough queries, more
likely to write regular queries referencing linked tables or using VBA
to execute SQL. This may be merely a consequence of the fact that
passthrough are by definition nonupdatable which renders them useless
for vast majority of possible applications within forms, though they may
be of some use for reports where updatability is a moot point.

I do agree with you that in principle, most of processing should stay on
server-side, but I think the way Access works runs counter to that
ideal. We have to write SQL query to build a recordsource to bind forms
to, and we can't rely on stored procedures unless we do it in VBA & ADO,
adding more development work/time, and views are not always the right
answer. For instance, we may still want to add extra conditions at the
runtime rather than at the time we create the views, and views can't
take in parameters, so we need to use a stored procedure anyway if we
need to use parameters).

Until Microsoft make it easier to refer to stored procedures & passing
in parameters without using VBA to set everything, I think the Access's
paradigm will continue to be in conflict with the ideal of keeping
server-side processing on server among other things.

Indeed, for most of cases, we can do just as well with good plain
old saved parameter queries or WhereCondition, as you said, Jet is
good at handing off the Where component. Of course, we have to be
very careful with how we phrase the part. For example:

WHERE Year(SomeDate) = '1992'

Would force a client-side evaluation because Year() function
expects a numeric argument, not a string argument.

Well, I wouldn't even write that WHERE clause in Access with a Jet
back end -- it won't use indexes, so I'd instead use BETWEEN
#1/1/1992# and #12/31/1992#. That won't cause a table scan when sent
to a server, either.

There's really no indication
that client-side evaluation will be done until we go to the server
and look at the log and noticing that Jet is requesting for two
columns, the key and the "SomeData" column but there's no Year
function being passed via ODBC even though ODBC can understand and
pass the Year() to backend.

To me, anything that won't use an index in Access is something you
won't want to send to a server. And this follows one of the
principles I've been using in designing Access apps for a long time:

If you design an efficient Access/Jet app, it will upsize easily,
because the same things that make for an efficient client/server app
also make an Access/Jet app more efficient.

Since there's no clear indication inside the Access environment, I
can easily see how many people would write queries that would
force unnecessary evaluation and never knowing about it until one
day users complain of it being too slow.

I think that once a serrer back end is in the mix, the skill level
has already exceeded that of the novice/intermediate Access user. I
know that Microsoft would like to think that's not the case, but it
is, so far as I can see.

I really, really wish that Microsoft would wise up and enable
support for parameters inside pass-through queries, which would
also facilicate us to use the server's native functions. We can
pass in few of SQL functions and use Jet's functions for the
SELECT clause with no ill effect, but it's a guessing game,
really. I'd rather not guess.

I think their response would be "stored procedure", but, of course,
that can't be bound to a form/report.

Then
when we consider the fact that ADO can provide us with updatable,
bind-able recordset using native SQL dialect, I don't see why
Microsoft can't implement it via the UI as well. C'est la vie.

The whole ADO thing in Access was never very successfully
implemented. There are just too many cases where you think you might
get an ADO recordset but instead get a DAO one. Now, that doesn't
upset *me*, because I don't *want* ADO recordsets, but for those who
want to use the capabilities of ADO, it can cause real frustration.

Well, I know that it's possible to pass multiple statements in a
single passthrough query, providing that multiple statements is
supported & enabled by the backend & the ODBC driver. That said
(and without testing), we'd still have to paste in the literal
values because if the parameters aren't given a default value, we
get an error, and we certainly won't get that little parameter
input box.

Yes, I guess so.

But again, I can't think of situations where:

1. you've written good index-using criteria,

AND

2. it can't be done by simply applying your WHERE clause to the
passthrough itself, e.g.:

SELECT MyPassThrough.* FROM MyPassThrough WHERE ...

There seems to me to be little advantage to having parameters
defined in the passthrough query itself -- if you want the "joy" of
parameters, you can easily define them in the SELECT statement
above, and Jet will process them efficiently *so long as you've
followed rule #1*.

Off the top of my head, I would say by far the best justification
would be the ability to use server's native functions that is not
available in Jet.

Examples of those would be....?

Again, it seems to me that most passthroughs are just client-side
views, and properly ought to be views on the server in the first
place. Is see the role of passthroughs and views as efficiently
processing a SELECT for consumption by the client application(s).
Criteria applied to that results should be easily handled by Jet
passing it off to the server.

The only disadvantage of views, of course, is that you have to link
to them lika a table if you want maximum ease of use. Another
approach, though, would be to create a saved QueryDef with connect
string that returns the rows of the server-side view, but at that
point, you've perhaps written almost as much SQL as it would take to
create the passthrough.

But I'd still vote for most passthroughs belong as views on the
server, regardless of ease of use issues in compared to the
passthrough. My reason is simply that it's better to have
server-side processing defined *server-side* insofar as that is
possible. Putting that same SQL in the application front end seems
to me to be a problematic thing to do.

[]

But I'm just glad that there's a way to avoid rewriting saved
querydef. I can only hope Microsoft eventually get around to fix
the shortcomings with the passthrough queries.

I think passthroughs are mostly of use to those who don't have
administrative permissions on the server, or for use with servers
that don't have views (e.g., MySQL before version 5).

 

[David W. Fenton]

For instance, we may still want to add extra conditions at the
runtime rather than at the time we create the views, and views
can't take in parameters, so we need to use a stored procedure
anyway if we need to use parameters).

I think that parameters are vastly overrated, to be honest.

I also think one should check the SQL Profiler to see what happens
if you have a saved QueryDef with parameters where the source is a
server-side view. My bet is that Jet sends it to the server and it
gets appropriately parameterized serverside. You don't even need a
saved QueryDef for this -- you can just write on-the-fly SQL with
parameters.