Appending Tables in Access

[ChiTownDiva]

Afternoon All...

I was wondering if there was a way to append a number of tables based on, say the table extension (i.e. _txt would be an extension) to a "master" table with out writing individual queries to accomplish this.

An example:

Odom_txt
sacra_txt
phil_txt

Master EDI Feed

The tables with "_txt" extension are the tables I would want to append to the "Master EDI Feed" table. The number and names of the tables in the database would vary, otherwise I would simply write queries and then create a macro.

Is there a way to accomplish this programmably?

Thanks in advance for any assistance with this.

ChiTownDivaus

 

[Ahliana]

There are two ways you could go about this. You'd have to test to see which was faster, or which was easier for you to maintain.

First, you could set up a function to create a recordset based on "SELECT Name FROM MSysObjects WHERE Type = 6 and Name LIKE "*_txt". Walk the recordset and dynamically write a long UNION query that combines them all, then append that to your master table. Although I have not written out the code for this, it would mean that it would not have to be updated if the table structure changes. Since you are using a UNION query, they all have to match, but you don't need to worry about it beyond that.

That may actually be the easier route. If you haven't looked at the MSysObjects table, go to Tools, Options, View, System Objects. You don't need to show this to query it.

I have, in years past, used MSysObjects for many things, and I rather like it. I have, however, had a UNION query that should work (with subqueries) but bombed Access.

Having the list of tables as a recordset can make life easier.



Another way to iterate through tables is something like the following. You walk through the objects, and if it is a table, you put that tablename into the append query. The query is not really written for you, it's just pseudo-code at that point. If you need further help there, let me know, I'll help.

Public Function fungfTableAppend() As Boolean

On Error GoTo Err_Trap

'Set the return value to false in case we bomb
    fungfTableAppend = False
    
'Declarations
    Dim db As Database
    Dim con As Container

'Set the database
    Set db = CurrentDb()

'Iterate through forms in containers
    For Each con In db.Containers
        If con.Name = "TABLES" Then
                For Each doc In con.Documents
                    Debug.Print , doc.Name
                    DoCmd.RunSQL "INSERT INTO [Master EDI Feed] FROM " & con.Name       'Fixme - fix this statement to work for you.
                Next    'doc
        End If  'con.name=all
    Next    'con

'Housecleaning
    Set con = Nothing
    db.Close
    Set db = Nothing

'Set the return value true because we completed succesfully
    fungfTableAppend = True

Exit Function

Err_Trap:
    MsgBox prompt:=Err.Number & vbCrLf & Err.Description & vbCrLf & "Error occurred in fungfTableAppend."
    Resume Next
    Resume

End Function

Ahliana
Argue for your limitations and, sure enough, they're yours! - Richard Bach

 

[PeteJohnston]

This cycles thru all of the tables in the current database and uses SQL to INSERT the records into the main table.

On Error Resume Next

Dim tbl As TableDef, tables As TableDefs
Dim strSQL As String
    
Set tables = CurrentDb.TableDefs
For Each tbl In tables
 If Right(tbl.Name, 4) = "_txt" Then
   strSQL = "INSERT INTO MasterTable " & _          "(field1, field2, field3)" & _
       "SELECT field1, field2, field3 " & _         "FROM " & tbl.Name
   CurrentDb.Execute strSQL
   If Err Then
     MsgBox "Error: " & Err.Number & _
        "Description: " & Err.Description
   End If
 End If
Next
    
MsgBox "All done!"

The problem would be the SQL syntax. Unless all of the tables had the same field names you would have to find some way to map the names in the _txt tables to those in the MasterTable. If they are the same it only needs the (fairly) simple INSERT.

ps. I know the SQL syntax is wrong but I don't have time to fix it right now.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[ChiTownDiva]

Thanks Pete...

I'm getting a 3134Description: Syntax Error message.

I can't see where the error is in the query:

Public Function AppendTables()

On Error Resume Next

Dim tbl As TableDef, tables As TableDefs
Dim strSQL As String

Set tables = CurrentDb.TableDefs
For Each tbl In tables
If Right(tbl.Name, 4) = "_txt" Then

strSQL = "INSERT INTO Master EDI Feed Org, Bottler, [FS/TS], Division, Pkg, [Beverage Prod], [Delivery Pt], Address, City, State, Zip, [Acct ID], [Delivery Pt ID], [Year/Month], [Year], [Date], Volume" & _
"SELECT Org, Bottler, [FS/TS], Division, Pkg, [Beverage Prod], [Delivery Pt], Address, City, State, Zip, [Acct ID], [Delivery Pt ID], [Year/Month], [Year], [Date], Volume " & _
"FROM " & tbl.Name

CurrentDb.Execute strSQL
If Err Then
MsgBox "Error: " & Err.Number & _
"Description: " & Err.Description
End If
End If
Next

End Function

It looks like every other INSERT TO statement I've seen or written. Can anybody see anything?

Thanks...

 

[PaulF]

try adding a space between Volume" and "Select

PaulF

 

[ChiTownDiva]

Thanks PaulF...

Here's the new code...

strSQL = "INSERT INTO MASTER EDI FEED DATA (Org, Bottler, [FS/TS], Division, Pkg, [Beverage Prod], [Delivery Pt], Address, City, State, Zip, [Acct ID], [Delivery Pt ID], [Year/Month], [Year], [Date], Volume) " & _
"SELECT Org, Bottler, [FS/TS], Division, Pkg, [Beverage Prod], [Delivery Pt], Address, City, State, Zip, [Acct ID], [Delivery Pt ID], [Year/Month], [Year], [Date], Volume" & _
"FROM " & tbl.Name

Still doesn't work...

 

[Ahliana]

You need straight brackets [] around any identifiers with spaces, such as the table name MASTER EDI FEED DATA. I generally recommend against spaces or special characters in identifiers (names of things). I suggest underscores instead. Hope it helps.

Ahliana
Argue for your limitations and, sure enough, they're yours! - Richard Bach

 

[ChiTownDiva]

Thanks Ahliana...

Tried that and it still doesn't work...

 

[PHV]

And this ?
strSQL = "INSERT INTO [MASTER EDI FEED DATA] (Org, Bottler, [FS/TS], Division, Pkg, [Beverage Prod], [Delivery Pt], Address, City, State, Zip, [Acct ID], [Delivery Pt ID], [Year/Month], [Year], [Date], Volume) " & _
"SELECT Org, Bottler, [FS/TS], Division, Pkg, [Beverage Prod], [Delivery Pt], Address, City, State, Zip, [Acct ID], [Delivery Pt ID], [Year/Month], [Year], [Date], Volume" & _
"[highlight] [/highlight]FROM " & tbl.Name



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244