Visual Basic Programming Code Examples

Visual Basic > Database SQL Stuff Code Examples

Calling DAO parameterized queries using ADO



Calling DAO parameterized queries using ADO

The following routine calls DAO parameterized queries from ADO without
using ADOX. A demonstration routine can be found at the bottom of this post:

'Purpose   :    Run a parameterised DAO query using ADO
'Inputs    :    cCon            ADO Database connection
'               sQueryName      Name of the query
'               avResults       Array of results returned from the query
'               avParameters    Array of parameters to pass into the query.
'                               The array should be a zero based 1d variant array
'Outputs   :    Returns True if the routines fails to run the query
'Notes     :    
'Revisions :

Function ExecuteParamQuery(cCon As ADODB.Connection, sQueryName As String, avResults As Variant, ParamArray avParameters() As Variant) As Boolean
    Dim Cmd As ADODB.Command, rsResults As ADODB.Recordset
    
    On Error GoTo ErrFailed
    Set Cmd = New ADODB.Command
    'Create the command
    Set Cmd.ActiveConnection = cCon
    Cmd.CommandText = sQueryName
    'Execute the Command, passing in the parameter values
    Set rsResults = Cmd.Execute(, avParameters, adCmdStoredProc)
    If rsResults.EOF = False Then
        'Recordset contains values
        avResults = rsResults.GetRows
        rsResults.Close
        Set rsResults = Nothing
    Else
        'Empty the result array
        avResults = Nothing
    End If
    Set Cmd = Nothing
    
    Exit Function
    
ErrFailed:
    'Failed to run query
    ExecuteParamQuery = True
End Function


'Demonstration routine:
Private Sub Command1_Click()
    Dim cCon As New adodb.Connection, avResults As Variant, vThisItem As Variant

    'Assumes there is a database called "Test.mdb" in the application directory
    cCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test.mdb;Persist Security Info=False"
    'See below for "qryGetSurname" SQL 
    ExecuteParamQuery cCon, "qryGetSurname", avResults, "red"
    'Print results
    For Each vThisItem In avResults
        Debug.Print CStr(vThisItem)
    Next
End Sub

'SQL for qryGetSurname:
'SELECT tblNames.Surname, tblNames.FirstName
'From tblNames
'WHERE (((tblNames.FirstName)=[iFirstName]));