Append tables with all fields using VBA and Wildcards

[Jorist]

Does anyone have a VBA script that they would be willing to share that
appends all fields from Table A into Table B without using Access Append
Queries? These tables will always have the same fields and structures.

I am trying to get this accomplished with wildcards and without having to
hard code every field into the script as I have MANY tables to update.

Any help would be greatly appreciated.

 

[Marshall Barton]

Does anyone have a VBA script that they would be willing to share that
appends all fields from Table A into Table B without using Access Append
Queries? These tables will always have the same fields and structures.

I am trying to get this accomplished with wildcards and without having to
hard code every field into the script as I have MANY tables to update.

An Append (INSERT INTO) query is the best way. The trick is
to construct the query properly before executing it.

If you have a table (or array) with the names of the tables
you want to archive, then you can use code along these
lines:

Set rs = db.OpenRecordset("tableoftables",dbOpenSnapshot)
Do Until .EOF
strSQL = "INSERT INTO " & rs!TableName & "* " _
& "SELECT " & rs!TableName & "* " _
& "FROM & rs!TableName"
db.Execute strSQL, dbFailOnError
rs.MoveNext
Loop

 

[Jorist]

Thanks Marsh, this helped.

An Append (INSERT INTO) query is the best way. The trick is
to construct the query properly before executing it.

If you have a table (or array) with the names of the tables
you want to archive, then you can use code along these
lines:

Set rs = db.OpenRecordset("tableoftables",dbOpenSnapshot)
Do Until .EOF
strSQL = "INSERT INTO " & rs!TableName & "* " _
& "SELECT " & rs!TableName & "* " _
& "FROM & rs!TableName"
db.Execute strSQL, dbFailOnError
rs.MoveNext
Loop