Access VBA version 2007 DoCmd.SetParameter?

Question

I inherited an access database that has many tables, forms, and queries. However, I am a PHP programmer, and VBA is very foreign to me. I was asked to make some changes that within a few days I was able to get work (with a lot of help from old random SO posts).

After transferring the database to users, the code that works on my computer does not work on them. It seems I have Access 2010 and they have 2007. As far as I can tell, the DoCmd.SetParameter function does not exist in VBA in Access 2007.

Here is the code snippet:

DoCmd.SetParameter "ReportYear", Year.Value
DoCmd.SetParameter "ReportMonth", Month.Value
DoCmd.OpenQuery "doFillData"

doFillData is a query inside Access, which is automatically added to another table, before starting it requires 2 parameters (year and month).

The obvious answer is to upgrade them to 2010, but I don't have that much power. I suppose I can create conditional code to do something different in 2007, but I cannot find a similar function to use. Does anyone have any idea?

Answer 1

Instead of using it, DoCmd.OpenQueryyou want to manipulate the querydef object with parameters, and then execute it. You can use Execute options, such as acFailOnErrorwhen executing this method (not available in OpenQuery), and you can determine the number of records affected.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("doFillData")
qdf.Parameter("ReportYear") = Year.Value
qdf.Parameter("ReportMonth") = Month.Value
qdf.Execute 

MsgBox qdf.RecordsAffected & " records were affected."