Three ways to programmatically duplicate a table in MS Access by VBA
Duplicating a table in Access manually is extremely easy - just Copy and then Paste the table object. The three screenshots below copy Products table, and then paste it to make a new table Products_Copy.
Copy table Products.
Paste to make a new table.
Name the new table Products_Copy.
Sometimes manually duplicating a table is not an option when you have to automate some tasks in Access. In this case, you may need to write code to duplicate a table on the fly. For example, when you perform ETL (Extract, Transform, Load) process inside Access, you will need some staging tables to hold data for temporary use. When a staging table needs to have the same structure as an existing table, this can only be accomplished by writing some VBA code to do what you can manually do by using Copy and Paste.
Basically, there are 3 ways to duplicate a table in Access by using VBA code and there are pros and cons for each.
1. Use Make Table query.
Make Table query is a SELECT ... INTO... SQL statement which can create a new table by using the structure and data from existing table(s). The advantage of using make-table query is that it's flexible for us to join or union with a few tables to create an amalgamated table.
Below is the simplest way to execute a Make Table query in VBA code. Remember to reference ADO library in VBA.
2. Use DAO's CopyObject method of the DoCmd object.
The CopyObject method carries out the CopyObject action in Visual Basic. For the usage of CopyObject method, please refer to the help page on Microsoft website here http://msdn.microsoft.com/en-us/library/ff844724.aspx Disadvantage of this method is that it also copies data into the new table, but sometimes the only thing you need in the new table is the table structure of the existing table. Note that if you only need table structure, you can execute a DELETE query to delete all records afterwards.
Below is the code snippet to create a copy of Products table.
Note that, if you already got a table Products_Copy in your database, you'll be asked to confirm.
3. Use DAO's TransferDatabase method of the DoCmd object.
This is probably the best way to duplicate a table because you have the option to specify if you need to duplicate table structure and/or data into the new table.
Below is the code snippet to create a copy of Products table for its table structure only. Note that we duplicate the table within the same database by using CurrentDb.Name in the code.
For detailed explanation of TransferDatabase method, please refer to this Microsoft page http://msdn.microsoft.com/en-us/library/bb214131%28v=office.12%29.aspx
If you already got a table Products_Copy in your database, you'll NOT be asked to confirm the deletion.
On the above linked Microsoft web page, it clearly specifies that, if you import a table from another Access database (or the same Access database) where the imported that is a linked table in that database, it will still be linked after you import it. That is, the link is imported, not the table itself. So there is no way that you can use TransferDatabase (or the CopyObject method, I tested) to import a linked table to make it become a local table. Note that, you can manually copy a linked table and then paste it, and you'll be asked if you want the pasted table to be a linked table or a local table. If it's a local table, the primary key and indexes will remain as well.
Add Library Reference
To use the VBA code listed on this page, you need to add References in your Access VBA for ADO and DAO library for all the code above to work.
Open your Access database and open Visual Basic code editor. In the menu cross the top, click Tools -> References...
Copyright © 2013 GeeksEngine.com. All Rights Reserved.
This website is hosted by LunarPages.
No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.