NEWBIE - VBA Append/Delete Tables

[Jorist]

I have a database for which data is imported on a quarterly basis. The
original table names are not always the same, but the imported tables take
the names of the original tables and add a 1 behind the name.

I would like to append all data from all fields into TableNameA1 to
TableNameA and then Delete TableNameA1 and then this for every table in the
database.

Any help would be appreciated. Thanks.

 

[George Nicholson]

I would create one append query for every pair of tables you work with. Its
simply easier than dealing with all the SQL code (which would require
specific field names for each table). A set of queries is also easier to
maintain than miles of code.

Give the the queries a consistent name that encorporates the table name like
"qryAppendTo_tblAccounts". Then you could easily do something like this.

For Each tdf in CurrentDB.TableDefs
If Right(tdf.Name,1) = "1" Then
'Table name ends in a 1, so we want to run the related query
strTableName = Left(tdfName, Len(tdf.Name)-1)
CurrentDB.Execute "qryAppendTo_" & strTableName, dbFailOnError
DoEvents
End If
Next

For i = CurrentDB.TableDefs.Count to 1 Step -1
' Work backwards when deleting multiple items from a collection.
If Right(tdf.Name,1) = "1" Then
'Table name ends in a 1, so we want to delete the table
CurrentDB.TableDefs.Delete tdf.Name
DoEvents
End If
Next

I would not try to combine the loops into one. Much safer to leave all of
the deletions as a separate 2nd step.

 

[Jorist]

Thanks Gorge. This has helped give me a place to start.