Appending Tables

[daveboyd]

Hi,

What's the easiest way to append ten tables and simultaneously add a new
field on the new table that records the table the appended records orginated
from?

Thanks from a query/Tabledefs collection beginner!

 

[Michel Walsh]

Hi,


SELECT "table1" as Origin, f1, f2, f3 FROM table1
UNION ALL
SELECT "table2", g1, g2, g3 FROM table2
UNION ALL

....

UNION ALL
SELECT "table10", h1, h2, h3 FROM table3



should do.

It is only a query, not a table. You can append the data to a table (or
create a table ) to add indexes or otherwise make the data updateable, as
example.


Hoping it may help,
Vanderghast, Access MVP

 

[Tom Ellison]

Dear Dave:

Appending each table is a separate task. Adding a new column in a table is
a separate task. There is no such thing as simultaneous. One step at a
time.

Tom Ellison

 

[daveboyd]

Okay, I think I'll try to add the table name field and metadata to each table
table record before doing the union.

Thanks for the responses!

 

[david.a.boyd]

Here's a complete bottom line answer in Access:

Option Compare Database
Option Explicit

Sub Sheets2Query()
Dim e As Excel.Application
Set e = CreateObject("excel.application")
e.Workbooks.Open ("xlAsPoorMansDatabase.xls")
Dim w As Excel.Workbook
Set w = e.ActiveWorkbook
Dim s As Excel.Worksheet
For Each s In w.Worksheets
DoCmd.TransferSpreadsheet acImport, , s.Name,
"xlAsPoorMansDatabase.xls", True
Next
' Add Metadata
Call add_Names
' That's one big query (table/recordset)!
Call union_Tables
e.Quit
Set e = Nothing
End Sub

Sub add_Names()
Dim db As Database
Set db = CurrentDb

Dim t As TableDef

For Each t In db.TableDefs
If (InStr(t.Name, "MSys") = 0) Then
If (InStr(t.Name, "~") = 0) Then
MsgBox (t.Name)
t.Fields.Append t.CreateField("SheetName", dbText)
Dim rs As Recordset
Set rs = db.OpenRecordset(t.Name)
rs.MoveFirst
Do Until rs.EOF
rs.Edit
rs!sheetname = t.Name
rs.Update
rs.MoveNext
Loop
End If
End If
Next
End Sub

Sub union_Tables()
Dim db As Database
Set db = CurrentDb

Dim td As TableDefs
Set td = db.TableDefs

Dim t As TableDef
Dim s As String
s = "SELECT * FROM "

Dim u As String
Dim q As String
q = ""
u = ""

For Each t In td
If (InStr(t.Name, "MSys") = 0) Then
If (InStr(t.Name, "~") = 0) Then
q = q + u + s + t.Name
If u = "" Then u = " UNION ALL "
End If
End If
Next

Dim rs As Recordset
Set rs = db.OpenRecordset(q + ";")

' Do something like a simple, readable report
' (to do -- use Excel/Word for viewable/editable report
' -- ironic, no?, but Excel is used for everything)
' for now, a simple check
rs.MoveFirst
MsgBox rs!sheetname
rs.MoveLast
MsgBox rs!sheetname
rs.Close
Set rs = Nothing


End Sub

Example data might look like:

xlAsPoorMansDatabase.xls

sheet Ones

a b c
1 2 3
four five six
7 8 9
0

sheet Tens

a b c
eleven twelve thirteen
14 15 16
17 18 19
10

sheet Twentys
a b c
21 22 23
24 twenty-five 12/26/2006
27 28 29
20

You get the idea -- note: the first row consists of the "field" names.
Assumption is that all tables in Excel have the same "header" row

1) Paste the example data into three sheets in an Excel file named
xlAsPoorMansDatabase.xls
2) Paste the code into Access and run Sheets2Query
3) The query could be saved as a new table (not quite sure how to do
this!), but is perfectly useable as a recordset as it stands.


Dave