Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 20th, 2003, 12:41 PM
Registered User
 
Join Date: Oct 2003
Location: DC, DC, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default pass-through query with input parmeter

Hi friends,

How to pass a parmater with path-through
query in Access2000?
i created a stored procedure which need to input a parameter. I want to run this procedure by path-through query.

Thanks for your help!

Jimmy

Reply With Quote
  #2 (permalink)  
Old October 20th, 2003, 02:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Jimmy,

Here's an approach I picked up from the "Microsoft Access Developer's Guide to SQL Server" by Mary Chipman and Andy Baron.

This pass-through query executes a parameterized stored procedure against the SQL Server version of the Northwind database using a DSN-less ODBC connection.

The query returns a single employee record using EmployeeID as criteria.

First the pass-through query get's built, then a stored procedure gets created on SQL Server which the pass-through query will execute, then some code gets written to re-build the pass-through query everytime a new employee record is requested.

The ExecutePassThrough sub recieves an Employee ID number as an argument and concatenates it to the Pass-Through query's SQL. BuildPassThrough is then called which uses ADOX to reset the pass- through query's command text.

 ================================================== =======
 Design the Pass-Through Query in Access (.mdb)
 ================================================== =======

 1. In Query design view, from the main menu select
    Query | SQL Specific | Pass-Through.

 2. Type "EXEC up_parmsel_Employees" as the query's SQL
    statement.

 3. Click the properties button on the Toolbar to open the
    Query properties dialog. Enter the following DSN-less
    connection string in the ODBC Connect Str property:

    ODBC;Driver={SQL Server};Server=(local); _
    Database=Northwind;Trusted_Connection=Yes

    NT authentication is used here.

    All the other default properties are fine. Clicking
    the ellipsis in the ODBC Connect Str property
    field will open the DSN Data Source dialog if you
    want to use a DSN.

 4. Save the query as "qsptEmployees" and close without
    running (the stored procedure doesn't exit yet).


 ================================================== =======
 Create the Stored Procedure in SQL Server 2000
 ================================================== =======

 1. With SQL Server running open the Query Analyzer.

 2. Select the Northwind database form the Toolbar's
    drop-down list.

 3. Type the following SQL in the Query Analyzer, parse,
    and run:

    CREATE PROC up_parmsel_Employees
      @EmployeeID int = NULL
    AS
    SELECT EmployeeID, LastName, FirstName, Title
    FROM Northwind.dbo.Employees
    WHERE EmployeeID = @EmployeeID

 4. Test that the proc was created successfully by
    clearing the Query Analyzer window and running the
    following command in the Query Analyzer:

    Exec up_parmsel_Employees 1

    A single employee record for "Nancy Davolio" should be
    displayed. The query and the proc it executes are now
    ready to go. Just need code to set the parameter that
    the proc expects.


 ================================================== =======
 Write the Parameter Setting Module in Access
 ================================================== =======

 1. Open a new module in Access and set a Reference to
    ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
    library.

 2. Paste in the following two subs:

     Public Sub ExecutePassThrough(lngParam As Long)
       Dim strTSQL As String
       Dim strQueryName As String

       strQueryName = "qsptEmployees"
       strTSQL = "EXEC up_parmsel_Employees " & lngParam

       Call BuildPassThrough(strQueryName, strTSQL)

       DoCmd.OpenQuery strQueryName
     End Sub


     Public Sub BuildPassThrough( _
       ByVal strQName As String, _
       ByVal strSQL As String)

       Dim cat As ADOX.Catalog
       Dim cmd As ADODB.Command

       Set cat = New ADOX.Catalog
       Set cat.ActiveConnection = CurrentProject.Connection

       Set cmd = cat.Procedures(strQName).Command

       ' Verify query is a pass-through query
       cmd.Properties( _
          "Jet OLEDB:ODBC Pass-Through Statement") = True

       cmd.CommandText = strSQL

       Set cat.Procedures(strQName).Command = cmd

       Set cmd = Nothing
       Set cat = Nothing

     End Sub


 ================================================== =======
 Test the Procedures, Resetting the Queries Parameters
 ================================================== =======

 1. In the immediate window, type the following and hit
    Enter:

    ExecutePassThrough (1)


 2. A query should open in datasheet view displaying Nancy
    Davolio's employee record.

    Or you could return the result set to an ADO recordset
    with:

    Set rst = New ADODB.Recordset
    rst.Open strQueryName, CurrentProject.AccessConnection

    You could use the following syntax:

    lngParam = [Forms]![frmSearch]![txtSearchValue]
    Call ExecutePassThrough(lngParam)

    to get the parameter value from a form.

HTH,

Bob

Reply With Quote
  #3 (permalink)  
Old April 2nd, 2004, 05:35 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

Tried this method for passing parameters and it worked great. However, I need to return the resultset to a report, and not sure how to call the module from the report in order to obtain the resultset, any idea?
Reply With Quote
  #4 (permalink)  
Old April 22nd, 2004, 07:42 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

In reponse to:

"Hello blinton25

You received the following message from: benmay14 (blm14@columbia.edu)

At: http://p2p.wrox.com/

Did you ever get report using a pass through query with arguments
problem solved? I am trying to do this myself... The problem I am describing
is available here:

http://p2p.wrox.com/topic.asp?TOPIC_ID=5485

Any help you could provide me would be greatly appreciated. Thanks!
"

Hi,

Sorry about the delay in responding, here goes:

1. Make the record source of the report TArrivalsByRegion.

2. In report open, create a querydef, to which you will append your form parameters.

Private Sub Report_Open(Cancel As Integer)

    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form


          ' Set database variable to current database.
     Set dbsReport = CurrentDb
     Set frm = Forms!DateRangePreviousCurrentYear
     ' Open QueryDef object.
     Set qdf = dbsReport.QueryDefs("TArrivalsByRegion")


 Dim rstReport As DAO.Recordset

qdf.SQL = "EXEC TArrivalsByRegion " & Forms!DateRangePreviousCurrentYear!PrevYear & "," & Forms!DateRangePreviousCurrentYear!CurrYear
qdf.ReturnsRecords = True


 ' Open Recordset object.

      Set rstReport = qdf.OpenRecordset()
rstReport.MoveFirst


3. Create a Passthough query which contains the following statement:

EXEC TArrivalsByRegion

4. Create a Stored Procedure with the following:


CREATE PROCEDURE TArrivalsByRegion

@PrevYear1 varchar(4),@CurrYear1 varchar(4)

AS

SELECT regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM dbo.RegionofResidenceCrosstabFunc(@PrevYear1,@Curr Year1)

Go


Interesting thing to note, after I run the code and check the Passthrough query it has:

EXEC TArrivalsByRegion 2001,2003

if I used the parameters 2001, 2003.

Looks like if I hardcoded the values but this isn't so because if I try different parameters, e.g 2001, 2002 the Passthough query is updated with these parameters.

Let me know if this helps.
Reply With Quote
  #5 (permalink)  
Old February 8th, 2018, 09:21 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default pass throuh query with parameter in report

Hello,
14 years later ...
I just looked for information on passing parameters to pass throuh query. after much research I found this excellent article.
I bring my contribution on the part concerning the passing parameters to a report. In fact the trick is to dynamically change the query before launching the report. the code presented contains many useless things, I allow myself to present the following code for the part:
Code:
   Private Sub Report_Open
   'it is enough to write:
   Dim qdf As QueryDef
   ††††† Set qdf = currentdb.QueryDefs ("TArrivalsByRegion")
   qdf.SQL = "EXEC TArrivalsByRegion" & Forms! DateRangePreviousCurrentYear! PrevYear & "," & Forms! DateRangePreviousCurrentYear! CurrYear
the rest of the article is ok for me
my english is not operational, I hope that the translator gives a good translation
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
pass a form input as a parameter to an access quer howbadboy Javascript How-To 1 March 1st, 2005 04:49 AM
SQL query from input form skywalk22 SQL Server ASP 7 October 21st, 2004 09:24 AM
Pass-Through Query Optimization redrobot5050 Access VBA 3 June 18th, 2004 01:25 AM
Parmeter query probs / confusion socoolbrewster Access 3 March 4th, 2004 11:10 AM
passing input to a query mirage Access 3 October 23rd, 2003 08:43 AM



All times are GMT -4. The time now is 08:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.