Solutions in Microsoft Access

Many of my Solutions in Microsoft Access are designed for ETL, to extract data from external sources, transform it, and apply business rules to streamline workflows. Over time, I’ve refined the ETL process to enhance efficiency and reduce code maintenance.

The Challenge of Traditional ETL in Access

Initially, I used import specifications for each file, combined with queries and VBA code to load data and enforce business rules. However, as file formats evolved or business requirements changed, maintaining these applications became cumbersome. Handing off such systems to business users for independent management was nearly impossible.

To overcome these challenges, I developed a generic import routine that empowers users to:

Define the expected files (Excel, CSV up to 255 columns, or CSV over 255 columns, termed in my code as “G255”).
Specify and modify business rules through a user-friendly interface.
Import, transform, and export data without needing to alter VBA code.

This approach minimizes code maintenance and enables users to manage imports and transformations independently.

Key Features of the Generic Import Routine

G255 Import for Large Datasets
Access limits tables to 255 columns. The G255 module extracts the required columns from larger datasets.
A user interface allows business users to adjust file imports, add new files, and update business rules dynamically.
SQL-Based Business Rules
Transformations and rules are executed as SQL commands linked to the import routine or called separately.
- Rules are defined and documented by users.
- Users can adjust the sequence of rule execution as needed.
Built-In Documentation and Reporting
- A memo field captures the purpose of each rule or transformation.
- A comprehensive report outlines all execution steps in sequence.
User-Friendly Interface
- Designed with a simple ribbon menu for easy navigation.
- Options to Import Data, Apply Rules, and Export Data directly from the ribbon.
- Navigation pane remains hidden for focus; access it via F11 or open in design mode by holding Shift during launch.

ETL Process Overview

Solutions in Microsoft Access - File imports — Import File Definitions

Solutions in Microsoft Access - Business Rules — Business Rules

In this screen the user specifies the expected file patterns. Define the file type , either csv , G255 or Excel and the rule group to apply. Files can be grouped to allow multiple files imports to be called on a single import.

Users can document the reasons behind the rules. Multiple rules are grouped into a section. A report option allows documentation of all rules. The user can selectively turn rules on or off.

For each rule the user has the option to view the detail of the query to be applied. Users also have the option to call internal functions.

Calling the ETL Process Function

To process the file imports and apply the business rules we call the function “ImportFile_Generic” – and pass the parameter

Section name – this can include multiple file patterns and file type
Folder Location – The import file location
Calling Form – this is used to pass back user update messages
Record ID – this optional parameter lets the user test a single record while loading
File Prefix – Optional setting to further filter imports to a given prefix

Call ImportFile_Generic(“Section Name”, Me.txtFolder, Me.Name, 0, “”)

Sample Solutions in Microsoft Access

A downloadable sample application demonstrates this solution in action.
Important Notes:

If you’re using an older Access version, adjust the code to ensure compatibility.
The application includes a ribbon menu for quick access to the ETL process.

How to Use the Sample Application

Access the Process Imports Screen
From the ribbon, select Process Imports to open the import workflow screen.
Run ETL Operations
Use buttons for:
- Import Data: Calls ImportGeneric("Folder Location", "Process Name") to load files tagged under a specific process name (e.g., “Import Invoices”).
- Apply Rules: Executes business rules on the normalized data.
- Export Data: Exports the processed data for further use.

Benefits of This Approach

Reduced Maintenance: Business users can independently modify file imports and rules.
Scalability: Supports large datasets with customizable import routines.
Flexibility: Easily adapts to evolving business requirements.
Efficiency: Simplifies and speeds up the ETL process with SQL-driven transformations.

Download the Sample Application

Click here to download the sample application.
Experience the power of streamlined ETL operations in Microsoft Access. Simplify data imports, enforce business rules, and export insights—all with minimal code maintenance.