VB and VBA Users Source Code: Calling DAO parameterized queries using ADO
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Calling DAO parameterized queries using ADO
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Thursday, December 28, 2000
Hits:
840
Category:
Database/SQL/ADO
Article:
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 'Author : Andrewb 'Date : 21/08/2000 '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, "Andrew" '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]));
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
