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, but I can't find a similar function to use. Anyone have any ideas?

pteranodon pteranodon · Accepted Answer · 2014-02-03T22:07:56

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?

1 Answers