Parameterized Action queries from VBA (A 97)

Topic

New Reply

I have reviewed some old posts about parameter queries, and they have been helpful but have not directly addressed my
question. I am trying to run an append query from VBA. The idea is to select a nember of values from a combobox
control and pass them as parameters to an append query that will append the appropriate records to a table.
At present I am doing this by running a “DoCmd.RunSQL” command and manipulating the SQL string – I think
the paramquery should be faster (but I won’t know until I get it working ).

Extracting the values from the combobox is working in the SQL/text manipulation version.

The code in the class module looks like this:

Private Sub Command33_Click()
Dim dbs As Database
Dim rst As Recordset
Dim qdf As DAO.QueryDef
Dim Sel As Integer
Dim ctlList As Control
Dim ctlActive As Control
Dim Item As Variant
Dim stDocName As String

Set dbs = CurrentDb
Set qdf = dbs.querydefs("qryPropEvalandStatus2MkTable")
 this is the parameter query - it is looking for an integer parameter "Selected" 
Set ctlList = Forms!frmPrintReports!lstbxRFPSelectList
For Each Item In ctlList.ItemsSelected
    Sel = ctlList.ItemData(Item)
    qdf("[Selected]") = Sel
 I am trying to pass the 'Sel' value taken from the listbox to the query.
It "seems" to be working - when the code chokes in the next line "Sel" and
qdf("[Selected]") both have a value representative of the first value
selected in the listbox.  I could cut out the "Sel" variable, but that snippet
is just taken out of the text manipulating version
that works - minor touchups to come later!
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
 At this point I get an illegal operation warning - THUMP  
Next Item
rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing
Set qdf = Nothing
End Sub

So – is it even possible to run an action query using the OpenRecordset
method – and if so, with what settings?
If not, is it possible to run an action query from within VBA, other than by the
CoCmd.RunSQL approach – and if so, how?

Reply | Quote

Replies

You are correct in your assumption that a stored (and compiled) query with a parameter will be faster than using a SQL string that is modified and must be compiled each time. The trick in doing this is to use the Parameters collection associated with a QueryDef. Unfortunately, I am running XP and 2000 and no longer have 97 installed, and the help in this area is woeful. But you should be able to use the 97 help to get details on how to do that with DAO. Hope this give you some insights.

Reply | Quote

You need to dim a Parameter object and loop through the parameters collection of the querydef passing values as appropriate. Here’s a routine I found in my library that might serve as an example. It is populating the parameters either from a passed paramarray or using inputboxes, but most of it should be adaptable to your uses.

Public Function RunParamQuery(ByVal strQryNm As String, _
                      ParamArray varParamValues()) As Boolean
  'Created by Charlotte Foust
  '12/9/99
  On Error GoTo Proc_err
  Dim dbs As Database
  Dim wsp As Workspace
  Dim qdf As QueryDef
  Dim prm As Parameter
  Dim blnOK As Boolean
  Dim varPrmType As Variant
  Dim strName As String
  Dim blnPrmVal As Boolean
  Dim blnPrmSet As Boolean
  Dim intLoop As Integer
  Dim intNumParams As Integer
  Dim intQDFType As Integer
  Dim varParamValue As Variant
  
  Const DUPLICATE_KEY_OR_INDEX = 3022
  
  blnOK = True
  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs(strQryNm)
  intNumParams = qdf.Parameters.Count
  ' See how many parameters there are to pass
  If intNumParams = UBound(varParamValues) + 1 Then
      blnPrmVal = True
  Else
    blnOK = False
  End If
  If Not blnPrmVal Then
   ' Loop through the parameters and
   ' prompt user for values except for
   ' any passed in the call
    If intNumParams = 0 Then
      intNumParams = 1
    End If
    For intLoop = 0 To intNumParams - 1
      blnPrmSet = False
      Set prm = qdf.Parameters(intLoop)
      strName = prm.Name
      varPrmType = prm.Type
      On Error Resume Next
      prm = varParamValues(intLoop)
      If Err = 0 Then
        blnPrmSet = True
      End If
      On Error GoTo Proc_err
      If Not blnPrmSet Then
          varParamValue = InputBox(strName, "Enter value")
          Select Case varPrmType
            Case vbLong
              varParamValue = CLng(varParamValue)
            Case vbInteger
              varParamValue = CInt(varParamValue)
            Case vbDouble
              varParamValue = CDbl(varParamValue)
            Case vbDate
              varParamValue = CDate(varParamValue)
            Case Else
              varParamValue = varParamValue
          End Select
        prm = varParamValue
      End If
    Next intLoop
  End If

  If blnOK Then
   '<>
   intQDFType = qdf.Type
    Select Case intQDFType
      Case dbQMakeTable, dbQAppend, dbQDelete
        Set wsp = DBEngine(0)
        If intQDFType = dbQMakeTable Then
        ElseIf intQDFType = dbQAppend Then
        ElseIf intQDFType = dbQDelete Then
        End If
        wsp.BeginTrans
          qdf.Execute dbFailOnError
        wsp.CommitTrans
    Debug.Print qdf.RecordsAffected
      Case Else
        If intQDFType = dbQSelect Then
        ElseIf intQDFType = dbQCrosstab Then
        ElseIf intQDFType = dbQSetOperation Then
        ElseIf intQDFType = dbQCompound Then
        End If
        DoCmd.OpenQuery strQryNm
    End Select
  End If

Proc_exit:
  On Error Resume Next
  Set qdf = Nothing
  Set dbs = Nothing
  Set wsp = Nothing
  RunParamQuery = blnOK
  Exit Function
Proc_err:
  Select Case Err.Number
    Case DUPLICATE_KEY_OR_INDEX
      'Ignore this one
    Case Else
      MsgBox "RunParamQuery error #" & Err & "--" & Err.Description
      blnOK = False
  End Select
  Resume Proc_exit
End Function 

Reply | Quote

You asked for a snippet of code in a private email, but I think in looking at your code that your only problem is with
qdf(“[Selected]”) = Sel
I believe it should read
qdf.Parameters(“Selected”)=Sel
Or you could simplify it to
qdf.Parameters(“Selected”)=ctrlList.ItemData(Item)
This of course presumes that you have a stored query with a criteria that says [Selected]. Charlotte’s code example is more elegant, but yours appears to be functional once the above line is corrected.

Reply | Quote