Append tables with all fields using VBA and Wildcards

  • Thread starter Thread starter Jorist
  • Start date Start date
J

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.
 
Jorist said:
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.
Click to expand...


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
 
Thanks Marsh, this helped.



Marshall Barton said:
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
Click to expand...
 
Back
Top