Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Parameterized Action queries from VBA (A 97)

    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 <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>).

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

    The code in the class module looks like this:
    <pre>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")
    <font color=red> this is the parameter query - it is looking for an integer parameter "Selected" </font color=red>
    Set ctlList = Forms!frmPrintReports!lstbxRFPSelectList
    For Each Item In ctlList.ItemsSelected
    Sel = ctlList.ItemData(Item)
    qdf("[Selected]") = Sel
    <font color=red> 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!</font color=red>
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    <font color=red> At this point I get an illegal operation warning - THUMP</font color=red> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Next Item
    rst.Close
    dbs.Close
    Set dbs = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    End Sub
    </pre>




    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?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parameterized Action queries from VBA (A 97)

    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.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameterized Action queries from VBA (A 97)

    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.

    <pre>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
    '<<any special handling goes here>>
    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 </pre>

    Charlotte

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parameterized Action queries from VBA (A 97)

    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
    <font face="Georgia"> qdf("[Selected]") = Sel</font face=georgia>
    I believe it should read
    <font face="Georgia"> qdf.Parameters("Selected")=Sel</font face=georgia>
    Or you could simplify it to
    <font face="Georgia"> qdf.Parameters("Selected")=ctrlList.ItemData(Item) </font face=georgia>
    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •