How-To Copy a Table’s Structure Only Without The Data

Here’s an interesting question that I was asked today:

I want yo copy only structure, empty table without data?

We immediately think of CopyObject method, but sadly, Microsoft never included the option to include/omit the data. So it always includes the data. Yes, you could use it non the less and then clear the table of the data afterwards, but this will cause unnecessary bloating and require a Compact and Repair to complete the process properly. It remains an option, but there are better approaches, so let’s briefly explore a couple of them.
 
Table of Contents

 

Manually Copy/Pasting Tables

Let us not overlook the good old manual copy/paste!

Whether you use the:

  • Keyboard keystrokes Ctrl+c/Ctrl+v
  • Ribbon’s Copy/Paste command buttons
  • Right-Click context menu Copy/Paste commands

These all permit a quick duplication of an existing table. The added bonus of this approach is that if will pop-up a dialog to offer you the option to copy the Structure Only or Structure and Data.

For one off situations, this is probably the quickest and simplest solution. Sometimes the simplest solution is the best.
 

Using SQL/Query

Local Tables

If you wish to perform your duplication via an SQL Statement/Query, it can be done. We could simply create an Access query with an SQL Statement such as:

SELECT [sSrcTableName].* INTO [sDestTableName]
FROM [sSrcTableName] 
WHERE 1=0;

Obviously, replacing the terms sSrcTableName, sDestTableName with your table names.

Linked Tables

Of course we can duplicate a linked table. To do so we can once again turn to simple SQL.

For an unencrypted back-end

SELECT * INTO [sDestTableName]
FROM [MS Access;DATABASE=C:\...\...\YourDatabase.accdb].[sSrcTableName]
WHERE 1=0;

For an encrypted back-end

SELECT * INTO [sDestTableName]
FROM [MS Access;PWD=YourBEPassword;DATABASE=C:\...\...\YourDatabase.accdb].[sSrcTableName]
WHERE 1=0;

Note, this will creates the table in the database running the command.

These commands can also easily be turned into reusable VBA procedures so you have more tools in your arsenal. Wink, wink.
 

Using VBA

TransferDatabase – Built-In Method

Probably the simplest solution would be to use the TransferDatabase method to Import the table into the database itself.

So the code would look like:

Docmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "ExistingTableName", "NewTableName", True

 

Custom VBA Procedure – Using SQL

If we want, we could also easily create simple code to automate the SQL Statement/Query approach. So we could create something like:

'---------------------------------------------------------------------------------------
' Procedure : DuplicateTableStructure
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Duplicate a table's structure only, no data
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSrcTableName     : Existing table name to be duplicated
' sDestTableName    : Name to give the new table
'
' Usage:
' ~~~~~~
' Call DuplicateTableStructure("ExistingTableName", "NewTableName")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2007-03-03              Initial Release
' 2         2024-10-09              Updated Header
'                                   Updated Error Handler
'---------------------------------------------------------------------------------------
Function DuplicateTableStructure(sSrcTableName As String, _
                                 sDestTableName As String)
On Error GoTo Error_Handler
    Dim oDb                   As DAO.Database
    Dim sSQL                  As String

    Set oDb = CurrentDb
    sSQL = "SELECT [" & sSrcTableName & "].* INTO [" & sDestTableName & "]"
    sSQL = sSQL & " FROM [" & sSrcTableName & "] WHERE 1=0;"
    oDb.Execute sSQL, dbFailOnError

    oDb.TableDefs.Refresh
    Application.RefreshDatabaseWindow    'Update the Nav Pane
    
Error_Handler_Exit:
    On Error Resume Next
    Set oDb = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: DuplicateTableStructure" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

and then you simply call it be doing:

Call DuplicateTableStructure("ExistingTableName", "NewTableName")

 

Custom VBA Procedure – Using CopyObject

For the sake of completeness, I thought I’d still provide an example using the CopyObject method even though, as mentioned above, I wouldn’t normally recommend this approach because of possible bloating it might cause.

'---------------------------------------------------------------------------------------
' Procedure : Table_DuplicateStructure
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Duplicate a table's structure only, no data
'               ***** In general not a good aproach because of bloating *****
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSrcTableName     : Existing table name to be duplicated
' sDestTableName    : Name to give the new table
'
' Usage:
' ~~~~~~
' Call Table_DuplicateStructure("ExistingTableName", "NewTableName")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2024-10-09              Initial Release
'---------------------------------------------------------------------------------------
Public Sub Table_DuplicateStructure(sSrcTableName As String, _
                                 sDestTableName As String)
On Error GoTo Error_Handler

    'Duplicate the table, this will include the data however
    DoCmd.CopyObject , sDestTableName, acTable, sSrcTableName
    'Now delete all the data
    CurrentDb.Execute "DELETE FROM [" & sDestTableName & "];", dbFailOnError
    'You should now compact the database.
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Table_DuplicateStructure" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

and then you simply call it be doing:

Call Table_DuplicateStructure("ExistingTableName", "NewTableName")

 

Indexes!

I overlooked one important aspect of copying tables, that is their Indexes. Thankfully a reader, Xavier Batle, was kind enough to raise the issue.

It is important to note that if you use direct SQL or the DuplicateTableStructure() Function which employs SQL to perform the copy, although the fields and properties are all preserved, they do in fact lose all of the table/field indexes!

On the other hand, using the CopyObject method, TransferDatabase method and/or manually copy/pasting tables retains the table/field Indexes.
 
 
So there you have it, a few different approaches you can employ to duplicate a table’s structure only.
 

Page History

Date Summary of Changes
2024-10-09 Initial Release
2024-10-12 Added the Manually Copy/Pasting Tables section to the article.
Added the Indexes! section to the article.
2024-10-13 Reorganized the article into a more logical flow.
Added the Table of Contents for easier navigation.

2 responses on “How-To Copy a Table’s Structure Only Without The Data”

  2. Hansjörg Truttmann

    Hi,
    In my projects, I use VBA scripts to keep backend data structures in sync with new frontend features. It involves copying a whole template tables (empty tables). Until recently, I used this:
    DoCmd.CopyObject strBackendDirAndPath, pstrTablenameFrom, acTable, pstrTablenameTo

    However, with recent changes by MS, in many cases Access no longer allows this unless the backend directory is explicitly marked as a trusted location.

    Instead I use

    DoCmd.TransferDatabase _
    TransferType:=acExport, _
    DatabaseType:=”Microsoft Access”, _
    DatabaseName:=strBackendDirAndFilename, _
    ObjectType:=acTable, _
    Source:=strTabelleFrom _
    Destination:=strTabelleTo, _
    StructureOnly:=true

    Best regards,
    Hansjörg