VBA/Excel/Access/Word/Access/SQL Parameter

Build the SQL statement dynamically

   <source lang="vb">

Sub MyFirstConnection2()

   Dim myConnection As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strSQL As String
   Dim strSearch As String
   
   strSearch = "Lee"
   
   strSQL = "SELECT FirstName, LastName FROM Employees" & _
             " WHERE LastName = " & " "" & strSearch & """
   
   Set myConnection = CurrentProject.Connection
   
   Set myRecordset = New ADODB.Recordset
   myRecordset.Open strSQL, myConnection
   
   Do Until myRecordset.EOF
      Debug.Print myRecordset.Fields("FirstName"), _
                  myRecordset.Fields("LastName")
      myRecordset.MoveNext
   Loop
   myRecordset.Close
   myConnection.Close
   Set myConnection = Nothing
   Set myRecordset = Nothing

End Sub

</source>
   
  

Creat a SQL statement and append parameter as ceriteria

   <source lang="vb">

Sub FindByPrice2(curPrice As Currency)

   Dim db As Database
   Dim rec As Recordset
   Dim strSQL As String
   Dim strMatches As String
   Dim intCounter As Integer
   strSQL = "SELECT * FROM tblSales WHERE AmountPaid = " & curPrice
   Set db = CurrentDb()
   Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
   Do Until rec.EOF
       Debug.Print rec!SalesID
       rec.MoveNext
   Loop
   intCounter = rec.RecordCount
   Debug.Print FormatCurrency(curPrice)
   rec.Close

End Sub

</source>
   
  

Creating a Parameter Query

   <source lang="vb">

Sub Create_ParameterQuery()

  Dim cat As ADOX.Catalog
  Dim cmd As ADODB.rumand
  Dim strPath As String
  Dim strSQL As String
  Dim strQryName As String
  On Error GoTo ErrorHandler
  strPath = CurrentProject.Path & "\mydb.mdb"
  strSQL = "Parameters [Type Country Name] Text;" & _
     "SELECT Customers.* FROM Customers WHERE " _
     & "Customers.Country=[Type Country Name];"
  strQryName = "Customers by Country"
  Set cat = New ADOX.Catalog
  cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
  Set cmd = New ADODB.rumand
  cmd.rumandText = strSQL
  cat.Procedures.Append strQryName, cmd
  Set cmd = Nothing
  Set cat = Nothing
  Exit Sub

ErrorHandler:

  If InStr(Err.Description, "already exists") Then
     cat.Procedures.Delete strQryName
     Resume
  Else
     MsgBox Err.Number & ": " & Err.Description
  End If

End Sub

</source>
   
  

Executing a SQL Statement Containing Parameters

   <source lang="vb">

Public Sub UpdateWithSQL()

       Dim cmd As New ADODB.rumand
       Dim conn As ADODB.Connection
       Dim prm As ADODB.Parameter
       Dim strConn As String
       Dim strSQL As String
       strConn = "Provider=SQLOLEDB.1;" & _
           "Data Source=(local); Initial Catalog=NorthWind;" & _
           "Integrated Security=SSPI"
       Set conn = New ADODB.Connection
       conn.Open strConn
       Set cmd = New ADODB.rumand
       cmd.rumandText = "UPDATE Products " & _
           "SET OrderDate = OrderDate, " & _
           "ShipVia = ShipVia, " & _
           "Freight = Freight " & _
           "WHERE OrderID = OrderID"
   cmd.rumandType = adCmdText
   cmd.ActiveConnection = conn
   Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput)
   cmd.Parameters.Append prm
   cmd.Parameters("OrderID").Value = 1
   Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput)
   cmd.Parameters.Append prm
   cmd.Parameters("OrderDate").Value = "10/10/2007"
   Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput)
   cmd.Parameters.Append prm
   cmd.Parameters("ShipVia").Value = 2
   Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput)
   cmd.Parameters.Append prm
   cmd.Parameters("Freight").Value = "1.5"
   cmd.Execute
   conn.Close

End Sub

</source>
   
  

Running Parameter Queries

   <source lang="vb">

Sub RunParameterQuery(datStart As Date, datEnd As Date)

   Dim cmd As ADODB.rumand
   Dim rst As ADODB.Recordset
   Set cmd = New ADODB.rumand
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.rumandText = "Select * from Employees " & _
       "Where BirthDate Between ? and ?"
   cmd.rumandType = adCmdText
   Set rst = cmd.Execute(Parameters:=Array(datStart, datEnd))
   Do Until rst.EOF
       Debug.Print rst("EmployeeID"), rst("BirthDate")
       rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
   Set cmd = Nothing

End Sub

</source>
   
  

User InputBox to read SQL statement parameter

   <source lang="vb">

Sub command_parameters()

 Dim conn As New Connection
 Dim rec As New Recordset
 Dim comm As New Command
 Dim ws As Worksheet
 Dim i&, countryname$
 Set ws = ThisWorkbook.Worksheets("command")
 conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
   "Data Source=" + ThisWorkbook.Path + "\nwind.mdb;"
 Set comm.ActiveConnection = conn
 comm.rumandText = "SELECT companyname FROM customers WHERE country = ?"
 countryname = InputBox("Please type in a country name (i.e. "germany").")
 comm.Parameters(0) = countryname
 rec.Open comm
 ws.[a1].CopyFromRecordset rec
 rec.Close: conn.Close

End Sub

</source>