Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Kendrick, Idaho, USA
    Thanked 0 Times in 0 Posts

    Action query parameters (2000)

    I need to run a stored action query from code using something like the OpenQuery method. The query has parameters. I can't find a way to set those parameters at runtime without building a form and using fields on the form. Surely there is an easier way.
    I know I can include the entire SQL statement including a WHERE clause, but it would be huge and I would rather not.
    Using a QueryDef to open a recordset allows parameters, but does not seem to work except with SELECT queries.
    Thanks for your thoughts.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: Action query parameters (2000)

    There's more than one way to do this. One way is to use the ADO Command and Parameter objects. Here is simple example, using Northwind Customers table. I made blank copy of table named Customers1. SQL for an append query where Country is specified as parameter when query runs:

    PARAMETERS [CountryName] Text ( 255 );
    INSERT INTO Customers1 ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax )
    SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, Customers.Phone, Customers.Fax
    FROM Customers
    WHERE (((Customers.Country)=[CountryName]));

    Sample code:

    Public Sub TestADOParameterActionQry()
    On Error GoTo Err_Handler

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim strCmd As String
    Dim strCountryName As String
    Dim lngRecs As Long
    Dim strMsg As String

    Set cnn = CurrentProject.Connection
    Set cmd = New ADODB.Command

    strCmd = "qryTestADOParameterActionQry"
    cmd.CommandText = strCmd
    Set prm = cmd.CreateParameter("<!t>[CountryName]<!/t>", adBSTR, adParamInput)
    cmd.Parameters.Append prm
    strCountryName = "UK"
    prm.value = strCountryName

    Set cmd.ActiveConnection = cnn
    cmd.Execute lngRecs

    strMsg = lngRecs & " records appended."
    MsgBox strMsg, vbInformation, "ACTION QUERY RESULTS"

    Set cnn = Nothing
    Set cmd = Nothing
    Set prm = Nothing
    Exit Sub
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "TestADOParameterActionQry Error Msg"
    Resume Exit_Sub
    End Sub

    "qryTestADOParameterActionQry" is name of the query whose SQL is shown above. When code runs, any records in Customers where Country = strCountryName variable are appended to table. If you run sub twice, w/o changing value of parameter, an "Invalid SQL statement" error will result because you tried to append records with duplicate primary keys. If using this method, be sure to add applicable error handling. For more detail on using ADO Command & Parameter objects, refer to ADO Help files.


  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Action query parameters (2000)

    If you're working with DAO, you can handle executing a parameter query in code like this:

    <pre>Public Function RunParamQuery(ByVal strQryNm As String, _
    Optional intParam As Integer, _
    Optional varParamValue) As Boolean
    'Created by Charlotte Foust
    ' modified 6/17/98, 4/4/99, 12/2/99, 1/4/2001
    On Error GoTo Proc_err
    Dim dbs As DAO.Database
    Dim wsp As DAO.Workspace
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.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

    blnOK = True
    Set wsp = DBEngine(0)
    Set dbs = wsp.Databases(0)
    Set qdf = dbs.QueryDefs(strQryNm)
    intNumParams = qdf.Parameters.Count

    'reduce the intParam for zero-based collection
    intParam = intParam - 1

    ' See how many parameters there are to pass
    If Not IsMissing(varParamValue) Then
    If intParam <> -1 Then
    ' If user passed a Parameter value
    ' and the parameter it applies to
    ' make sure it gets special handling
    blnPrmVal = False
    blnPrmVal = True
    blnOK = False
    End If
    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
    If intParam <> -1 Then
    If intLoop = intParam Then
    Set prm = qdf.Parameters(intParam)
    prm = varParamValue
    blnPrmSet = True
    End If
    End If

    If Not blnPrmSet Then
    varParamValue = InputBox(strName, "Enter value")
    Select Case varPrmType
    Case dbLong
    varParamValue = CLng(varParamValue)
    Case dbInteger
    varParamValue = CInt(varParamValue)
    Case dbDouble
    varParamValue = CDbl(varParamValue)
    Case dbDate
    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
    qdf.Execute dbFailOnError
    'Debug.Print qdf.RecordsAffected
    Case Else
    End Select
    End If

    On Error Resume Next
    Set qdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    RunParamQuery = blnOK
    Exit Function
    MsgBox "RunParamQuery error #" & Err & "--" & Err.Description
    blnOK = False
    Resume Proc_exit
    End Function</pre>


Posting Permissions

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