Combine multiple tables into one

[Travis]

I took over a database in which the last person created a new table for
everyday worth of data (ie. Feb-21-08 is named 022108). There are over 100
tables in the single database with the same columns and table attributes, I
would like to combine all of these tables into one where I have a Date_Key
field in place of the table name. I figured out how to query the table names
using the following:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY
MSysObjects.Name;

Now I just need to figure out a way to have a macro populate the master
table using the table query to select the tables I want to combine. Any
ideas?

Thank you for your help.

 

[John W. Vinson]

I took over a database in which the last person created a new table for
everyday worth of data (ie. Feb-21-08 is named 022108). There are over 100
tables in the single database with the same columns and table attributes, I
would like to combine all of these tables into one where I have a Date_Key
field in place of the table name. I figured out how to query the table names
using the following:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY
MSysObjects.Name;

Now I just need to figure out a way to have a macro populate the master
table using the table query to select the tables I want to combine. Any
ideas?

I wouldn't do it with a macro, but you could do so using a VBA code loop.
Assuming that you want to select only tables whose names contain six digits,
and that Date_Key is a Text field that you want to contain the table name, try
this: create a query TablesToMerge using

SELECT [Name] FROM MsysObjects WHERE (MSysObjects.Type)=1 AND [Name] LIKE
"######" ORDER BY [Name];

Then you could use VBA code like the following:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error GoTo Proc_Error
Set db = CurrentDb
Set rs = db.Openrecordset("TablesToMerge", dbOpenDynaset)
Do Until rs.EOF
strSQL = "INSERT INTO NewTablename(DATE_KEY, field1, field2, ..., fieldn)" _
& " SELECT '" & rs![Name] & "', field1, field2, ..., fieldn" _
& " FROM [" & rs![Name] & "];"
db.Execute strSQL, dbFailOnError
rs.MoveNext
Loop
Proc_Exit:
Exit Sub
Proc_Error:
<error handling code here>
Resume Proc_Exit
End Sub

You'll need to use your tables' actual fieldnames of course... do this ON A
BACKED UP database obviously!!!

 

[Steve Schapel]

Good idea, Travis!

However, as this involves a looping operation, it would be quite awkward
for a macro. Using a VBA procedure would be smoother. Here is a
skeleton suggestion (test on backup!!)...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dt As Date
Dim strSQL As String
Dim strAPP As String
Set dbs = CurrentDb
strSQL = "SELECT MSysObjects.[Name]" & _
" FROM MSysObjects"
" WHERE (Left$([Name],1)<>"~")" & _
" AND (Left$([Name],4) <> "Msys")" & _
" AND (MSysObjects.Type)=1" & _
" ORDER BY MSysObjects.Name"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .OEF
dt = CDate(Left(![Name],2) & "/" & Mid(![Name],3,2) & "/" &
Right([Name],2))
strAPP = "INSERT INTO MasterTable ( Date_Key, SomeField,
AnotherField, etc )" & )
" SELECT #" & dt & "#, SomeField, Another Field etc" & _
" FROM " & ![Name]
dbs.Execute strAPP, dbFailOnError
Loop
.Close
End With
Set rst = Nothing
Set dbs = Nothing

 

[Steve Schapel]

.... forget the MoveNext in the code! Correction:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dt As Date
Dim strSQL As String
Dim strAPP As String
Set dbs = CurrentDb
strSQL = "SELECT MSysObjects.[Name]" & _
" FROM MSysObjects"
" WHERE (Left$([Name],1)<>"~")" & _
" AND (Left$([Name],4) <> "Msys")" & _
" AND (MSysObjects.Type)=1" & _
" ORDER BY MSysObjects.Name"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .OEF
dt = CDate(Left(![Name],2) & "/" & Mid(![Name],3,2) & "/" &
Right([Name],2))
strAPP = "INSERT INTO MasterTable ( Date_Key, SomeField,
AnotherField, etc )" & )
" SELECT #" & dt & "#, SomeField, Another Field etc" & _
" FROM " & ![Name]
dbs.Execute strAPP, dbFailOnError
.MoveNext
Loop
.Close
End With
Set rst = Nothing
Set dbs = Nothing

 

[Travis]

Steve,

Does it matter that I'm running Access 2000? I'm getting compile errors on
the "Dim dbs as DAO.Database" line. Do I need to load a particular library
to run this code? I'm not a novice, however this code is a little more
complicated than my skill set. Am I over my head?

Travis

 

[John W. Vinson]

Does it matter that I'm running Access 2000? I'm getting compile errors on
the "Dim dbs as DAO.Database" line. Do I need to load a particular library
to run this code?

Yes, and yes: 2000 defaulted to use the ADO object library rather than DAO.
Select Tools... References; scroll down the list to find Microsoft DAO x.xx
(the highest version if there are more than one), and check it.

 

[Travis]

Thank you, John.

This works like I had hoped. You and Steve have given me great help and
insight, not to mention saved me countless hours of time.

I have one more question. Can you explain this logic to me?

Do Until .OEF

Thank you again.

Travis

 

[Steve Schapel]

Travis,

EOF means "end of file" and is a property of a recordset. So we are
telling it to keep doing the routine for each row in the recordset,
until it gets to the end.

 

[John W. Vinson]

EOF means "end of file" and is a property of a recordset. So we are
telling it to keep doing the routine for each row in the recordset,
until it gets to the end.

"Start at the beginning, go on until you reach the end, and then stop."
- Lewis Carroll in Through The Looking Glass