Cat On A Spreadsheet

VBA and Databases (Part 2): Secure Connections and Parameterized Queries

In the previous post, we connected Excel VBA to external databases like SQL Server and Access, ran queries, and pulled results straight into worksheets.

That’s a great foundation — but the version we built was wide open. The connection string had credentials in plain text, and the SQL command was built by string concatenation (which is both unsafe and error-prone).

In this follow-up, we’ll improve security and reliability by:

Removing hard-coded passwords from your VBA code
Loading credentials from a hidden config sheet or external file
Using basic encryption to obscure sensitive data
Switching to parameterized SQL queries
Integrating error logging and email notifications

Step 1: Storing Credentials Securely

Instead of hardcoding credentials, we’ll store them on a hidden worksheet named “Config”:

Field	Example Value
DBServer	SQLSERVER01
Database	SalesDB
UserID	reportuser
Password	MyEncryptedPassword123

Then we’ll read these values through a helper function. To add a small layer of protection, we’ll use simple reversible encryption (not perfect, but better than plaintext).

VBA Code: Reading and Decrypting Credentials

  Option Explicit
 
 Private Const ENCRYPT_KEY As Integer = 7
 
 '--- Read database settings from Config sheet ---
 
 Public Function GetDBSetting(ByVal key As String) As String
 
      Dim ws As Worksheet
 
      Set ws = ThisWorkbook.Sheets("Config")
 
      GetDBSetting = ws.Range("B" & Application.Match(key, ws.Range("A:A"), 0)).Value
 
 End Function
 
 '--- Simple XOR-based decryption ---
 
 Public Function DecryptText(ByVal encryptedText As String) As String
 
      Dim i As Long, output As String
 
      For i = 1 To Len(encryptedText)
 
          output = output & Chr(Asc(Mid(encryptedText, i, 1)) Xor ENCRYPT_KEY)
 
      Next i
 
      DecryptText = output
 
 End Function

Encrypt your password once manually using the same XOR logic and paste the encrypted string in the Config sheet. Never store plain text credentials.

Step 2: Building the Secure Connection

Now we can use our settings to open a database connection safely.

  Public Function GetDBConnection() As Object
 
      On Error GoTo ErrHandler
 
      Dim conn As Object
 
      Set conn = CreateObject("ADODB.Connection")
 
      Dim connStr As String
 
      connStr = "Provider=SQLOLEDB;" & _
 
          "Data Source=" & GetDBSetting("DBServer") & ";" & _
 
          "Initial Catalog=" & GetDBSetting("Database") & ";" & _
 
          "User ID=" & GetDBSetting("UserID") & ";" & _
 
          "Password=" & DecryptText(GetDBSetting("Password")) & ";"
 
      conn.Open connStr
 
      Set GetDBConnection = conn
 
      Exit Function
 
 ErrHandler:
 
      LogAndEmailError "GetDBConnection", Erl
 
 End Function

Step 3: Parameterized Queries

Instead of building SQL like this:

sql = "SELECT * FROM Sales WHERE Region = '" & userInput & "'"

We’ll use ADODB.Command and parameter objects — much safer and faster:

  Public Sub GetSalesData(ByVal region As String)
 
      On Error GoTo ErrHandler
 
      Dim conn As Object, cmd As Object, rs As Object
 
      Set conn = GetDBConnection()
 
      Set cmd = CreateObject("ADODB.Command")
 
      With cmd
 
          .ActiveConnection = conn
 
          .CommandText = "SELECT * FROM Sales WHERE Region = ?"
 
          .CommandType = 1 'adCmdText
 
          .Parameters.Append .CreateParameter("Region", 200, 1, 50, region)
 
          Set rs = .Execute
 
      End With
 
      Sheet1.Range("A2").CopyFromRecordset rs
 
      rs.Close: conn.Close
 
      Exit Sub
 
 ErrHandler:
 
      LogAndEmailError "GetSalesData", Erl
 
 End Sub

Step 4: Centralized Error Logging and Notification

We’ll use a shared error handler that logs to a time-stamped file and emails the admin (for this example, DBAdmin@company.com).

  Public Sub LogAndEmailError(ByVal procName As String, ByVal lineNum As Long)
 
      Dim logPath As String, logLine As String, fNum As Integer
 
      Dim olApp As Object, olMail As Object
 
      logPath = ThisWorkbook.Path & "\ErrorLog_" & Format(Now, "yyyymmdd_hhnnss") & ".txt"
 
      logLine = Now & " | " & procName & " | Line " & lineNum & _
 
          " | Err " & Err.Number & ": " & Err.Description
 
      fNum = FreeFile()
 
      Open logPath For Output As #fNum
 
          Print #fNum, logLine
 
      Close #fNum
 
      Set olApp = CreateObject("Outlook.Application")
 
      Set olMail = olApp.CreateItem(0)
 
      With olMail
 
          .To = "DBAdmin@company.com"
 
          .Subject = "Database Error - " & procName
 
          .Body = logLine
 
          .Attachments.Add logPath
 
          .Send
 
      End With
 
 End Sub

Wrapping Up

You now have a secure, professional, and maintainable database integration in VBA. It:

Reads connection info from a protected source
Obscures passwords
Uses parameterized SQL for safety
Centralizes logging and alerting

This setup scales well across teams and avoids exposing sensitive info in your VBA codebase — a key step toward enterprise-grade Excel automation.