Custom Search

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.

' Create a new table and load data by using ProductID and ProductName column from Products table.
CurrentProject.Connection.Execute "SELECT ProductID, ProductName INTO Products_Copy FROM Products"

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 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.

' Create a new table from an existing table Products. It duplicates both its structure
' and data. Note that the DestinationDatabase argument is left blank to
' indicate that the new table is created within the current database.

DoCmd.CopyObject , "Products_Copy", acTable, "Products"

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.

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "Products", "Products_Copy", StructureOnly:=True

For detailed explanation of TransferDatabase method, please refer to this Microsoft page

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...

Happy Duplicating!


Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.How to create a composite unique index (not as a primary key) in MS Access
10.Use VBA to get the correct number of records in a Recordset object
11.Disable Access Prompt when a record is changed, table deleted, or action queries run
12.How to hide and unhide a MS Access object
13.How to return multiple values from a VBA function (Part 3)
14.How to return multiple values from a VBA function (Part 2)
15.How to return multiple values from a VBA function (Part 1)
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

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.

Home | Feedback | Terms of Use | Privacy Policy