How to use VBA code to duplicate an existing table in MS Access.

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.
Copy an Access table.

Paste to make a new table.
Paste an Access table.

Name the new table Products_Copy.
Name an Access table.

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


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

Confirm to replace an Access table.

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

Reference ADO and DAO library in Access.

Other Recent Articles from the MS Access category:

1.	Examples of MS Access DateDiff function used in query and VBA code
2.	MS Access DateDiff function
3.	How to find out your computer name and username by VBA
4.	Examples of MS Access DatePart function
5.	MS Access DatePart function
6.	Examples of MS Access DateAdd function
7.	MS Access DateAdd function
8.	IIF function basics - the CASE statement of MS Access
9.	MS Access Date Expression
10.	Solved: MS Access error "The text is too long to be edited"
11.	Create MS Access Combo Box essential properties by VBA code
12.	Create MS Access Combo Box essential properties manually
13.	How to do text search in MS Access programmatically
14.	Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
15.	How to easily get a list of field names in MS Access
16.	How to count distinct records in MS Access
17.	How to do transaction based processing in MS Access
18.	How to open a document (local/network file or web page) from MS Access
19.	How to use ADOX to create unique composite index - the VBA approach
20.	How to do cross-table update queries in MS Access - the right way
21.	Three efficient ways to get the number of records by using VBA
22.	How to create a composite unique index (not as a primary key) in MS Access
23.	Use VBA to get the correct number of records in a Recordset object
24.	Disable Access Prompt when a record is changed, table deleted, or action queries run
25.	How to hide and unhide a MS Access object
26.	How to return multiple values from a VBA function (Part 3)
27.	How to return multiple values from a VBA function (Part 2)
28.	How to return multiple values from a VBA function (Part 1)
29.	Create a DLL by CSharp or VB.Net for VBA
30.	How to correctly reference and call a DLL
31.	How to register a C# or VB.Net DLL
32.	Email address validation by Regular Expressions using VBA
33.	Fix MS Access error: Query must have at least one destination field
34.	How to unselect radio buttons in MS Access after it has been selected
35.	How to Change Query Timeout Value for MS Access SQL Queries
36.	What is Northwind Traders database

	Custom Search