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

 

[June7 via AccessMonster.com]

Have you tried: Where OrderDate > #09/01/2009#
The # symbol delimits value as date type, apostrophe means text, nothing for
numeric.

 

[June7 via AccessMonster.com]

Sorry, meant to show setting variable:
dteOrderDate = "#" & tbxOrderDate & "#"
Where OrderDate > dteOrderDate
Or reference a textbox (or recordset field) directly:
"Where OrderDate > #" & tbxOrderDate & "#"

Have you tried: Where OrderDate > #09/01/2009#
The # symbol delimits value as date type, apostrophe means text, nothing for
numeric.

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

[quoted text clipped - 15 lines]

Brad

 

[John W. Vinson]

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.

You can't do so directly, because the database engine you're passing the query
to has no way to read variables from your VBA project.

What you need to do is construct the SQL string that you're passing through,
concatenating the variable value:

Dim strSQL As String
strSQL = "SELECT blah, blah, blah FROM somewhere " _
& "WHERE OrderDate > '" & Format(varDate, "mm/dd/yyyy") & "'"

and then create assign this SQL string to your query's SQL property.

 

[Brad]

June7,

Thanks for the help, I really appreciate it.

I am struggling with getting a variable in a “Pass Through†query.

I have a test Pass-Through working with a hard-coded value – works nicely.

I have SQL Where statements for local tables that have variables – works
nicely.

I am now trying to figure out how to get a variable into the Where statement
for SQL that is being shipped up to a SQL-Server via ODBC.

Sorry about not explaining clearly what I am trying to do.

Thanks again,
Brad

Sorry, meant to show setting variable:
dteOrderDate = "#" & tbxOrderDate & "#"
Where OrderDate > dteOrderDate
Or reference a textbox (or recordset field) directly:
"Where OrderDate > #" & tbxOrderDate & "#"

Have you tried: Where OrderDate > #09/01/2009#
The # symbol delimits value as date type, apostrophe means text, nothing for
numeric.

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

[quoted text clipped - 15 lines]

Brad

 

[John Spencer]

One way to do this is to build a pass-through query and save it.

Then rebuild the SQL string with VBA

Public Sub BuildAndShowQuery()
Dim strSQL As String
Dim qdef As QueryDef
Dim db As DAO.Database

Set db = CurrentDb()
strSQL = "SELECT * FROM FAQ WHERE FID Between 1 and 10"
Set qdef = db.QueryDefs("NameOfPassThroughQuery")
qdef.SQL = strSQL
DoCmd.OpenQuery "NameOfPassThroughQuery"
End Sub


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[David W. Fenton]

You can't do so directly, because the database engine you're
passing the query to has no way to read variables from your VBA
project.

What you need to do is construct the SQL string that you're
passing through, concatenating the variable value:

Dim strSQL As String
strSQL = "SELECT blah, blah, blah FROM somewhere " _
& "WHERE OrderDate > '" & Format(varDate, "mm/dd/yyyy") & "'"

and then create assign this SQL string to your query's SQL
property.

Wouldn't the usual method here be to define parameters and set them
when you execute the query, instead of rewriting the saved QueryDef
each time you use it?

 

[David W. Fenton]

One way to do this is to build a pass-through query and save it.

Then rebuild the SQL string with VBA

Public Sub BuildAndShowQuery()
Dim strSQL As String
Dim qdef As QueryDef
Dim db As DAO.Database

Set db = CurrentDb()
strSQL = "SELECT * FROM FAQ WHERE FID Between 1 and 10"
Set qdef = db.QueryDefs("NameOfPassThroughQuery")
qdef.SQL = strSQL
DoCmd.OpenQuery "NameOfPassThroughQuery"
End Sub

Why would you just not define the original passthrough with
parameters?

 

[John W. Vinson]

Wouldn't the usual method here be to define parameters and set them
when you execute the query, instead of rewriting the saved QueryDef
each time you use it?

Perhaps I'm misunderstanding - how would SQL (or Oracle or MySQL or whatever)
know what the parameters ARE? I thought passthroughs had to be complete in
themselves.

 

[John Spencer]

Because the passthrough won't understand the parameters. It won't
pickup the values and will error.

If you have a different solution then post it. Or at least post an example.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[David W. Fenton]

Perhaps I'm misunderstanding - how would SQL (or Oracle or MySQL
or whatever) know what the parameters ARE? I thought passthroughs
had to be complete in themselves.

I mis-spoke -- I was testing in an environment that didn't give me
correct results.

However, it does seem to me that there's very little reason to
rewrite a QueryDef -- just utilize your passthrough QueryDef as you
would a server-side view, and apply a WHERE clause to it. I can't
think of any normal criteria that would cause that to get processed
client-side.

 

[David W. Fenton]

If you have a different solution then post it.

I was wrong about parameters, because I incorrectly set up my test
scenario.

But I just don't see the point in rewriting a passthrough QueryDef
just to poke a WHERE clause into it. Why not just use the
passthrough with a WHERE clause? E.g.:

SELECT MyPassThrough.*
FROM MyPassThrough
WHERE MyPassThrough.SomeField = 123

Unless SomeField is an expression calculated using server-side
functions, it won't execute any differently than if you rewrote the
QueryDef to have the WHERE clause in it.

I think the recommendation to rewrite a QueryDef is made far more
often than necessary.

 

[John W. Vinson]

I mis-spoke -- I was testing in an environment that didn't give me
correct results.

However, it does seem to me that there's very little reason to
rewrite a QueryDef -- just utilize your passthrough QueryDef as you
would a server-side view, and apply a WHERE clause to it. I can't
think of any normal criteria that would cause that to get processed
client-side.

I agree. A passthrough in this circumstance is simply the wrong way to go.