ms-access Parameterized Queries QueryDef Parameterized Query Approach

Example

This approach will prevent a user from embedding a second SQL statement in their input for execution.

Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

strSQL = "PARAMETERS [FirstName] Text(255), [LastName] Text(255), [Phone] Text(255); " _
         & "INSERT INTO Employees (chrFirstName, chrLastName, chrPhone) " _
         & "VALUES ([FirstName], [LastName], [Phone]);"

Set db = CurrentDb

Set qdf = db.CreateQueryDef("", strSQL)
qdf.Parameters("FirstName") = Me!txtFirstName
qdf.Parameters("LastName") = Me!txtLastName
qdf.Parameters("Phone") = Me!txtPhone
qdf.Execute

Me!txtFirstName = vbNullString
Me!txtLastName = vbNullString
Me!txtPhone = vbNullString

qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing

Valid Parameter Types:

• DATETIME: for dates (parameter expects VBA Date)
• SHORT,LONG: For integers (SHORT expects Integer, LONG expects Long)
• SINGLE,DOUBLE : For floating point (expect Single and Double respectively)
• VARCHAR or TEXT: For strings
• MEMO or LONGTEXT: For strings longer than 255 characters