Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52

    Question Unanswered: Stored SQL statement

    Hi, I need a way to execute a query I have. It is called qryGetCustomers

    What it basically does is:

    SELECT LastName FROM CUSTOMERS

    I need a way in VBA to execute the query qryGetCustomers and put it in a variable varCustomers(100)

    This is what I thought you would need to do:

    Dim cnnConnection as ADODB.Connection
    Dim rstRecordset as ADODB.Recordset
    dim cnnCommand as ADODB.Command

    set cnnConnection = new adodb.connection
    set rstrecordset = new adodb.recordset
    set cnnCommand as adodb.command

    cnnconnection = currentproject.connection

    Set cnnCommand.ActiveConnection = cnnConnection
    cnnCommand.CommandText = "qryGetCustomers"
    cnnCommand.CommandType = adCmdStoredProc

    Set rstRecordset = cnnCommand.Execute

    'get the first record
    dim strFirstLName as string

    strFirstLName=rstrecordset.fields(0)

    The problem is when I run the SQL statement I get the customers but doing it this way I dont get anything. Please help me someone

    Thanking you all in advance

    - Mark

  2. #2
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52

    Question

    Hi people, here is the code:

    On Error Resume Next

    Dim dbsDatabase As DAO.Database
    Dim rstRecordset As DAO.Recordset
    Dim strQueryName As String

    strQueryName = "qryLoanReview"

    'Set rstRecordset
    Set dbsDatabase = CurrentDb
    Set rstRecordset = dbsDatabase.OpenRecordset(strQueryName, StartDate.Value, EndDate.Value)
    Dim strData As String

    Do While Not rstRecordset.EOF
    strData = rstRecordset!ID
    rstRecordset.MoveLast
    Loop

    rstRecordset.Close
    dbsDatabase.Close

    That is the code inside VBA. I get an error message on the set rstrecordset line stating error code 3061 Too few parameters, expected 2

    This is the long sql statement I am executing:

    SELECT tblClients.ID, tblClients.[Applicant 1 Last Name] AS LastName, tblClients.[Applicant 1 First Name] AS FirstName, tblLoans.LoanReviewDate, tblLoans.FixedRateExpiry
    FROM tblClients INNER JOIN tblLoans ON tblClients.ID = tblLoans.ClientID
    WHERE (((tblLoans.LoanReviewDate) Between [Forms]![frmReviewDateRange]![StartDate] And [Forms]![frmReviewDateRange]![EndDate])) OR (((tblLoans.FixedRateExpiry) Between [Forms]![frmReviewDateRange]![StartDate] And [Forms]![frmReviewDateRange]![EndDate]));

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Which is it you wish to resolve, the ADO or the DAO?

    For the DAO (last version), you'll need to resolve the form parameters, possibly something like this:

    ' additional declarations
    dim qdf as dao.querydef
    dim prm as dao.parameter

    set qdf=dbsDatabase.querydefs(strQueryName)
    for each prm in qdf.parameters
    prm.value=eval(prm.name)
    next prm
    set rstRecordset = qdf.openrecordset()

    Then - you'r loop is never going to finish, since it uses .movelast. Should you rather be trying .movenext?
    Roy-Vidar

  4. #4
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52
    Hi, what is qdf.parameters. I understand that its to do with the parametrs passed to the SQL statement but do I have to tell access this since they are linked to the form?

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    You are aware of the parameters, the query "is aware" of the parameters, but DAO isn't, so this makes DAO/the Jet engine aware of the parameters too, so the answer is yes. When opening a stored query with parameters from forms, there's a need to resolve the parameters. Did you try?
    Roy-Vidar

  6. #6
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52

    Talking

    ur a legend dude, thank you so much. I really like this forum. I owe u big time thank u

Posting Permissions

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