Union some tables in a MS access database

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.

OR
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.

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*"

Hi,

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

Thanks
Ez

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*"));

Select allOpen 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.

This makes the assumption that your target table is named UnionedxxxTable and has the same structure as the xxx tables.