Add All Records in One Access Table to Another Table that Contains the Same Fields and Others

Tonight's tip concerns VBA code posted to the Dev Center for MS Office at https://msdn.microsoft.com/en-us/library/office/ff834799.aspx . The VBA code runs a SQL INSERT INTO command to insert all of the records in one Access table into another table that must contain all of the same fields, but may contain many other fields. This is particularly helpful when you're trying to merge multiple tables into a master table and there are numerous fields - some of which match, and other that don't.

In this example, we have two tables. All of the fields in the table named, 'Batting' are in the table named, 'BattingPost' which also contains one extra field named, 'Round'.

In a new module in Visual Basic we insert this code listing the path to our database on the line beginning 'Set dbs', and list the table records will be added to after INSERT INTO and the source of the records after FROM.

Option Compare Database

Sub InsertIntoX1() Dim dbs As Database ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("C:\Users\SeanKOShea\Documents\Database8.accdb") ' Select all records in the New Customers table ' and add them to the Customers table. dbs.Execute " INSERT INTO BattingPost " _ & "SELECT * " _ & "FROM [Batting];" dbs.Close End Sub

As you can see we end up with this result - a table containing 19592 records - the sum of the 7902 records in the Batting table and the 11690 records in the BattingPost table. The data from Batting goes into the BattingPost field where the names are the same.