Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unanswered: Pass parameter to query using ADO

    Unless I am doing this all wrong -

    I appear to be missing the point on this one- quite happy using DAO - am now upsizing to SQL server - using a Project as a front end, and ADO.

    Currently- usng DAO, I run a report based on a query (select * from customer) - sending a parameter to the query using querydefs in VBA. This 'modifies' the query to read "select * from customers where code = ABC001"

    Am really struggling to understand how to send the parameters and thus modify the query (Stored Procedure) using ADO.

    Any pointers in the right direction would be greatly received.

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    There are two approaches you can try.

    1. open an adodb recordset and pass the parameters to it.
    2. open an adodbrecordset with the parameter in the source.

    1 is doable but i don't use it very often (more coding)
    here is an example for method 2

    Dim cn as new adodb.connection
    Dim rs as new adodb.recordset

    Set cn = CurrentProject.Connection
    rs.open "SELECT * FROM tblCustomer WHERE CustomerID = " & l_CustomerID", cn, adOpenDynamic, adLockPessimistic

    If Not rs.Eof Then
    MsgBox "It Works"
    EndIf

    rs.close
    cn.close
    set rs = nothing
    set cn = nothing

  3. #3
    Join Date
    Jul 2003
    Posts
    4

    Many thanks

    for your reply - appreciate it.

    As the reason I wish to pass a parameter to the query is so that I can run a report - I was under the impression that I could not use a recordset as a data source for a report.

    My workaround at the moment is to pass the parameter to the stored proc - and use it to append to a table - which I can then use as a source for the report.

    Once all processing has finished for this parameter (customer) - delete the contents of the table, and then repopulate with the next parameter.

    Would be grateful for your comments - I am running through the customers table, and one at a time sending their data to the report - move next - run the report again.

    Many thanks

  4. #4
    Join Date
    Mar 2002
    Posts
    192
    Assuming the report you wish to generate is an access report and not a crystal report then the solution is even simpler.


    1. Link the table from sql server to access.
    2. build your query based on this table
    3. base your report on this query

    the following lines of sample code will open the report with criteria.

    Dim l_MyValue As String

    ' You'll need to write some code to set the value

    DoCmd.OpenReport "", acViewNormal, , "MyfieldFromMyQuery = '" & l_MyCriteriaValue & "'"

  5. #5
    Join Date
    Jul 2003
    Posts
    4

    Thanks again for your reply

    it showed me the syntax for using the 'where' part of the docmd.open report.

    Currently connecting the data via Access Project ( now 6-7 millions records on average - will increase)

    Needed to run a filter on the report - on a customer by customer basis.
    I then use a recordset to get my first customer - run the report using 'where' and then move on to the next reord(customer) in my script.

    Am new to SQL Server - having worked out the Access DAO for all of this last month (500,000 records) - now get hit with large increase in number of records - so have had to use SQL.

    Presume my use of the filter is the correct way - from a performance point of view - to process this. In that I am only sending small instruction down pipe to server - server then processes request and returns only records that match.

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •