Module: TableCopyDAO in Category Access/Jet Databases : Table and Records from Total Visual SourceBook

Copy table structures (including complex fields) between tables using DAO in VBA and VB6.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modTableCopyDAO module.
CopyTableStructure Procedure Copies the structure of a table to a new table, including all properties and indexes. If the named table already exists in the destination database, this function will not copy the table and returns a value of False. If running in Access, the DoCmd.TransferDatabase command should be used instead. DAO does not allow the creation of certain Access-defined field properties. These include ColumnWidth, ColumnOrder and ColumnHidden. Therefore, this routine may not recreate all the existing property settings in the destination table. After using this function, be sure to check the resulting table for accuracy and any needed manual changes.
CopyTableFields Procedure Copy the field definitions from an existing table to a new one.
CopyTableProperties Procedure Copy the table properties from an existing table to a new one.
CopyIndexes Procedure Copy the index definitions from an existing table to a new one.
CopyLinkedTable Procedure Copies a linked table from one database to another database. This copies the link rather than the actual data.
AddFieldToTable Procedure Adds a field to a table in any Jet database by creating the field object, setting its properties, and appending it to the Fields collection of the specified table.
ChangeFieldType Procedure Changes the data type of the named table field. DAO does not allow you to programmatically change the data type of a field once it exists in a table. To get around this limitation, this procedure uses SQL Data Manipulation Language (DML) queries to create a new temporary field with the specified new data type, copies the data from the original field to the new field, deletes the original field, and renames the new temporary field to the original fields name.
CopyRelationsAll Procedure Copies all relations from one database to another (tables must already exist in the destination database).
CopyRelationsOne Procedure Copies all the relations for one table from one database to another (tables must already exist in the destination database).
' Example of modTableCopyDAO
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_modTableCopyDAO()
  ' Comments: Example of using the modTableCopyDAO module to copy Microsoft Access table structures and properties in VBA and VB6.
  '           See the results in the Immediate Window.

  Const cstrSampleDB As String = "C:\Total Visual SourceBook 2013\Samples\Sample.mdb"
  Const cstrLinkedTable As String = "LinkedTable"
  Const cstrLocalTable As String = "LocalTable"
  Dim strNewTable As String

  ' Example of copying a linked table to another database
  If MsgBox("Are you sure you want to copy linked table'" & cstrLinkedTable & "' to '" & cstrSampleDB & "'?", vbYesNo) = vbYes Then
    Debug.Print "Copying '" & cstrLinkedTable & "' to '" & cstrSampleDB & "'"
    CopyLinkedTable CurrentDb, cstrLinkedTable, cstrSampleDB
  End If

  ' Example of copying a table's structure within the current Microsoft Access database.
  ' To use another database, use substitute CurrentDB with a database pointer to that database.
  strNewTable = cstrLocalTable & "_Copy"
  If MsgBox("Are you sure you want to copy '" & cstrLocalTable & "' to '" & strNewTable & "'?", vbYesNo) = vbYes Then
    Debug.Print "Copying '" & cstrLocalTable & "' to '" & cstrLocalTable & "1'"
    If CopyTableStructure(CurrentDb, cstrLocalTable, strNewTable) Then
      Debug.Print "Table structure copied successfully."
      Debug.Print "Copy failed."
    End If
  End If

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

View all FMS products for Microsoft Access All Our Microsoft Access Products


Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info




Free Product Catalog from FMS