Skip to content

Instantly share code, notes, and snippets.

@mjdescy
Created May 30, 2018 15:10
Show Gist options
  • Save mjdescy/6c89c5969c3618e220814c6d04f6e449 to your computer and use it in GitHub Desktop.
Save mjdescy/6c89c5969c3618e220814c6d04f6e449 to your computer and use it in GitHub Desktop.
Microsoft Access VBA class to run queries via DAO
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
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)
rs.MoveFirst
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