VBA ADODB Paramaterized Query Example

This is a sample of a piece of code that executes a SQL statement in VBA and uses an ADODB parameter.

Why go to all the trouble? Google 'SQL Injection Attack' and you'll find out. Using an ADO Parameter object will protect you.

We're frequently called upon to write simple validations behind forms in Dynamics GP, this piece of code is boiler plate for us - cut and paste, edit the SQL, done.

For the record, we're a 'stored procedure only' shop... but for things as small as this the direct SQL make sense. The  query is small, and the customer doesn't have to maintain the stored procedure.                        

Note that there are no quotes around the parameter question mark... that took me a while to get. Using this method, you don't use quotes even for string params.

Along the way we caused a few errors:

Run-time error '3708': Parameter object is improperly defined. Inconsistent or incomplete information was provided.

and

Run-time error '1006': Unsafe Operation. This operation cannot be performed in the target field's AfterUserChange, BeforeLostFocus, or AfterLostFocus Events. This operation could compromise the integrity of the application.

 

 

Related Articles

... and you 'll find more on the VBA Menu

BTW, that last one was caused from the BeforeUserChange event... but we had popped up a message box before the field assignment. Moving the field assignment before the message box fixed the error.

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strItemClass As String
  
Sub openConnection()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub
  
Private Sub Window_AfterOpen()
    openConnection
End Sub
 
Private Sub zUDString20_AfterGotFocus()
 
End Sub
 
Private Sub zUDString20_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    If cn.State = 0 Then
        openConnection
    End If
 
    Dim strPaymentTerm As String
    strPaymentTerm = Me.zUDString20
     
    'get an item
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "select pymtrmid from sy03300 where pymtrmid = ?"
    cmd.Parameters.Append cmd.CreateParameter("@pymtrmid", adVarChar, adParamInput, 21, strPaymentTerm)
    Set rst = cmd.Execute
 
    If rst.EOF Then
        Me.zUDString20 = ""
        MsgBox ("Invalid Payment Term")
        Exit Sub
    Else
        strPaymentTerm = rst("pymtrmid")
        Me.zUDString20 = strPaymentTerm
    End If
 
End Sub
 
 
Private Sub zUDString20_Changed()
 
End Sub

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences