CODEWITHSUNDEEP
excelms-access
Eben
Eben

Reputation: 96

Merge many Access databases to single Excel spreadsheet

I have a folder containing several hundred tables across several hundred Access databases, all with identical column headers. I want to merge them all into a single Excel spreadsheet, ideally not having to do this manually. There are many answers online about merging multiple Excel spreadsheets to a single Access database, but not vice versa. Any suggestions?

Upvotes: 1

Views: 932

Answers (1)

HackSlash
HackSlash

Reputation: 5803

You would need a really large UNION query.

  1. Create a master Access front-end (A DB with no local tables)
  2. Import Links to all tables in all back-ends that you want to include
  3. Write the MONSTER QUERY that contains all the data that you want to export to Excel
  4. Make a data connection in Excel that imports your MONSTER QUERY.

Step 3 is obviously the hard part. A UNION query allows you to join the columns from different tables with identical headers so that they all show up in one resultant table view from the query.

To make this easier on yourself, break it in to parts. Do just two tables from two databases with identical column headers to show yourself that UNION does what you want. Then keep adding to it, one table at a time, until the query is done.

EXAMPLE:

SELECT Company, Address1, Address2, Address3, City, StateProvince, ZipPostalCode,
Country, "Client" AS Type
FROM tblClients
WHERE Country = "U.S.A."

UNION SELECT LeadName, Address1, Address2, "", City, State, Zip, Country,
"Lead" AS Type
FROM tblLeads
WHERE Country = "U.S.A."
ORDER BY ZipPostalCode;

READ THIS:

Create a Query to Combine Data from Two Tables with Similar Structures

https://www.oreilly.com/library/view/access-cookbook/0596000847/ch01s11.html

Upvotes: 1

Related Questions