MS Access prepared statements

by 
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Set db = CurrentDb
strSql = "UPDATE Month_Totals Set item_date = [which_date]" & _
    " WHERE id = [which_id];"
Debug.Print strSql
Set qdf = db.CreateQueryDef(vbNullString, strSql)
With qdf
    .Parameters("which_date").Value = Date()
    .Parameters("which_id").Value = 1
    .Execute dbFailOnError
End With

That example used a new, unsaved QueryDef. If you have a saved parameter query, you can use it instead by substituting this line for the CreateQueryDef line:

Set qdf = db.QueryDefs("YourQueryName")

Either way, you can then refer to individual parameters by their names as I did, or by their positions in the SQL statement … so this will work same as above:

.Parameters(0).Value = Date()
.Parameters(1).Value = 1

Additional notes:

  1. .Value is the default property for a Parameter, so including it here is not strictly required. On the other hand, it doesn’t hurt to be explicit.
  2. As Gord noted below, you can use “Bang notation” with the parameter’s name like !which_id, which is more concise than .Parameters("which_id")

More Related Contents:

  1. How does one decompile and recompile a database application?
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. Code to loop through all records in MS Access
  5. Bang Notation and Dot Notation in VBA and MS-Access
  6. How do I force VBA/Access to require variables to be defined?
  7. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  8. How to diagnose MS access crashes
  9. Equivalent cURL in VBA?
  10. How do I access the selected rows in Access?
  11. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  12. Prevent enabling of shift bypass – Access 2010
  13. VBA procedure to import csv file into access
  14. Transform multiple rows into columns with unique key
  15. how to show query results in a datasheet in the same form?
  16. Access VBA: Find max number in column and add 1
  17. Create sequential ID value based on the year that a record is added
  18. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  19. Null values for variables in VBA
  20. Using a centralized VBA module in multiple Access databases
  21. How to execute a query in ms-access in VBA code?
  22. How to add a menu item to the default right click context menu
  23. VBA – Run Time Error 3271 using DAO object
  24. Trouble with InputBoxes
  25. Get Name of Current VBA Function
  26. “General error Unable to open registry key Temporary (volatile) …” from Access ODBC
  27. Is there a simple way of populating dropdown in this Access Database schema?
  28. Determining whether an object is a member of a collection in VBA
  29. “Operation must use an updateable query” error in MS Access
  30. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”

Leave a Comment