MSAccessTools Class Library

A COM-Visible VB.NET library that provides utilities for working with Microsoft Access databases.

This library allows you to automate Access operations from environments such as VBScript, VBA, and other COM-based languages.

---

📖 Table of Contents

• Overview
• Requirements
• Other Considerations
  • ODBC Driver Credentials
• Library Classes
  • RunQuery

---

🧩 Overview

The MSAccessTools library simplifies automation and data exchange with Microsoft Access databases — particularly for scripted environments that don’t natively support the Access object model.

It can:

• Open and execute saved Access queries.
• Pass in parameters dynamically.
• Export query results directly to Excel.
• Optionally log actions and results to file.

---

⚙️ Requirements

Each class may have its own dependencies, but the following are required for the library as a whole:

🔸 Microsoft Access Database Engine 16.0 (ACE / OLEDB 16.0)

This installs the Microsoft Office 16.0 Access database engine Object Library COM component, which enables interaction with Access databases via DAO and OLEDB.

💡 Note: This does not require a Microsoft Access license.

Installation Steps:

1. Download the Microsoft Access Database Engine 2016 Redistributable from Microsoft’s official site.
2. Choose the version matching your Office or system architecture (32-bit or 64-bit).
3. Install the package:

   AccessDatabaseEngine_x64.exe /quiet

   ⚠️ If you have Office installed in a different bitness (e.g., 32-bit Office but need 64-bit driver), you may need to uninstall or use the /quiet or /passive switches to bypass setup restrictions.

Back to top ↑

---

🧠 Other Considerations

🔐 ODBC Driver Credentials

For security reasons, classes in this library do not allow user credentials to be passed in code. This prevents credentials (e.g. Sage logins) from being inadvertently stored in scripts.

When running Access queries that use a Sage ODBC data source:

• Ensure your DSN already has the credentials stored.
• Do not use the default SOTAMAS90 driver, as it requires a company code to be passed with the credentials.
• Instead, create a dedicated DSN per company (e.g. SOTAMAS90-MVC).

While Access queries can store credentials directly, this is strongly discouraged since anyone running the query would inherit those stored credentials.

🧭 For assistance configuring DSNs correctly, contact Keith Davis (extension x5213 or email).

Back to top ↑

---

🧱 Library Classes

⚙️ RunQuery Class

Requirements

• Microsoft Excel 16.0 Object Library
  Used when exporting query results to Excel via ExportToExcel().

Properties

Property	Type	Description
DBPath	String	Full path to the Access database (.accdb or .mdb).
QueryName	String	The name of the stored Access query to execute.
LogPath	String	Optional log file path. Logging is disabled if not set.
Parameter(name)	Variant	Sets named parameters for the Access query (if applicable).

Subroutines

Subroutine	Description
ClearParameters()	Clears all parameter values currently stored for the query.
ExportToExcel()	Executes the query and exports results to a new Excel workbook in memory.

Functions

None.

---

💻 Example Usage

' Early binding (VBA example)
Dim oQry As MSAccessTools.RunQuery
Set oQry = New MSAccessTools.RunQuery

' Late binding (VBScript example)
Dim oQry
Set oQry = CreateObject("MSAccessTools.RunQuery")

' Path to the database containing the stored query
oQry.DBPath = "\\1051files\acct$\DBFiles\CachedTables.accdb"

' (Optional) Log file path
oQry.LogPath = "C:\Users\Username\Documents\Logs\PaymentLookupLog.log"

' Query name within the Access database
oQry.QueryName = "PaymentLookup"

' Pass any parameters (only required if the query uses them)
oQry.Parameter("PaymentAmt") = 587.15

' Run and export the query to Excel
oQry.ExportToExcel()

Back to top ↑