Why isn't currentdb.execute working?

[GBalcom]

I've been reading about the benefits of using currentdb.execute vs Open query for executing Action Queries....I really like the idea of the "dbFailOnError" rolling back any changes if the action query has errors....

However, I haven't been able to get it to work in my VBA in two separate projects....Here's my code:

  'Run Query
    CurrentDb.Execute "qrySalesData", dbFailOnError

qrySalesData is the name of the query...When I run this I get the following error:
Error Number3061, Too Few parameters. Expected 2

I do have two parameters being called from the form this is on (Start and End Dates)

Thanks for any insight...

 

[GBalcom]

Ok, found some good documentation on msdn:
http://msdn.microsoft.com/en-us/library/office/ff821728.aspx

Here is my code now which works well:

  'Run Query
    'set parameters
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qrySalesData")
        qdf.Parameters("StartDate").Value = Me.txtStartDate
        qdf.Parameters("EndDate").Value = Me.txtEndDate
        
        qdf.Execute dbFailOnError

Note: I had to make real parameters in my query, not just link them to a form at the bottom of the design view window..

Hopefully this helps someone else.

 

[mdlueck]

Probably you should drop your DB objects thusly. And objects you create with Set you should also clean up in the same context you created them.

  'Run Query
    'set parameters
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qrySalesData")
        qdf.Parameters("StartDate").Value = Me.txtStartDate
        qdf.Parameters("EndDate").Value = Me.txtEndDate
        
        qdf.Execute dbFailOnError

[B]        Set qdf = Nothing
        Set db = Nothing
[/B]