Union some tables in a MS access database

Posted on 2011-10-21
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 18

    Expert Comment

    by:Richard Daneke
    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 119

    Expert Comment

    by:Rey Obrero
    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


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

    LVL 44

    Expert Comment

    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 44

    Expert Comment

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

    Accepted Solution

    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)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now