Skip to content

Instantly share code, notes, and snippets.

Created May 30, 2018 15:10
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
Microsoft Access VBA class to run queries via DAO
MultiUse = -1 'True
Attribute VB_Name = "DAOQueryRunner"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Option Explicit
Private myCurrentDb As DAO.Database
Sub Class_Initialize()
Set myCurrentDb = CurrentDb
End Sub
Sub Class_Terminate()
Set myCurrentDb = Nothing
End Sub
Public Function GetRecordsetFromTable(pTableName As String) As DAO.RecordSet
Set GetRecordsetFromTable = myCurrentDb.OpenRecordset(pTableName, dbOpenTable)
End Function
Public Function GetRecordsetFromSQLQuery(pSqlQuery As String) As DAO.RecordSet
Set GetRecordsetFromSQLQuery = myCurrentDb.OpenRecordset(pSqlQuery)
End Function
Public Function GetSingleValueFromSQLQuery(pSqlQuery As String) As Variant
Dim rs As DAO.RecordSet
Set rs = GetRecordsetFromSQLQuery(pSqlQuery)
GetSingleValueFromSQLQuery = rs.Fields(0).Value
Set rs = Nothing
End Function
Public Function GetRecordsetForNamedQuery(pQueryName As String) As DAO.RecordSet
Dim QueryDef As DAO.QueryDef
Dim RecordSetToReturn As DAO.RecordSet
Set QueryDef = myCurrentDb.QueryDefs(pQueryName)
Set RecordSetToReturn = QueryDef.OpenRecordset()
Set GetRecordsetForQuery = RecordSetToReturn
End Function
Public Function GetRecordsetForNamedQueryWithParametersDictionary(pQueryName As String, pDictionary As Scripting.Dictionary) As DAO.RecordSet
Dim QueryDef As DAO.QueryDef
Dim RecordSetToReturn As DAO.RecordSet
Dim DictionaryKey As Variant
Set QueryDef = myCurrentDb.QueryDefs(pQueryName)
For Each DictionaryKey In pDictionary.Keys()
QueryDef.Parameters(DictionaryKey) = pDictionary(DictionaryKey)
Next DictionaryKey
Set RecordSetToReturn = QueryDef.OpenRecordset()
Set GetRecordsetForQueryWithParametersDictionary = RecordSetToReturn
End Function
Public Sub ExecuteActionQuery(pSqlQuery As String)
Call myCurrentDb.Execute(pSqlQuery, dbFailOnError)
End Sub
Public Function ExecuteActionQueryAndReturnRowsAffected(pQueryName As String) As Long
Call myCurrentDb.Execute(pSqlQuery, dbFailOnError)
ExecuteActionQueryAndReturnRowsAffected = myCurrentDb.RecordsAffected
End Function
Public Sub DeleteAllRowsFromTable(pTableName As String)
Dim DeleteQuery As String
DeleteQuery = "DELETE FROM [" & pTableName & "]"
Call ExecuteActionQuery(DeleteQuery)
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment