Others

How to Merge Multiple Microsoft Access Databases Into One

Steve K Oct 8, 2026 869 views

Question details

The user needs to combine data from multiple identical Microsoft Access databases transferred from separate sites via USB into a single master database without causing primary key conflicts.

How to Merge Multiple Microsoft Access Databases Into One

Product: Microsoft Access
Device & OS: not provided
Scenario: Consolidating decentralized, offline Access databases from various sites into a central master database.
Observed behavior: Merging decentralized databases often results in duplicate primary keys and overlapping values, especially when relying on default AutoNumber fields.

Before you start

Always create a secure backup copy of your central master database and the USB site databases before running any append or make-table queries to prevent accidental data loss.

Solution 1Recommended

Use Composite Keys and Append Queries

The most robust method for merging distributed databases is assigning a unique location code to each site and appending data to the master database using a composite primary key.

AutoNumber fields generate sequential numbers starting from 1 in each separate database. When databases operate offline at different sites, these values will overlap during a merge.

By combining a unique site identifier (Location Code) with the local AutoNumber ID, you create a Composite Key. This ensures every record remains globally unique across all databases.

Add a Location Code Field

In each site's database, open the table in Design View and add a new Short Text field named 'LocationCode'. Assign a unique default value (e.g., 'SiteA', 'SiteB') to this field for each specific site.

Create a Composite Primary Key

In Design View, highlight both the 'LocationCode' field and your original 'ID' field. Click the 'Primary Key' button in the ribbon to set both fields together as the primary key.

Link USB Tables to the Master Database

Insert the USB drive, open your master database, and navigate to External Data > New Data Source > From Database > Access. Choose 'Link to the data source by creating a linked table' and select the database on the USB drive.

Run an Append Query

Go to Create > Query Design. Add the linked USB table, change the query type to 'Append', select your master table as the destination, map the fields, and click 'Run' to safely merge the records.

Preventing Key Collisions: Using a composite key completely eliminates AutoNumber collisions, allowing you to merge an unlimited number of decentralized site databases safely.

Solution 2

Combine Tables Using a UNION ALL Query

If you prefer not to change the fundamental table structure, you can use a UNION ALL SQL query to virtually combine the tables by formatting and merging the IDs on the fly.

Free Microsoft Office alternative

Looking for a Lightweight Office Alternative? Try WPS Office

While WPS Office does not include a database management tool like Access, it is a highly efficient, free alternative to Microsoft Office for your daily document, spreadsheet, and presentation needs. If you export your Access data to an Excel format, you can easily manage, clean, and merge your data using WPS Spreadsheet.

1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device.
2. Export Access Data to Excel: In Microsoft Access, export your merged tables to an .xlsx file so it can be handled as a spreadsheet.
3. Open and Edit in WPS Spreadsheet: Launch WPS Office and open the exported .xlsx file to seamlessly analyze and edit your data with full compatibility.

100% compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx)Free, lightweight, and uses minimal system resourcesEasily open and merge exported Access tables (CSV/Excel) in WPS SpreadsheetFamiliar user interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use AutoNumber for merged distributed databases?

AutoNumber fields automatically generate sequential numbers starting from 1 in each separate database. When you attempt to merge databases from different sites into one master table, you will encounter overlapping duplicate IDs, resulting in primary key violations and failed imports.

Can I automate the merging process from a USB drive?

Yes, you can write a VBA (Visual Basic for Applications) procedure in Access that automatically detects the USB drive path, links the site tables, and executes a pre-built append query to merge the data with a single click.

What is a composite key in Microsoft Access?

A composite key is a primary key that consists of two or more fields (such as a 'Location Code' and a local 'ID'). Together, these fields guarantee that every record is uniquely identified across multiple sites, even if the local IDs overlap.

Office Quick Solutions