SQL Pass-Through Query with User prompted parameters

[wnfisba]

I was wondering if and how it's possible to execute a SQL Server Pass-Through
Query from Access by using qualifying WHERE criteria that is provided by a
user. For instance, I want to provide data filtering by a Start Date and an
End Date where the operator will provide the Start and End Date. How can I do
this with a SQL Server Pass-Through Query in Access???

I appreciate any help and Thank You in advance.

wnfisba

 

[Dirk Goldgar]

I was wondering if and how it's possible to execute a SQL Server
Pass-Through Query from Access by using qualifying WHERE criteria
that is provided by a user. For instance, I want to provide data
filtering by a Start Date and an End Date where the operator will
provide the Start and End Date. How can I do this with a SQL Server
Pass-Through Query in Access???

Using a pass-through query, the best approach is to prompt for the
parameters, then rewrite the SQL for the query before executing it. You
can rewrite the SQL by way of the DAO QueryDef object, using code
similar to this:

Dim strSQL As String

' ... some code here builds a SQL Select statement
' and assigns it to strSQL ...

' Rewrite the query's SQL
CurrentDb.QueryDefs("MyPassThruQuery").SQL = strSQL

 

[wnfisba]

Thanks Dirk. Do you know where there are actual examples of how to do this
out there???

I'm not close to being a VB expert...a novice at best...

 

[Dirk Goldgar]

Thanks Dirk. Do you know where there are actual examples of how to do
this out there???

I'm not close to being a VB expert...a novice at best...

Okay, let's flesh the example out a little. Suppose you're running this
from a form with two text boxes, named "txtFromDate" and "txtToDate",
and a command button named "cmdRunQuery". I'll also assume you have a
pass-through query named "qptMyQuery", and you want to open this query
(to display its results as a datasheet or to execute it, if it's an
action query), when the button is clicked. But the query's SQL needs to
be modified to include the values from the text boxes as criteria.

Code might look something like this:

'----- start of untested example code -----
Private Sub cmdRunQuery_Click()

Dim strFromDate As String
Dim strToDate As String
Dim strSQL

' Make sure we have dates to work with.
' NOTE: for this example, I'm insisting that both
' dates be given. You could work it so that
' either or both dates are optional.

With Me!txtFromDate
If IsNull(.Value) Then
MsgBox "Please specify the 'from' date."
.SetFocus
Exit Sub
Else
strFromDate = Format(.Value, "YYYY-MM-DD")
End If
End With

With Me!txtToDate
If IsNull(.Value) Then
MsgBox "Please specify the 'to' date."
.SetFocus
Exit Sub
Else
strToDate = Format(.Value, "YYYY-MM-DD")
End If
End If
End With

' Build the SQL for the query. This is just
' a simple example.
strSQL = _
"SELECT * FROM SomeTable WHERE " _
"SomeDateField >= '" & strFromDate & _
" AND " & _
"SomeDateField <= '" & strToDate

CurrentDb.QueryDefs("qptMyQuery").SQL = strSQL

DoCmd.OpenQuery "qptMyQuery"

End Sub
'----- end of code -----

 

[wnfisba]

Thanks Dirk.

Can I contact you about this???

I am by no means a VB programmer but am very willing to learn.

Please reply to (e-mail address removed)

Thanks again Dirk!

 

[Dirk Goldgar]

Okay, let's flesh the example out a little. Suppose you're running
this from a form with two text boxes, named "txtFromDate" and
"txtToDate", and a command button named "cmdRunQuery". I'll also
assume you have a pass-through query named "qptMyQuery", and you want
to open this query (to display its results as a datasheet or to
execute it, if it's an action query), when the button is clicked.
But the query's SQL needs to be modified to include the values from
the text boxes as criteria.

Code might look something like this:

'----- start of untested example code -----
Private Sub cmdRunQuery_Click()

Dim strFromDate As String
Dim strToDate As String
Dim strSQL

' Make sure we have dates to work with.
' NOTE: for this example, I'm insisting that both
' dates be given. You could work it so that
' either or both dates are optional.

With Me!txtFromDate
If IsNull(.Value) Then
MsgBox "Please specify the 'from' date."
.SetFocus
Exit Sub
Else
strFromDate = Format(.Value, "YYYY-MM-DD")
End If
End With

With Me!txtToDate
If IsNull(.Value) Then
MsgBox "Please specify the 'to' date."
.SetFocus
Exit Sub
Else
strToDate = Format(.Value, "YYYY-MM-DD")
End If
End If
End With

' Build the SQL for the query. This is just
' a simple example.
strSQL = _
"SELECT * FROM SomeTable WHERE " _
"SomeDateField >= '" & strFromDate & _
" AND " & _
"SomeDateField <= '" & strToDate

CurrentDb.QueryDefs("qptMyQuery").SQL = strSQL

DoCmd.OpenQuery "qptMyQuery"

End Sub
'----- end of code -----

Whoops, a slight correction is needed. Change to:

strSQL = _
"SELECT * FROM SomeTable WHERE " _
"SomeDateField >= '" & strFromDate & _
"' AND " & _
"SomeDateField <= '" & strToDate & "'"

 

[Dirk Goldgar]

Thanks Dirk.

Can I contact you about this???

I am by no means a VB programmer but am very willing to learn.

I'd be happy to help you, but I don't do private consulting except on a
fee-for-service basis. If that's what you want, you can contact me via
the address you'll find on my web site, which is listed in my sig at the
bottom of this message. But if you just have a few questions, it's
likely they can be answered here in the newsgroup.

By the way, I strongly recommend you not post your real e-mail address
in a newsgroup message. Spammers and viruses collect e-mail addresses
from newsgroup posts, so posting your real address can rapidly result in
your being buried in undesirable messages.