Merge Tables

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Is is possible to merge some 100 small tables into one
table, without having to create one make table query, and
then appending all the others in an append query.

The tables al be gin with the same word OUTFUT then a
date, so it would be good if it was possible to do it in
code like outfut* ......hope this makes sense...and I
could just be being lazy! but since I will have to do
this often it would be good if I could somehow automate
the procedure.

Cheers
 
Mike,

The following sub will do it, provided:
1. All tables share the same design
2. The tagret table alreasy exists - so you'll have to create it manually
before you first run it
3. You change "Target Table Name" to the actual name.

Sub Append_Tables()
TargetTable = "Target Table Name"
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 6) = "OUTFUT" Then
SourceTable = tbl.Name
strSQL = "INSERT INTO [" & TargetTable & "]"
strSQL = strSQL & " SELECT " & SourceTable & ".*"
strSQL = strSQL & " FROM " & SourceTable
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
Next
End Sub

HTH,
Nikos
 
Doesn't seem to be working, I defined variable
TargetTable as Object not sure if that is right but it
says tbl variable not defined......not all that good with
VBA at the mo....still learning!
-----Original Message-----
Mike,

The following sub will do it, provided:
1. All tables share the same design
2. The tagret table alreasy exists - so you'll have to create it manually
before you first run it
3. You change "Target Table Name" to the actual name.

Sub Append_Tables()
TargetTable = "Target Table Name"
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 6) = "OUTFUT" Then
SourceTable = tbl.Name
strSQL = "INSERT INTO [" & TargetTable & "]"
strSQL = strSQL & " SELECT " & SourceTable & ".*"
strSQL = strSQL & " FROM " & SourceTable
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
Next
End Sub

HTH,
Nikos

Is is possible to merge some 100 small tables into one
table, without having to create one make table query, and
then appending all the others in an append query.

The tables al be gin with the same word OUTFUT then a
date, so it would be good if it was possible to do it in
code like outfut* ......hope this makes sense...and I
could just be being lazy! but since I will have to do
this often it would be good if I could somehow automate
the procedure.

Cheers
Click to expand...


.
Click to expand...
 
Mike,

You probably have "Require Variable Declaration" checked in your VBE
options. Add the following declarations at the beginning:

Dim TargetTable As String
Dim tbl As TableDef

TargetTable should be declared as a string, not an object! It's just used to
hold the table name to integrate it in the sql string.

The other thing you need to check is if you have DAO in your references
(Tools > References from the VBE window), which this code requires. If not,
add it (Microsoft DAO 3.51 for A97, Microsoft DAO 3.6 for A2K or later). The
code does work, I have tested it (in A2K).

Nikos

Mike said:
Doesn't seem to be working, I defined variable
TargetTable as Object not sure if that is right but it
says tbl variable not defined......not all that good with
VBA at the mo....still learning!
-----Original Message-----
Mike,

The following sub will do it, provided:
1. All tables share the same design
2. The tagret table alreasy exists - so you'll have to create it manually
before you first run it
3. You change "Target Table Name" to the actual name.

Sub Append_Tables()
TargetTable = "Target Table Name"
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 6) = "OUTFUT" Then
SourceTable = tbl.Name
strSQL = "INSERT INTO [" & TargetTable & "]"
strSQL = strSQL & " SELECT " & SourceTable & ".*"
strSQL = strSQL & " FROM " & SourceTable
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
Next
End Sub

HTH,
Nikos

Is is possible to merge some 100 small tables into one
table, without having to create one make table query, and
then appending all the others in an append query.

The tables al be gin with the same word OUTFUT then a
date, so it would be good if it was possible to do it in
code like outfut* ......hope this makes sense...and I
could just be being lazy! but since I will have to do
this often it would be good if I could somehow automate
the procedure.

Cheers
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top