Access 2007 VBA DoCmd.SetParameter replacement?

Question

I've inherited an Access Database that has a lot of tables, forms, and queries in it. However, I'm a PHP programmer and VBA is pretty foreign to me. I was asked to make some changes, which over the course of a few days I was able to get working (with a lot of help from old random SO posts). After passing the database back to the users, the code that works on my computer is not working on theirs. It seems I have Access 2010 and they have 2007. As best I can tell, the function DoCmd.SetParameter doesn't exist in VBA in Access 2007. Here's a snippet of the code : ``` DoCmd.SetParameter "ReportYear", Year.Value DoCmd.SetParameter "ReportMonth", Month.Value DoCmd.OpenQuery "doFillData" ``` doFillData is a query inside of Access that inserts in to another table automatically, requiring 2 parameters (year and month) before running. The obvious answer is, make them upgrade to 2010, but I don't have that power. I assume I'll be able to create conditional code to do something different in 2007,…

Accepted Answer

Instead of using `DoCmd.OpenQuery`, you want to manipulate the querydef object's named parameters and then execute it. You can use Execute options like `acFailOnError` when executing this way (not available with OpenQuery) and you can detect 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."
```

Access 2007 VBA DoCmd.SetParameter replacement?

Solution

Problem