MS Access prepared statements

Question

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?

Sylvestersylvia

Answer 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:

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")

Coveney

"Bang notation" also works for setting the parameter values, e.g., qdf!which_id = 1 – Jolson