Access as an ETL Orchestrator: From SAP Exports to Azure SQL
- Maria Barnes
- 3 days ago
- 3 min read
Application Profile
Name | BISRAP |
Tasks | Orchestrate ETL/ELT from ERP/SAP exports & spreadsheets into Azure SQL using data quality gates with reject/quarantine handling. |
Users | ~50 fixed users across the Netherlands, Romania, and the United States; 100% remote. |
Software Versions | Microsoft Access 365, Azure SQL Database, Windows 10/11 on an Azure VM |
Notable Features | Modules in Access interface with SAP through the GUI scripting object. Makes use of Access' ease of connection with SQL Azure and offloads heavy data manipulation to the more powerful SQL. |
Company | Bekaert Combustion Technology B.V., Netherlands |
Author | Bert Hoekstra (Manufacturing Process & Systems Specialist) Adapted for AFo by Maria Barnes |
This showcase demonstrates how Microsoft Access can act as an ETL/ELT orchestrator between ERP exports and Azure SQL. Access/VBA handles scheduling, retries, logging, and a simple UI, while heavy transforms run in T-SQL.
How a lightweight Access front end coordinates a production‑grade ELT pipeline for SAP data.
Bekaert Heating Solutions produces over 50 models of premix gas burners designed for commercial and industrial heating. Because our SAP setup plans per order/date at a work center level, but production is organized by lines, planning became chaotic. We built BISRAP (Burner Insight System – Resource and Performance) to bridge that gap: BISRAP maps work centers to production lines, giving planners a clear, line-level view of scheduled orders.
How it works
Data is pulled from SAP via standard transactions, exported to Excel, imported into MS Access, then staged and processed with stored procedures into Azure SQL. What started as a manual process (max once per day) is now fully automated with VBA scripting. The result: near real-time insights, less manual work, fewer errors, and more accessible data for operators.
Detailed Process (Step‑by‑Step)
1) Trigger & Orchestration
Windows Task Scheduler starts a batch file and opens the Access app without the need for human intervention.
Access resolves the configured trigger and selects the corresponding module to run.
Scheduled runs make use of retries, logging, and email alerts for exceptions.
2) Extract (SAP → Excel)
SAP GUI scripting opens SAP, selects the required transaction/variant, and exports the report to Excel. The Access VBA module interfaces with the SAP GUI through a call to GetObject("SAPGUI") and then uses the built in methods of the class to find and download data. See Introducing the SAP GUI for information on details.
The file is stored in a fixed network/share location. SAP and Excel are closed cleanly.
3) Load (Excel → Access → Azure SQL Staging)
Access imports the spreadsheet (multiple sheets supported).
Validation & comparison against existing data - only new/changed rows move forward.
Data is written to an Azure SQL staging table.
4) Transform (Staging → Curated)
A stored procedure in SQL Server performs the set‑based processing (e.g., MERGE, checks, dedupe).
The staging‑first approach also keeps live updates very fast - practically unnoticeable for end users.
5) Monitoring & Logging
All steps are logged (what/when/result) and viewable in the Access application.
Exceptions are traceable; failed steps are easy to pinpoint (extract vs load vs transform).
6) Full Automation & Performance
A lightweight job runner sequences the steps (extract → validate/load → SQL transform) without human intervention.
Jobs are retry-safe.
The Access ↔ Azure SQL architecture keeps the experience fast and responsive, even across multiple plants.
Summary
Besides the typical Access application features like providing the desired view into data (in this case organized by production line as opposed to work center), this application demonstrates how VBA modules can utilize the power of connecting to outside COM objects and other data sources via linked tables to orchestrate an entire pipeline.
One of the true powers of Microsoft Access is its ability to easily connect to the best tools for each part of the pipeline. Access provides a great interface between Excel exports and SQL Server data through its ease of imports and the use of linked SQL tables. SQL Server, on the other hand, has more data transformation power built into its T-SQL language, so it makes sense to do those tasks in SQL Server.
The Showcase section presents interesting Access projects to demonstrate the current range of usage and capabilities of the product. Interesting can refer to technical, organizational or thematic aspects. If you would like to suggest an application for an article, please contact us using this form. |