Automating Microsoft Access Database Operations with Power Automate Desktop: Opening Reports, Running Queries, and Exporting Results to Excel in Unattended RPA Workflows

Introduction

Picture this: your organization runs a critical operations database in Microsoft Access. Every weekday morning, someone opens it manually, runs three parameter queries, exports the results to Excel, formats the output, and emails the report to department heads by 8 AM. It takes about forty-five minutes. The person who does it has been doing it for six years. Now they're leaving, and nobody else fully understands the quirks of the Access database — which queries to run in which order, which export button doesn't work and requires a workaround, and why the database sometimes throws a "could not lock file" error that requires a specific sequence of clicks to dismiss.

This is exactly the scenario that unattended RPA was built for. Microsoft Access remains deeply embedded in enterprise operations — finance departments, inventory systems, compliance databases, HR tracking — and the majority of these installations will never be migrated to a modern platform. They sit in a gray zone: too important to ignore, too legacy to integrate via API. Power Automate Desktop can take over the entire daily ritual, running at a scheduled time with no human present, opening the database, executing queries, exporting results to Excel with consistent formatting, and handing the files off to downstream processes.

By the end of this lesson, you'll be able to build a production-grade, unattended desktop flow that automates the full Access reporting cycle. You'll understand not just the mechanical steps but the architectural decisions that make the difference between a fragile demo and a bot that runs reliably for months.

What you'll learn:

• How to launch and control Microsoft Access programmatically, handling startup dialogs and trusted location warnings
• Techniques for opening and running Access queries and reports using both UI automation and VBA/COM automation
• Multiple strategies for exporting Access data to Excel — through the UI, through Access macros, and through direct COM object manipulation
• How to handle the most common failure modes in unattended Access automation: locked files, missing ODBC drivers, parameter prompts, and stale connections
• How to structure the full workflow for reliable unattended execution, including error recovery and run logging

---

Prerequisites

You should be comfortable with Power Automate Desktop fundamentals — if you haven't built and run a desktop flow before, start with Getting Started with Power Automate Desktop: Installing, Recording, and Running Your First Desktop Flow. You should understand how variables and data tables work in PAD (see Variables, Lists, and Data Tables in Power Automate Desktop: A Complete Practitioner's Guide), and you should have working knowledge of Excel automation (covered in Automating Excel with Power Automate Desktop: Reading, Writing, and Running Macros). Familiarity with the difference between attended and unattended run modes is assumed — if you're unclear on that distinction, read Attended vs Unattended RPA: Choosing a Run Mode and Configuring Machines in Power Automate before proceeding.

On the machine side, you need Microsoft Access installed (not just the Access Runtime), the target .accdb or .mdb file accessible on a local or network path, and Power Automate Desktop with an active Power Automate license that includes unattended run capacity.

---

Understanding Your Options: Three Automation Approaches

Before writing a single action, you need to make a deliberate architectural decision: how will you actually automate Access? There are three fundamentally different approaches, and picking the wrong one is the single biggest source of project failure.

Approach 1: UI Automation — You interact with the Access application window directly, clicking buttons, navigating menus, and responding to dialogs just as a human would. This is the most approachable method and works well when Access has a consistent, stable interface (forms, report buttons, export menus).

Approach 2: VBA/COM Automation via Run Script — You use Power Automate Desktop's "Run VBScript" action to instantiate Access as a COM object (CreateObject("Access.Application")), then call its object model directly. No visible window, no clicking. You open tables, run queries, and export data entirely through code. This is faster and far more reliable for unattended scenarios, but requires that you understand the Access object model.

Approach 3: Direct Database Connectivity — You bypass Access entirely and connect to the underlying .accdb file as an ODBC/OLEDB data source from PAD's database actions or via PowerShell. This works for querying data, but it can't run Access-specific macros, trigger reports formatted with Access's report engine, or run action queries that modify data with Access's own error handling.

Most production automations use a hybrid of Approach 1 and Approach 2. The COM approach handles the heavy lifting (running queries, exporting results), while UI automation handles anything that the object model can't reach — particularly Access's report preview, which often behaves differently in headless COM mode.

---

Setting Up the Access Automation Environment

Trusted Locations and Security Warnings

Microsoft Access has a security model that will stop your unattended bot cold if you don't address it first. When Access opens a database from a path it doesn't trust, it displays a security warning bar and may disable all macros and VBA. In an unattended scenario, there's no human to click "Enable Content."

The fix is to add the database's folder as a Trusted Location in Access before you deploy. Open Access, go to File → Options → Trust Center → Trust Center Settings → Trusted Locations, and add the network path or local folder where your database lives. Do this on the automation machine itself, under the service account that will run the unattended bot — not your personal account.

If you can't add a Trusted Location (for example, your IT department locks down Trust Center via Group Policy), you have two alternatives: sign the database's VBA project with a trusted code-signing certificate, or set the macro security level to "Enable all macros" (not recommended in production for obvious reasons). The certificate approach is cleanest and will survive GPO enforcement.

The Service Account and File Locking

Access creates a lock file (.laccdb for .accdb files, .ldb for .mdb files) whenever a database is open. This is where "could not lock file" errors originate. In an unattended workflow, you need to ensure:

1. No other user has the database open exclusively when your bot runs
2. Your bot cleans up after itself — if a previous run crashed and left Access running, the lock file persists and the next run fails

Add a pre-flight check at the start of your flow: use the "If File Exists" action to check whether the lock file exists before attempting to open the database. If it does, either wait and retry (using a loop with a delay), or surface an alert to the monitoring team.

# PAD pseudocode for pre-flight lock file check
Set Variable: DBPath = "\\FileServer\Ops\Operations.accdb"
Set Variable: LockFilePath = "\\FileServer\Ops\Operations.laccdb"
Set Variable: MaxRetries = 5
Set Variable: RetryCount = 0

LOOP WHILE RetryCount < MaxRetries:
    IF File.Exists(LockFilePath):
        Wait 60 seconds
        RetryCount = RetryCount + 1
    ELSE:
        BREAK
END LOOP

IF RetryCount >= MaxRetries:
    THROW ERROR "Database locked after 5 minutes - aborting"

---

Approach 2 in Depth: COM Automation with VBScript

This is where the real power lives. The PAD "Run VBScript" action lets you execute an entire VBScript program synchronously and capture its output. You can use this to drive Access completely programmatically.

Opening the Database and Running a Query

Here's a complete VBScript that opens an Access database, runs a select query, and writes the results to a CSV file:

Dim accApp
Dim db
Dim rs
Dim fso
Dim ts
Dim outputPath
Dim headers
Dim rowData
Dim i

outputPath = "C:\Automation\Output\SalesReport_" & Format(Now(), "YYYYMMDD") & ".csv"

' Create Access application object
Set accApp = CreateObject("Access.Application")
accApp.Visible = False  ' Headless - no window

' Open the database (read-only = False, exclusive = False)
accApp.OpenCurrentDatabase "\\FileServer\Ops\Operations.accdb", False

' Open a recordset from a saved query
Set db = accApp.CurrentDb()
Set rs = db.OpenRecordset("qry_DailySalesSummary", 2)  ' 2 = dbOpenDynaset

' Write to CSV
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(outputPath, 2, True)  ' 2 = ForWriting, create = True

' Write header row
headers = ""
For i = 0 To rs.Fields.Count - 1
    If i > 0 Then headers = headers & ","
    headers = headers & """" & rs.Fields(i).Name & """"
Next
ts.WriteLine headers

' Write data rows
Do While Not rs.EOF
    rowData = ""
    For i = 0 To rs.Fields.Count - 1
        If i > 0 Then rowData = rowData & ","
        Dim fieldVal
        fieldVal = rs.Fields(i).Value
        If IsNull(fieldVal) Then
            fieldVal = ""
        End If
        rowData = rowData & """" & Replace(CStr(fieldVal), """", """""") & """"
    Next
    ts.WriteLine rowData
    rs.MoveNext
Loop

' Clean up
ts.Close
rs.Close
db.Close
accApp.CloseCurrentDatabase
accApp.Quit

Set ts = Nothing
Set fso = Nothing
Set rs = Nothing
Set db = Nothing
Set accApp = Nothing

WScript.Echo "SUCCESS:" & outputPath

In PAD, you'd use the "Run VBScript" action with this script embedded (or loaded from a file), then capture the output variable. If the script echoes SUCCESS:C:\Automation\Output\SalesReport_20241118.csv, you parse that output to get the file path for the next actions in the flow.

Running Action Queries (Append, Update, Delete)

If your workflow includes running Access action queries before the export — say, a query that populates a summary table from raw transaction data — you can run these directly through the DAO object model:

' Run an action query (no recordset returned)
db.Execute "qry_PopulateDailySummary", 128  ' 128 = dbFailOnError

' Check how many records were affected
Dim recordsAffected
recordsAffected = db.RecordsAffected
WScript.Echo "Records processed: " & recordsAffected

The dbFailOnError flag (value 128) is critical. Without it, Access's DAO will silently ignore errors in action queries — a record that violates a constraint just gets skipped. With it, any failure raises a VBScript error that PAD can catch.

Handling Parameter Queries

Parameter queries are common in Access databases that were designed for human use — users are expected to type in a date range, a department code, or a region. When you call OpenRecordset on a parameter query without supplying parameters, Access raises error 3061 ("Too few parameters").

The solution is to either: (a) create a QueryDef object, set its parameters, and then open a recordset from it; or (b) execute the query as a SQL string with the parameters embedded.

' Approach A: QueryDef with parameters
Dim qdf
Set qdf = db.QueryDefs("qry_SalesByDateRange")
qdf.Parameters("StartDate") = CDate("2024-11-01")
qdf.Parameters("EndDate") = CDate("2024-11-30")
Set rs = qdf.OpenRecordset(2)

' Approach B: Inline SQL (better for dynamic date ranges)
Dim startDate, endDate, sql
startDate = Format(DateAdd("d", -1, Date()), "YYYY-MM-DD")  ' Yesterday
endDate = Format(Date(), "YYYY-MM-DD")  ' Today
sql = "SELECT * FROM tbl_Sales WHERE SaleDate >= #" & startDate & "# AND SaleDate <= #" & endDate & "#"
Set rs = db.OpenRecordset(sql, 2)

In PAD, you'd pass StartDate and EndDate as input variables to the flow, construct them in PAD using the "Format datetime" action, then pass them into the VBScript via string interpolation in the "Run VBScript" action. This is cleaner than hardcoding dates.

---

Approach 1 in Depth: UI Automation for Access Reports

The Access report engine produces beautifully formatted output — pagination, grouping, conditional formatting — that the raw DAO recordset approach can't replicate. When your stakeholders need the formatted report (not just the data), you need to interact with the Access UI to open the report and trigger the export.

Launching Access and Handling the Startup Sequence

Use PAD's "Launch application" action with the full path to the .accdb file as an argument to MSACCESS.EXE:

Application path: C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE
Arguments: "\\FileServer\Ops\Operations.accdb"
Window style: Normal

After launching, wait for Access to fully load. The key window to wait for is the main Access window, not just the process. Use the "Wait for window" action targeting the Access application window with a WindowTitle contains 'Operations' condition.

Once Access is open, you may see a startup form if the database has one configured. Your flow needs to handle this: if the startup form is present, close it or work with it; if not, continue to the Navigation Pane. Build this as a conditional check using "If UI element exists."

For robust selector design in Access, which has a fairly consistent internal UI structure, focus on control names and class names rather than position or text where possible. The Access Navigation Pane, for example, has a consistent internal class hierarchy that doesn't change with database content. This is exactly the kind of selector strategy covered in UI Elements and Selectors in Power Automate Desktop: Building Automations That Don't Break.

Opening and Exporting a Report via the Ribbon

To export a report through the UI:

1. Right-click the report name in the Navigation Pane to open the context menu
2. Select "Export" → "Excel" (or another format)
3. Handle the "Export — Spreadsheet" wizard dialog: set the file path, check "Export data with formatting and layout," choose whether to open the file after export, and click OK

The context menu approach is the most reliable because it avoids navigating the ribbon, which can have variable states depending on what's currently selected. The "Export Spreadsheet" dialog is a standard Windows dialog with stable element names.

After the export completes, Access shows a "Save Export Steps" dialog. In unattended workflows, click "Close" on this dialog — you don't need to save the steps because PAD is the orchestrator, not Access's macro system.

---

Exporting to Excel: Making the Output Production-Ready

Whether you exported via COM to CSV or via UI to Excel, you now have raw output that typically needs post-processing before it's usable. This is where PAD's Excel integration earns its keep.

From CSV to Formatted Excel

If you used the COM/VBScript approach and wrote a CSV, your next PAD actions should:

1. Open Excel with the "Launch Excel" action
2. Open the CSV file
3. Auto-fit columns using the "Run Excel Macro" action or a VBScript call
4. Apply a named table style
5. Add a timestamp and report title in a header row
6. Save as .xlsx to the output directory

For the formatting steps, the cleanest approach is a short embedded Excel VBA macro. Rather than trying to click through Excel's ribbon to apply formatting (fragile, brittle), you inject the formatting logic via code:

' Executed via PAD's "Run Excel Macro" action
Sub FormatSalesReport()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim tbl As ListObject
    
    Set ws = ActiveWorkbook.Sheets(1)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Add title row at top
    ws.Rows(1).Insert
    ws.Rows(1).Insert
    ws.Cells(1, 1).Value = "Daily Sales Summary Report"
    ws.Cells(2, 1).Value = "Generated: " & Format(Now(), "YYYY-MM-DD HH:MM:SS")
    ws.Cells(1, 1).Font.Bold = True
    ws.Cells(1, 1).Font.Size = 14
    
    ' Apply table formatting starting from row 3 (data starts here)
    Set tbl = ws.ListObjects.Add(xlSrcRange, _
        ws.Range(ws.Cells(3, 1), ws.Cells(lastRow + 2, lastCol)), , xlYes)
    tbl.TableStyle = "TableStyleMedium9"
    
    ' Auto-fit columns
    ws.Columns.AutoFit
    
    ' Freeze panes at row 4
    ws.Activate
    ActiveWindow.FreezePanes = False
    ws.Cells(4, 1).Select
    ActiveWindow.FreezePanes = True
End Sub

For deeper coverage of working with Excel ranges and sheets without relying on macros for every operation, see Working with Excel Ranges, Sheets, and Named Cells in Power Automate Desktop: Reading Tables, Writing Data, and Switching Worksheets Without Macros.

Handling Multiple Query Exports in One Workbook

A common requirement is exporting three or four different queries into separate sheets of a single Excel workbook. The cleanest architecture is:

1. Run all queries via COM/VBScript and export each to a separate CSV
2. Open one Excel workbook
3. Loop through the CSV files, importing each into a new sheet with the appropriate sheet name
4. Apply formatting
5. Save and close

This separation of concerns — data extraction in VBScript, presentation assembly in Excel/PAD — makes each step independently testable and easier to troubleshoot.

# PAD pseudocode for multi-sheet assembly
SET ExcelInstance = Excel.LaunchNew()

FOR EACH CsvFile IN ["SalesSummary.csv", "InventoryStatus.csv", "OpenOrders.csv"]:
    SET SheetName = GetFileNameWithoutExtension(CsvFile)
    Excel.AddWorksheet(ExcelInstance, SheetName)
    Excel.SetActiveWorksheet(ExcelInstance, SheetName)
    
    # Read CSV rows and write to sheet
    SET CsvData = File.ReadCSV(CsvFile)
    Excel.WriteDataTable(ExcelInstance, CsvData, StartRow=1, StartCol=1)
    
    # Run formatting macro
    Excel.RunMacro(ExcelInstance, "FormatSheet", SheetName)
END FOR

# Remove the default empty Sheet1
Excel.DeleteWorksheet(ExcelInstance, "Sheet1")
Excel.SaveAs(ExcelInstance, OutputPath, "xlsx")
Excel.Close(ExcelInstance)

---

Structuring for Reliable Unattended Execution

An unattended bot running at 5:30 AM with no human watching needs to handle every foreseeable failure gracefully. Here's how to architect the full flow.

The Four-Phase Structure

Organize your flow into four explicit phases, each as a separate subflow (a practice covered well in Subflows and Reusable Logic in Power Automate Desktop):

Phase 1: Pre-Flight — Verify prerequisites. Check that the database file exists and isn't locked. Verify the output directory exists and is writeable. Check that Access is not already running from a previous failed run (kill any orphaned MSACCESS.EXE processes). Initialize a run log file.

Phase 2: Data Extraction — Open the database via COM, run queries in the correct sequence, export to intermediate CSVs, close Access cleanly.

Phase 3: Report Assembly — Open Excel, assemble the multi-sheet workbook from CSVs, apply formatting, save to the final output path.

Phase 4: Post-Processing and Notification — Move or archive intermediate files, write the final run log entry (records processed, duration, output file path), and trigger downstream actions (email, SharePoint upload, etc.).

Error Handling at Each Phase

Wrap each phase in an "On block error" handler. The handling strategy should differ by phase:

• Pre-Flight failure: Abort immediately and notify the monitoring team. Don't proceed with a locked database.
• Extraction failure: Attempt to close Access cleanly via VBScript (accApp.Quit), even if the query failed. Log the specific error. Notify and abort.
• Assembly failure: Log and notify. The CSVs exist, so a human can manually assemble the report if needed.
• Post-Processing failure: This is the least critical phase. Log the error but don't alert as urgently — the report exists, it just didn't get distributed automatically.

For a thorough treatment of error handling patterns in PAD, including retry policies and recovery screenshots, see Error Handling in Desktop Flows: On Block Error, Retry Policies, and Recovery Screenshots.

Killing Orphaned Access Processes

A previous run that crashed mid-execution often leaves MSACCESS.EXE running with the database still open and locked. Your pre-flight subflow should detect and handle this:

' VBScript to check for and kill orphaned Access processes
Dim objWMI, colProcesses, objProcess
Set objWMI = GetObject("winmgmts:\\.\root\cimv2")
Set colProcesses = objWMI.ExecQuery("SELECT * FROM Win32_Process WHERE Name = 'MSACCESS.EXE'")

Dim processCount
processCount = 0

For Each objProcess In colProcesses
    objProcess.Terminate()
    processCount = processCount + 1
Next

WScript.Echo "Terminated:" & processCount

Run this in a "Run VBScript" action at the start of Phase 1. If any processes were terminated, wait 5 seconds before proceeding to ensure the lock file is released.

Run Logging

Every unattended run should write to a persistent log. The simplest approach is a CSV log file appended to on each run:

Timestamp,RunStatus,RecordsExported,OutputFile,ErrorMessage
2024-11-18 05:31:04,SUCCESS,1847,\\FileServer\Reports\DailySales_20241118.xlsx,
2024-11-19 05:31:11,FAILED,0,,Database locked by user FINANCE\jsmith
2024-11-20 05:30:58,SUCCESS,1923,\\FileServer\Reports\DailySales_20241120.xlsx,

Build this log append as its own subflow that accepts parameters (Status, RecordCount, OutputFile, ErrorMessage) and appends a row to the log CSV. For reading and writing CSV files in PAD, see Reading and Writing to CSV and Text Files in Power Automate Desktop: Parsing Delimiters, Handling Headers, and Looping Through Records.

---

Connecting PAD Flows to the Scheduling Layer

Your Access automation doesn't run on a schedule by itself — you need to trigger it. There are two main options:

Option 1: Cloud Flow trigger — A scheduled cloud flow (in Power Automate cloud) triggers the desktop flow on your registered machine. This gives you a centralized scheduling UI in the Power Automate portal, full run history, and the ability to pass dynamic inputs (like the report date range) from the cloud flow. This is the preferred approach for enterprise deployments. For how inputs and outputs flow between cloud and desktop, see Triggering Desktop Flows from Cloud Flows: Passing Inputs and Returning Outputs.

Option 2: Windows Task Scheduler — A Task Scheduler job calls the PAD runner directly. This works without a Power Automate Premium license for scheduling specifically, but you lose centralized monitoring and alerting.

For production unattended deployments, the cloud flow approach is strongly preferable. It integrates with Power Automate's run monitoring, gives you alerting on failure, and participates in the machine group load balancing system.

---

Hands-On Exercise

This exercise builds a complete working automation for a representative scenario: an Access database with two queries (a daily summary and an exception report), exported and assembled into a two-sheet Excel workbook.

Setup

1. Create a test Access database at C:\AutomationLab\TestOps.accdb with:

   • A table tbl_Orders with fields: OrderID, OrderDate, CustomerName, Region, Amount, Status
   • Populate with at least 50 rows of realistic test data spanning the last 30 days
   • A saved select query qry_DailySummary: total orders and amount grouped by Region for today's date
   • A saved select query qry_PendingOrders: all orders where Status = "Pending"

2. Create the output directory: C:\AutomationLab\Output\

Phase 1: Pre-Flight Subflow

Create a subflow named PreFlight with these actions:

• Set variable DBPath = C:\AutomationLab\TestOps.accdb
• Set variable LockFile = C:\AutomationLab\TestOps.laccdb
• Run VBScript to kill any orphaned MSACCESS.EXE processes
• Check if DBPath exists using "If file exists" — throw error if not
• Check if LockFile exists — if yes, wait 30 seconds and check again; after 3 retries, throw error
• Set variable RunDate = today formatted as YYYYMMDD
• Set variable OutputDir = C:\AutomationLab\Output\

Phase 2: Extraction Subflow

Create a subflow named ExtractData with these actions:

• Build the VBScript below and store it in variable ExtractionScript
• Run VBScript using the extraction script
• Parse the output: if it starts with ERROR:, throw a custom error with the message
• Extract the two CSV file paths from the output (one per line prefixed with FILE:)
• Store paths in variables SummaryCSV and PendingCSV

Extraction VBScript (adapt from earlier examples):

On Error GoTo 0

Dim accApp, db, rs, fso, ts
Dim outputDir, summaryPath, pendingPath
Dim i, headers, rowData, fieldVal

outputDir = "C:\AutomationLab\Output\"

On Error Resume Next

Set accApp = CreateObject("Access.Application")
If Err.Number <> 0 Then
    WScript.Echo "ERROR:Could not create Access COM object - " & Err.Description
    WScript.Quit
End If

accApp.Visible = False
accApp.OpenCurrentDatabase "C:\AutomationLab\TestOps.accdb", False

If Err.Number <> 0 Then
    WScript.Echo "ERROR:Could not open database - " & Err.Description
    WScript.Quit
End If

Set db = accApp.CurrentDb()
Set fso = CreateObject("Scripting.FileSystemObject")

' --- Export DailySummary ---
summaryPath = outputDir & "DailySummary.csv"
Set rs = db.OpenRecordset("qry_DailySummary", 2)

If Err.Number <> 0 Then
    WScript.Echo "ERROR:Could not open qry_DailySummary - " & Err.Description
    accApp.Quit
    WScript.Quit
End If

Set ts = fso.OpenTextFile(summaryPath, 2, True)
headers = ""
For i = 0 To rs.Fields.Count - 1
    If i > 0 Then headers = headers & ","
    headers = headers & Chr(34) & rs.Fields(i).Name & Chr(34)
Next
ts.WriteLine headers

Do While Not rs.EOF
    rowData = ""
    For i = 0 To rs.Fields.Count - 1
        If i > 0 Then rowData = rowData & ","
        fieldVal = rs.Fields(i).Value
        If IsNull(fieldVal) Then fieldVal = ""
        rowData = rowData & Chr(34) & Replace(CStr(fieldVal), Chr(34), Chr(34) & Chr(34)) & Chr(34)
    Next
    ts.WriteLine rowData
    rs.MoveNext
Loop
ts.Close
rs.Close

' --- Export PendingOrders ---
pendingPath = outputDir & "PendingOrders.csv"
Set rs = db.OpenRecordset("qry_PendingOrders", 2)
Set ts = fso.OpenTextFile(pendingPath, 2, True)
headers = ""
For i = 0 To rs.Fields.Count - 1
    If i > 0 Then headers = headers & ","
    headers = headers & Chr(34) & rs.Fields(i).Name & Chr(34)
Next
ts.WriteLine headers

Do While Not rs.EOF
    rowData = ""
    For i = 0 To rs.Fields.Count - 1
        If i > 0 Then rowData = rowData & ","
        fieldVal = rs.Fields(i).Value
        If IsNull(fieldVal) Then fieldVal = ""
        rowData = rowData & Chr(34) & Replace(CStr(fieldVal), Chr(34), Chr(34) & Chr(34)) & Chr(34)
    Next
    ts.WriteLine rowData
    rs.MoveNext
Loop
ts.Close
rs.Close

' Clean up
db.Close
accApp.CloseCurrentDatabase
accApp.Quit

WScript.Echo "FILE:" & summaryPath
WScript.Echo "FILE:" & pendingPath

Phase 3: Assembly Subflow

Create a subflow named AssembleWorkbook with these actions:

1. Launch Excel (new, hidden)
2. For each CSV (Summary, then Pending):
   • Add a new worksheet with the appropriate name
   • Read the CSV using "Read from CSV file" action
   • Write the data table to the worksheet starting at row 3
   • Write report title in row 1, generation timestamp in row 2
   • Apply formatting via "Run Excel macro" (use the FormatSheet macro from earlier)
3. Delete Sheet1
4. Save as C:\AutomationLab\Output\DailyOpsReport_%RunDate%.xlsx
5. Close Excel

Phase 4: Post-Processing Subflow

1. Move the intermediate CSV files to an Archive subfolder
2. Append a success row to C:\AutomationLab\RunLog.csv

Main Flow

Call the subflows in sequence: PreFlight → ExtractData → AssembleWorkbook → PostProcessing. Wrap each call in an "On block error" action that logs the failure and sets a RunStatus variable. At the end, regardless of success or failure, call a final WriteRunLog subflow.

---

Common Mistakes & Troubleshooting

"Could not use '...accdb'; file already in use"

Cause: The database lock file exists because another session (or a previous bot run) has it open. Fix: Add the pre-flight lock file check described earlier. Verify the orphaned process termination script is running correctly. Check your service account permissions on the lock file — if the bot can't delete the lock file, it's often a permissions issue on the network share.

VBScript exits with "ActiveX component can't create object" (Error 429)

Cause: Access is not installed on the automation machine, or the Access runtime is installed instead of the full product. Fix: Verify with Dir("C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE") in VBScript. The full Access application must be installed. The Access Runtime does not expose a full COM automation interface.

Parameter queries fail with "Too few parameters" (DAO Error 3061)

Cause: Your VBScript is calling OpenRecordset directly on a query that has parameter prompts defined. Fix: Use the QueryDefs approach shown earlier, explicitly setting each parameter before opening the recordset. Alternatively, rewrite the query as SQL with parameters embedded as literal values (appropriate for date-based filters that PAD calculates).

Export produces corrupted CSV with embedded commas in fields

Cause: Text fields contain commas that aren't being properly quoted. Fix: The VBScript examples above wrap every field in double quotes and escape internal double quotes. Verify your VBScript uses Chr(34) (the double-quote character) rather than """" patterns, which can be misinterpreted in some string contexts. Test with a record that contains a comma in a text field before deploying.

Access report export produces blank Excel file

Cause: The report query returns no rows for the current parameters (e.g., no data for today's date because the database uses UTC and your server is in a different timezone). Fix: Add a row count check before exporting. In VBScript, check rs.RecordCount (you may need to call rs.MoveLast and then rs.MoveFirst first, since DAO doesn't always populate RecordCount until the recordset is fully traversed). If count is zero, log a warning rather than creating an empty file.

Unattended run fails but attended run succeeds

Cause: The most common cause is Trust Center settings not configured for the service account. The second most common is a missing printer driver — Access sometimes requires a default printer to render reports, and unattended machines often don't have a default printer configured. Fix: Configure Trust Center under the service account (log in as that user, configure settings). For the printer issue, install a virtual printer driver (Microsoft Print to PDF is usually available) and set it as the default printer on the machine.

---

Advanced Patterns

Using PowerShell Instead of VBScript for COM Automation

VBScript is reliable but dated. If your environment allows PowerShell execution (check the execution policy on the machine: Get-ExecutionPolicy), PowerShell's COM automation is more powerful and easier to debug:

$access = New-Object -ComObject Access.Application
$access.Visible = $false
$access.OpenCurrentDatabase("C:\AutomationLab\TestOps.accdb", $false)

$db = $access.CurrentDb()
$rs = $db.OpenRecordset("qry_DailySummary", 2)

$rows = @()
$fields = @()
for ($i = 0; $i -lt $rs.Fields.Count; $i++) {
    $fields += $rs.Fields.Item($i).Name
}

while (-not $rs.EOF) {
    $row = @{}
    for ($i = 0; $i -lt $rs.Fields.Count; $i++) {
        $row[$fields[$i]] = $rs.Fields.Item($i).Value
    }
    $rows += [PSCustomObject]$row
    $rs.MoveNext()
}

$rs.Close()
$access.CloseCurrentDatabase()
$access.Quit()

$rows | Export-Csv "C:\AutomationLab\Output\DailySummary.csv" -NoTypeInformation
Write-Output "SUCCESS:C:\AutomationLab\Output\DailySummary.csv"

PAD's "Run PowerShell script" action captures Write-Output the same way it captures WScript.Echo. PowerShell also gives you Export-Csv for free, which handles all the quoting edge cases correctly.

Compacting the Database After Automation

Access databases grow in file size over time due to how DAO handles deleted records. If your bot runs action queries (updates, deletes, appends), consider running a compact-and-repair as part of the post-processing phase once a week. This requires closing the database first, then using the CompactDatabase method on a new Access application instance:

Dim accApp
Set accApp = CreateObject("Access.Application")
accApp.CompactRepair "C:\AutomationLab\TestOps.accdb", "C:\AutomationLab\TestOps_Compact.accdb"
' Then rename the compacted version back to the original name
Set accApp = Nothing

The CompactRepair method writes the compacted version to a new file — it can't compact in place. Use PAD's file actions to rename the compacted file over the original after the process completes.

Integration with Downstream Systems

Once the Excel file is produced, your flow can hand it off to downstream systems through the cloud layer. A common pattern is to upload the file to SharePoint via a cloud flow action, which then triggers a Power Automate cloud flow that sends the report via email. This keeps file distribution logic in the cloud (where it's easier to manage) and heavy lifting in the desktop flow (where the legacy Access integration lives). For the full picture of multi-application workflows in PAD, see Automating Multi-Application Workflows in Power Automate Desktop: Transferring Data Between Windows Apps, Web Browsers, and Excel in a Single Desktop Flow.

---

Summary & Next Steps

You now have a complete blueprint for automating Microsoft Access reporting in unattended RPA workflows. The key architectural decisions to carry forward:

• COM automation via VBScript or PowerShell is the workhorse for data extraction — fast, headless, and not vulnerable to UI changes
• UI automation fills in where COM can't reach, particularly for formatted report export
• Pre-flight checks for lock files and orphaned processes are non-negotiable in production
• Four-phase structure (Pre-Flight → Extraction → Assembly → Post-Processing) with independent error handlers makes debugging straightforward and keeps each concern separate
• Always test as the service account before scheduling as unattended

The workflow you've built here is a foundation, not a ceiling. Real Access databases tend to have more complex query chains, startup forms with authentication, linked tables over ODBC connections, and access control macros. Each of these adds a wrinkle — but the patterns you've practiced here (pre-flight checks, COM-first with UI fallback, structured error handling) handle each new wrinkle in the same systematic way.

For your next steps, consider how to scale this pattern. If your organization has multiple Access databases requiring similar automation, the subflow structure you've built is already close to reusable — parameterize the database path, query names, and output locations, and you can call the same subflows across different flows. If your Access data eventually needs to land in SQL Server or SharePoint instead of Excel, the extraction phase remains unchanged; only the assembly phase needs a different output target.

For unattended deployment and monitoring at scale, review Managing Machines and Machine Groups for Scalable Unattended Automation in Power Automate and Monitoring and Troubleshooting Desktop Flow Runs at Scale — both are essential reading before you move a bot like this into a production environment where you need to know immediately when something breaks at 5:30 AM.