Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Union some tables in a MS access database

Posted on 2011-10-21
Medium Priority
Last Modified: 2012-05-12

I need to union some of the tables in a given MS Access database (.mdb file). Trick is, I do not know how many tables are there in the database but I do know that, names of all the tables that are needed to be appended together, all start with 'xxx' as the first three character and all have same structure..

Question by:ezsas
LVL 19

Expert Comment

by:Richard Daneke
ID: 37008591
You can create a union query SQL in access and run it from the navigation pane or by macro or code.

You can create a number of Append Queries and run them from the navigation pane or by macro or code.

If it is a one time issue, I would simply copy and paste the records into one large table.  Remember to highlight only the actual table records(and not the * or new record row), choose Copy, then select the new record row in the receiving table before you choose Paste.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37008613
a query needs to explicitly specify the source Domai (Table Or Query) so, you need to know the names of the table before you could build your Union query.

however, it is still possible to build the union query using VBA, but i rather not do it this way..

to get the names of the tables you need, run this query against the msysobjects table

select [name] from msysobjects where [name] like "xxx*"

Author Comment

ID: 37008658

If it is not complicated by macro. I like to know how do I create one. Else, I would look for alternative.

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 46

Expert Comment

ID: 37010124
You can do this without a macro or VBA code.

1. Run this query

SELECT "dbEngine(0)(0).Execute " & "'INSERT INTO UnionedxxxTable SELECT " & msysobjects.[name] & ".* FROM " & msysobjects.[name] & "'"
 AS AppendToUnionTable
FROM msysobjects
WHERE (((msysobjects.Name) Like "xxx*"));

Open in new window

2. Select all the output rows.
3. Copy the selected rows to the clipboard
4. Open Notebook
5. Paste the rows into Notebook
6. Replace ' with "

Note: this replaces apostrophe characters with quote characters.

7. Select all but the first line (AppendToUnionTable)
8. Copy to the clipboard
9. Open the Immediate window (Ctrl+G)
10. Paste the rows
11. Starting with the first line, press the enter key until you have executed all lines.
LVL 46

Expert Comment

ID: 37010130
This makes the assumption that your target table is named UnionedxxxTable and has the same structure as the xxx tables.
LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 37010153
If you create a module and create a routine, like below, then you will be able to append to your union table no matter what xxx tables you have.

Public Function AppendToUnionTable()
    Dim rs As Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("select [name] from msysobjects where [name] like " & Chr(34) & "xxx*" & Chr(34))
    Do Until rs.EOF
        DBEngine(0)(0).Execute "INSERT INTO UnionedxxxTable SELECT " & rs!Name & ".* FROM " & rs!Name
End Function

Open in new window

Such a function could be invoked from a command button on a form, from a macro, from the Immediate window, or inside the routine (F5 key or Run menu or Run toolbar button)

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question