MS Access prepared statements
Is it possible to execute a prepared statement in MS Access on a local table in VBA like this:
UPDATE part SET part_description=? WHERE part_id=?
If so how is it done?
Solution 1:
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:
-
.Value
is the default property for aParameter
, so including it here is not strictly required. On the other hand, it doesn't hurt to be explicit. - 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")