Cat On A Spreadsheet

Cat On A Spreadsheet

Building a Universal Database Access Layer in VBA (Reusable ADO + DAO Wrapper Design)

When Excel projects scale beyond single-user workbooks and move into operational reporting + automation roles, the number one major maintainability problem that appears is how teams handle database access. Most Excel VBA scripts end up with connection strings embedded in 40 different modules, SQL scattered everywhere, and mixed usage of ADO and DAO depending on who last edited the macro. That is not maintainable long term — and it absolutely breaks the minute IT migrates Access to SQL Server or SQL Server to Azure SQL.

 

This post shows you how to architect a reusable database access module in VBA that abstracts away the underlying DB engine, so your “business logic” code never directly touches DAO or ADO again. The result is that changing database vendor becomes a one-module edit — instead of rewriting every procedure in your workbook.

 

Architectural Goal

 

We want a single module responsible for all DB interactions.

  • You set the DB type (SQL Server or Access)

  • You set the ConnectionString

  • All query calls go through one unified function

 

Your VBA code does not know or care what DB engine is behind it — it simply calls RunQuery("SELECT ...") and receives back a Recordset object.

This is actual maintainable architecture.

 

The Universal Database Module

Option Explicit

Private Const CONN_TYPE_SQL As String = "SQL"
Private Const CONN_TYPE_ACCESS As String = "ACC"

Private gConnType As String
Private gConnString As String

Public Sub InitDB(ConnectionType As String, ConnectionString As String)
gConnType = ConnectionType
gConnString = ConnectionString
End Sub

Public Function RunQuery(SQLText As String) As Object
If gConnType = CONN_TYPE_SQL Then
Set RunQuery = RunQueryADO(SQLText)
ElseIf gConnType = CONN_TYPE_ACCESS Then
Set RunQuery = RunQueryDAO(SQLText)
Else
Err.Raise vbObjectError + 1001, , "DB Not Initialized"
End If
End Function

Private Function RunQueryADO(SQLText As String) As Object
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = gConnString
cn.Open
Set rs = cn.Execute(SQLText)
Set RunQueryADO = rs
End Function

Private Function RunQueryDAO(SQLText As String) As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = OpenDatabase(gConnString)
Set rs = db.OpenRecordset(SQLText)
Set RunQueryDAO = rs
End Function

 

Example Usage

 

SQL Server: 

Sub TestSQL()
InitDB "SQL", "Provider=SQLOLEDB;Data Source=Server01;Initial Catalog=SalesDB;Integrated Security=SSPI;"

Dim rs As Object
Set rs = RunQuery("SELECT TOP 50 * FROM Customers")

Debug.Print rs.Fields("CustomerName").Value
End Sub

 

Access:

Sub TestAccess()
InitDB "ACC", "C:\Data\Sales.accdb"

Dim rs As Object
Set rs = RunQuery("SELECT * FROM Products WHERE QtyInStock <> 0")

Debug.Print rs.Fields("ProductName").Value
End Sub

 

Why this matters

 

This is the difference between script automation and software architecture. When your company migrates from Access → SQL Server — which they eventually will — you do not rewrite every reporting macro. You simply adjust the ConnectionString in one location. And if someone in the future wants to plug in SQLite or another provider — this module is the place where that can be added without touching any business logic. This is the pattern that makes VBA scalable inside corporate ecosystems.

10 November 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet