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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200909/1

[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 & "#"

June7 wrote:
>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

--
Message posted via http://www.accessmonster.com

[John W. Vinson]

On Sat, 5 Sep 2009 14:51:01 -0700, Brad <Br...@discussions.microsoft.com>
wrote:

>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.
--

John W. Vinson [MVP]

[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
'====================================================

[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

[David W. Fenton]

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:8v16a5hjpf2dpnqgi...@4ax.com:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

John Spencer <spe...@chpdm.umbc> wrote in
news:#hFSgzoL...@TK2MSFTNGP05.phx.gbl:

> 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]

On 6 Sep 2009 21:36:14 GMT, "David W. Fenton" <XXXu...@dfenton.com.invalid>
wrote:

>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]

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in

news:3jh8a55hqf5gee6qq...@4ax.com:

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]

John Spencer <spe...@chpdm.umbc> wrote in

news:#glX9y0L...@TK2MSFTNGP06.phx.gbl:

> 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]

On 7 Sep 2009 23:05:59 GMT, "David W. Fenton" <XXXu...@dfenton.com.invalid>
wrote:

>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.