"The code is exactly
how I would like to write code and the algorithms used are very
efficient and well-documented."
Van T. Dinh, Microsoft MVP
|
|
Create, execute, and browse Microsoft Access queries using DAO/Jet in VBA and VB6.
Procedure List
Procedure Name
|
Type
|
Description
|
(Declarations)
|
Declarations
|
Declarations and private variables for the modQueriesDAO module
|
CreateNewQuery
|
Procedure
|
Saves a SQL string as a new query in the database.
|
ExecuteQueryDAO
|
Procedure
|
Run a saved action query.
|
ExecuteQueryParameterDAO
|
Procedure
|
Run a query that requires a parameter (the query must be an action query).
|
ExecuteQueryParametersDAO
|
Procedure
|
Run a query that requires multiple parameters (the query must be an action query).
|
ExecuteSQLDAO
|
Procedure
|
Runs a SQL string on any database (the SQL string must be an non-row-returning action query).
|
OpenRecordsetQueryDAO
|
Procedure
|
Open a DAO recordset on a query that retrieves records (can't be an action query).
|
OpenRecordsetParameterDAO
|
Procedure
|
Open a DAO recordset on a query that requires a parameter and retrieves records (can't be an action query).
|
OpenRecordsetParametersDAO
|
Procedure
|
Open a DAO recordset on a query that uses multiple parameters and retrieves records (can't be an action query).
|
Example Code for Using Module: QueriesDAO
' Example of modQueriesDAO
'
' To try this example, do the following:
' 1. Create a new user form in your project.
' 2. Create a command button 'cmdTest'
' 3. Paste this code into the form's module
Private Const mcstrSample As String = "C:\Total Visual SourceBook 2013\Samples\Sample.mdb"
Private Sub cmdTest_Click()
Dim dbsSample As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim fOK As Boolean
Dim lngAffected As Long
Dim astrParamNames(0 To 1) As String
Dim avarParamVals(0 To 1) As Variant
Dim intI As Integer
On Error GoTo PROC_ERR
Set dbsSample = DAO.OpenDatabase(mcstrSample)
' Example of ExecuteSQLDAO
strSQL = "SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle, Customers.Address, " & _
"Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, Customers.Phone, Customers.Fax " & _
"INTO FMS_TEST FROM Customers; "
ExecuteSQLDAO dbsSample, strSQL
' Example of CreateNewQuery
strSQL = "DELETE * FROM FMS_TEST WHERE country = 'SWEDEN'"
fOK = CreateNewQuery(dbsSample, "qryFMS1", strSQL)
Debug.Print "qryFMS1 created successfully " & fOK
strSQL = "DELETE * FROM FMS_TEST WHERE country = [prmCountry]"
fOK = CreateNewQuery(dbsSample, "qryFMS2", strSQL)
Debug.Print "qryFMS2 created successfully " & fOK
strSQL = "DELETE * FROM FMS_TEST WHERE country = [prmCountry1] OR country = [prmCountry2]"
fOK = CreateNewQuery(dbsSample, "qryFMS3", strSQL)
Debug.Print "qryFMS3 created successfully " & fOK
' Example of ExecuteQueryDAO
Debug.Print "Executing qryFMS1 (testing ExecuteQueryDAO)..."
lngAffected = ExecuteQueryDAO(dbsSample, "qryFMS1")
Debug.Print "Rows affected: " & lngAffected
' Example of ExecuteQueryParameterDAO
Debug.Print "Executing qryFMS2 (testing ExecuteQueryParameterDAO)..."
lngAffected = ExecuteQueryParameterDAO(dbsSample, "qryFMS2", "prmCountry", "Germany")
Debug.Print "Rows affected: " & lngAffected
' Example of ExecuteQueryParametersDAO
astrParamNames(0) = "prmCountry1"
avarParamVals(0) = "UK"
astrParamNames(1) = "prmCountry2"
avarParamVals(1) = "Spain"
Debug.Print "Executing qryFMS3 (testing ExecuteQueryParametersDAO)..."
lngAffected = ExecuteQueryParametersDAO(dbsSample, "qryFMS3", astrParamNames, avarParamVals)
Debug.Print "Rows affected: " & lngAffected
' Example of OpenRecordsetQueryDAO
strSQL = "SELECT Customers.CustomerID, Customers.CompanyName FROM Customers"
If CreateNewQuery(dbsSample, "qryFMSSelect1", strSQL) Then
If OpenRecordsetQueryDAO(dbsSample, "qryFMSSelect1", rst) Then
Debug.Print "OpenRecordsetQueryDAO: First 2 Results for Recordset:"
For intI = 1 To 2
Debug.Print " " & intI & ". " & rst![CustomerID] & " - " & rst![CompanyName]
rst.MoveNext
Next intI
End If
End If
' Example of OpenRecordsetParameterDAO
strSQL = strSQL & " WHERE Customers.Country=[prmCountry]"
If CreateNewQuery(dbsSample, "qryFMSSelect2", strSQL) Then
If OpenRecordsetParameterDAO(dbsSample, "qryFMSSelect2", "prmCountry", "USA", rst) Then
Debug.Print "OpenRecordsetParameterDAO: First Result for Recordset:"
Debug.Print " 1. " & rst![CustomerID] & " - " & rst![CompanyName]
End If
End If
' Example of OpenRecordsetParametersDAO
strSQL = strSQL & " AND Customers.Region = [prmState]"
astrParamNames(0) = "prmCountry"
avarParamVals(0) = "USA"
astrParamNames(1) = "prmState"
avarParamVals(1) = "WA"
If CreateNewQuery(dbsSample, "qryFMSSelect3", strSQL) Then
If OpenRecordsetParametersDAO(dbsSample, "qryFMSSelect3", astrParamNames, avarParamVals, rst) Then
Debug.Print "OpenRecordsetParametersDAO: First Result for Recordset:"
Debug.Print " 1. " & rst![CustomerID] & " - " & rst![CompanyName]
End If
End If
PROC_EXIT:
' Clean up
On Error Resume Next
With dbsSample
.Execute "DROP table FMS_TEST"
.QueryDefs.Delete "qryFMS1"
.QueryDefs.Delete "qryFMS2"
.QueryDefs.Delete "qryFMS3"
.QueryDefs.Delete "qryFMSSelect1"
.QueryDefs.Delete "qryFMSSelect2"
.QueryDefs.Delete "qryFMSSelect3"
End With
Exit Sub
PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume PROC_EXIT
End Sub
Overview of Total Visual SourceBook
The source code in Total Visual
SourceBook includes modules and classes for Microsoft Access, Visual
Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily
add this professionally written, tested, and documented royalty-free code
into your applications to simplify your application development efforts.
Additional Resources
|
|