VBA, ADO.Connection and query parameters

by

You need to use an ADODB.Command object that you can add parameters to. Here’s basically what that looks like

Sub adotest()

    Dim Cn As ADODB.Connection
    Dim Cm As ADODB.Command
    Dim Pm As ADODB.Parameter
    Dim Rs as ADODB.Recordset

    Set Cn = New ADODB.Connection
    Cn.Open "mystring"
    Set Cm = New ADODB.Command
    With Cm
        .ActiveConnection = Cn
        .CommandText = "SELECT * FROM table WHERE parentid=?;"
        .CommandType = adCmdText

        Set Pm = .CreateParameter("parentid", adNumeric, adParamInput)
        Pm.Value = 1

        .Parameters.Append Pm

        Set Rs = .Execute
    End With

End Sub

The question mark in the CommandText is the placeholder for the parameter. I believe, but I’m not positive, that the order you Append parameters must match the order of the questions marks (when you have more than one). Don’t be fooled that the parameter is named “parentid” because I don’t think ADO cares about the name other than for identification.

More Related Contents:

  1. Exporting MS Access Forms and Class / Modules Recursively to text files?
  2. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text
  3. Why Use Integer Instead of Long?
  4. Loop through files in a folder using VBA?
  5. Force a screen update in Excel VBA
  6. Find all matches in workbook using Excel VBA
  7. Create a new sheet for each unique agent and move all data to each sheet
  8. Set Auto Filtering multiple wildcards
  9. Pass arguments to Constructor in VBA
  10. How to make Excel VBA variables available to multiple macros?
  11. VBA collection: list of keys
  12. Determining whether an object is a member of a collection in VBA
  13. Is there a faster CountIF
  14. Excel VBA How to detect if something was pasted in a Worksheet
  15. Getting error Procedure too large in VBA Macros (Excel)
  16. VBA – Folder Picker – set where to start [duplicate]
  17. Difference in type between using and not using Set keyword
  18. How to do late binding in VBA?
  19. Environ Function code samples for VBA
  20. What is the best way to access a serial port from VBA?
  21. Continue For loop
  22. mac excel vba loop : from list & then export as pdf
  23. Can Advanced Filter criteria be in the VBA rather than a range?
  24. How to add an embedded image to an HTML message in Outlook 2010
  25. Speed up multiple replacement
  26. In Excel VBA, what is the way to check if the web page is fully loaded?
  27. How to get the procedure or function name at runtime?
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment