Append Tables

[Guest]

I have a piece of code which has imported hundreds of tables into access.
They have been created with filename now() 1, now () 2, now 3 and now()4 (ie
date stamped as filename with numbers 1 to 4 added). I now want to append all
these temporary tables into four existing permanent tables. Lets call them
Table1, Table2, Table3 and Table4. I don't think I can use an append query as
the filename is never the same. Could someone please help me code a loop to
append these tables? I have searched on newsgroups and google etc but not
found anything that will help me yet. I think I need to utilise the
docmd.runsql insert into maybe but I don't know how to handle the temp table
names. All temp tables ending in 1 want to go to the same table, 2 to
another, etc.

Can anyone please help? I am trying to learn more... ;-)

Thanks in advance for any help.
Sue

 

[John Spencer (MVP)]

Try some code like the following. TEST it on a COPY of your database. This is
UNTESTED code.

Public Sub AddDataToTables()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim tblAny As DAO.TableDef

Set dbAny = CurrentDb()

For Each tblAny In dbAny.TableDefs
IF Instr(1,tblAny.Name,"PermanentTable") <> 1 Then
Select Case Right(tblAny.Name, 1)
Case "1", "2", "3", "4"
strSQL = "INSERT INTO [PermanentTable]" & Right(tblAny.Name, 1) & "
SELECT * FROM [" & tblAny.Name & "]"
dbAny.Execute strSQL
Case Else
'Do nothing
End Select
End IF
Next tblAny
End Sub

Assumptions:
Temp tables all end in 1,2,3, or 4 and
permanent tables all have same name except for number at end

TEST on a copy.

Did I menton TEST on a COPY

 

[Guest]

Thanks for this, most appreciated. 'permanent' table names are all different
though and don't end in a number - sorry if my post made you think they
were... I guess I could rename them though just to do this? I'll test it
out... don't worry I will use a copy ;-)

Thanks, Sue

Try some code like the following. TEST it on a COPY of your database. This is
UNTESTED code.

Public Sub AddDataToTables()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim tblAny As DAO.TableDef

Set dbAny = CurrentDb()

For Each tblAny In dbAny.TableDefs
IF Instr(1,tblAny.Name,"PermanentTable") <> 1 Then
Select Case Right(tblAny.Name, 1)
Case "1", "2", "3", "4"
strSQL = "INSERT INTO [PermanentTable]" & Right(tblAny.Name, 1) & "
SELECT * FROM [" & tblAny.Name & "]"
dbAny.Execute strSQL
Case Else
'Do nothing
End Select
End IF
Next tblAny
End Sub

Assumptions:
Temp tables all end in 1,2,3, or 4 and
permanent tables all have same name except for number at end

TEST on a copy.

Did I menton TEST on a COPY

I have a piece of code which has imported hundreds of tables into access.
They have been created with filename now() 1, now () 2, now 3 and now()4 (ie
date stamped as filename with numbers 1 to 4 added). I now want to append all
these temporary tables into four existing permanent tables. Lets call them
Table1, Table2, Table3 and Table4. I don't think I can use an append query as
the filename is never the same. Could someone please help me code a loop to
append these tables? I have searched on newsgroups and google etc but not
found anything that will help me yet. I think I need to utilise the
docmd.runsql insert into maybe but I don't know how to handle the temp table
names. All temp tables ending in 1 want to go to the same table, 2 to
another, etc.

Can anyone please help? I am trying to learn more... ;-)

Thanks in advance for any help.
Sue

 

[John Spencer (MVP)]

Well, you could change the Case Statement to
Case "1"
strSQL = "INSERT INTO [NameOfFirstPermanentTable] SELECT * FROM ["...
...
Case "2"
strSQL = "INSERT INTO [NameOf2ndPermanentTable] SELECT * FROM ["...
...
Case "3"
...

Thanks for this, most appreciated. 'permanent' table names are all different
though and don't end in a number - sorry if my post made you think they
were... I guess I could rename them though just to do this? I'll test it
out... don't worry I will use a copy ;-)

Thanks, Sue

Try some code like the following. TEST it on a COPY of your database. This is
UNTESTED code.

Public Sub AddDataToTables()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim tblAny As DAO.TableDef

Set dbAny = CurrentDb()

For Each tblAny In dbAny.TableDefs
IF Instr(1,tblAny.Name,"PermanentTable") <> 1 Then
Select Case Right(tblAny.Name, 1)
Case "1", "2", "3", "4"
strSQL = "INSERT INTO [PermanentTable]" & Right(tblAny.Name, 1) & "
SELECT * FROM [" & tblAny.Name & "]"
dbAny.Execute strSQL
Case Else
'Do nothing
End Select
End IF
Next tblAny
End Sub

Assumptions:
Temp tables all end in 1,2,3, or 4 and
permanent tables all have same name except for number at end

TEST on a copy.

Did I menton TEST on a COPY

I have a piece of code which has imported hundreds of tables into access.
They have been created with filename now() 1, now () 2, now 3 and now()4 (ie
date stamped as filename with numbers 1 to 4 added). I now want to append all
these temporary tables into four existing permanent tables. Lets call them
Table1, Table2, Table3 and Table4. I don't think I can use an append query as
the filename is never the same. Could someone please help me code a loop to
append these tables? I have searched on newsgroups and google etc but not
found anything that will help me yet. I think I need to utilise the
docmd.runsql insert into maybe but I don't know how to handle the temp table
names. All temp tables ending in 1 want to go to the same table, 2 to
another, etc.

Can anyone please help? I am trying to learn more... ;-)

Thanks in advance for any help.
Sue

 

[Guest]

John
Thanks very much for this!! It sorted out my problem for me. I didn't use
the instr part though as I wasn't sure why this was required. All imported
and appended successfully now .
Thanks again
Sue

Well, you could change the Case Statement to
Case "1"
strSQL = "INSERT INTO [NameOfFirstPermanentTable] SELECT * FROM ["...
...
Case "2"
strSQL = "INSERT INTO [NameOf2ndPermanentTable] SELECT * FROM ["...
...
Case "3"
...

Thanks for this, most appreciated. 'permanent' table names are all different
though and don't end in a number - sorry if my post made you think they
were... I guess I could rename them though just to do this? I'll test it
out... don't worry I will use a copy ;-)

Thanks, Sue

Try some code like the following. TEST it on a COPY of your database. This is
UNTESTED code.

Public Sub AddDataToTables()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim tblAny As DAO.TableDef

Set dbAny = CurrentDb()

For Each tblAny In dbAny.TableDefs
IF Instr(1,tblAny.Name,"PermanentTable") <> 1 Then
Select Case Right(tblAny.Name, 1)
Case "1", "2", "3", "4"
strSQL = "INSERT INTO [PermanentTable]" & Right(tblAny.Name, 1) & "
SELECT * FROM [" & tblAny.Name & "]"
dbAny.Execute strSQL
Case Else
'Do nothing
End Select
End IF
Next tblAny
End Sub

Assumptions:
Temp tables all end in 1,2,3, or 4 and
permanent tables all have same name except for number at end

TEST on a copy.

Did I menton TEST on a COPY

hughess7 wrote:

I have a piece of code which has imported hundreds of tables into access.
They have been created with filename now() 1, now () 2, now 3 and now()4 (ie
date stamped as filename with numbers 1 to 4 added). I now want to append all
these temporary tables into four existing permanent tables. Lets call them
Table1, Table2, Table3 and Table4. I don't think I can use an append query as
the filename is never the same. Could someone please help me code a loop to
append these tables? I have searched on newsgroups and google etc but not
found anything that will help me yet. I think I need to utilise the
docmd.runsql insert into maybe but I don't know how to handle the temp table
names. All temp tables ending in 1 want to go to the same table, 2 to
another, etc.

Can anyone please help? I am trying to learn more... ;-)

Thanks in advance for any help.
Sue