union 120 tables in Access (easy way?)

  • Thread starter Thread starter izi
  • Start date Start date
I

izi

I need to combine 120 tables in access.
1) is there an easier way to do this?
2) can access handle it
3) what happens if i remove a table among the 120 tables. will data
automaticly be updated.

*all tables have same fields
 
120 tables will not be doable with a union query.

The simplest method would be to choose one table as the master and then append
the remaining 119 tables into the master table.

You could probably write a VBA routine to do the appends. Are there other
tables in the database or are there just the 120. If there are just 120
tables, then it should be possible to write some VBA that would loop through
the tables and append them all to one master table.

Something like this UNTESTED VBA code.

Public Function fAppendAll()
Dim DbAny as DAO.Database
Dim tdef as TableDef
Dim StrSQL as String

StrSQL = "INSERT Into MasterTable SELECT * FROM "
Set dbAny = currentDb()
For Each Tdef in dbany.TableDefs
if tdef.Name <> "MasterTable" and _
Not(Tdef.Name Like "Msys*") Then
dbany.execute StrSQL & "[" & tdef.Name & "]"
End If
Next tDef
End Function

Sorry, gotta go. Appointment to pick up grandkids. Good luck
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Why? As in "why do you have 120 tables [with] all [the] same fields?"

That doesnt' sound like a relational database, that sounds like a
spreadsheet.

"How" you do things in Access depends on "what" -- what data you are
keeping, what data structure you are using, what you want to accomplish.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top