Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL Pass-Through Query with User prompted parameters

Discussion in 'Information Technology' started by wnfisba, Jul 28, 2006.

  1. wnfisba

    wnfisba
    Expand Collapse
    Guest

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

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:F2BEC52F-A616-4B6D-82DA-6B7928FF8293@microsoft.com
    > 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


    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. wnfisba

    wnfisba
    Expand Collapse
    Guest

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

    > "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    > news:F2BEC52F-A616-4B6D-82DA-6B7928FF8293@microsoft.com
    > > 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
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:D6C99E0A-F22A-41F4-96DC-E3E77C2F0710@microsoft.com
    > 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" wrote:
    >
    >> "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    >> news:F2BEC52F-A616-4B6D-82DA-6B7928FF8293@microsoft.com
    >>> 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


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

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  6. wnfisba

    wnfisba
    Expand Collapse
    Guest

    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 me...wnfisba@yahoo.com

    Thanks again Dirk!

    "Dirk Goldgar" wrote:

    > "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    > news:D6C99E0A-F22A-41F4-96DC-E3E77C2F0710@microsoft.com
    > > 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" wrote:
    > >
    > >> "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    > >> news:F2BEC52F-A616-4B6D-82DA-6B7928FF8293@microsoft.com
    > >>> 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

    >
    > 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 -----
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
    >
     
  7. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:uJ5uP97kGHA.1208@TK2MSFTNGP02.phx.gbl
    >
    > 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, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  8. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:7264FD49-4C1F-41F5-AE29-A3F753A00944@microsoft.com
    > 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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     

Share This Page