October 3, 2011
When you add new records to an Access table and also want to add those records to another table in the same database, there is a fast way to do it without using a lot of code. Here is the way:
- First, create a table specifically for adding new record basics. Give it an obvious name like “AddNewRecords”. This table should only contain the fields that are in all of the tables you plan to update, such as ID Number, FirstName, and LastName. (There should not be many matching fields between the different tables, except these.)
- Open the new table in Design View and make sure that the field sizes and data types match those in the tables you are going to update.
- Now go to the Datasheet View and add the new records.
- Save the table and close it.
- Click on the Create tab and in the Other section select Query Design.
- Choose Simple Query and from the Tables/Queries box select the table you just created.
- Send all of the fields over by clicking on the double arrows, and then hit Next.
- In the next view, select Modify the Query Design and Finish.
- Hit Datasheet view to verify the new records, and then go back to DesignView.
- Now comes the fun! Up in the Query Type section, select the Append button.
- Select the first table to which you want to append new records.
- Make sure that all of the fields will be added. If, for instance, the ID field is not included in the Append to: row, click on the arrow in the cell where it should appear and select the field that matches the ID field in the AddNewRecords.
- Now, go back up to the ribbon and click Run. The new records have been added to the first table.
- Go back to the Design tab, and repeat steps 10 – 13 to add the new records to the second table. Do this for all of the tables which will be updated. (Be sure that you select the correct ID field for each one.)
- When you are done, save and close the query. Now you can go to your forms and start adding all of the unique data to each record, without having to spend time adding those records to each table.
- When you get ready to add more new records to the database, open the AddNewRecords table, and delete the names that are there so you can add the new ones. Repeat steps 3 – 15.