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

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

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modTableCopyDataDAO module.
CopyTableDataDAO Procedure Copy all the records from one table to another using DAO. Supports ACCDB complex field types. The destination table must have all the fields of the source, but the order may be different and it may have additional fields.
CopyRecordComplexDAO Procedure Copy a record from one recordset to another based on field names including complex field types. The destination table/recordset must have all the fields of the source, but the order may be different and it may have additional fields.
CopyComplexFieldDAO Procedure Copy the values in a complex field from one field to another.
CopyLargeFieldDAO Procedure Copy contents of one large field to another due to max 32K chunk.
CopyRecordSimpleDAO Procedure Copy a record from one recordset to another based on field names and no complex (multivalue or attachment fields) or large fields (>32K). The destination table/recordset must have all the fields of the source, but the order may be different and it may have additional fields.
CopyTableDataSimpleDAO Procedure Copies the records from a table from one database to another using a query. This does not support complex fields such as multivalue and attachment fields.
' Example of modTableCopyDataDAO
'
' 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)

'           This example assumes that you have Microsoft Access and that you have this sample database installed:

Private Const mcstrSampleDB As String = "C:\Total Visual SourceBook 2013\Samples\Sample.accdb"
Private Const mcstrTableComplex As String = "Customers-Complex"
Private Const mcstrTableComplex2 As String = "Copy of Customers-Complex"
Private Const mcstrTableSimple As String = "Customers"
Private Const mcstrTableSimple2 As String = "Copy of Customers"

Private mdbs As DAO.Database

Private Sub Example_modTableCopyDataDAO()
  ' Comments: Example of using the modTableCopyDataDAO module to copy data between Microsoft Access tables by record in VBA and VB6.

  Dim rstSource As DAO.Recordset
  Dim rstDest As DAO.Recordset
  Dim fOK As Boolean
  Dim strError As String

  Set mdbs = DAO.OpenDatabase(mcstrSampleDB)

  ' Initialize by emptying sample destination tables
  EmptyDestTables

  ' Example of CopyTableDataDAO
  fOK = (CopyTableDataDAO(mdbs, mcstrTableComplex, mcstrTableComplex2) = "")
  Debug.Print "Table data duplicated: " & fOK
  EmptyDestTables

  ' Copy records from one recordset to another including complex fields
  Set rstSource = mdbs.OpenRecordset(mcstrTableComplex)
  Set rstDest = mdbs.OpenRecordset(mcstrTableComplex2)
  strError = CopyRecordComplexDAO(rstSource, rstDest)
  Set rstSource = Nothing
  Set rstDest = Nothing
  If strError = "" Then
    Debug.Print "Record with complex fields duplicated successfully"
  Else
    Debug.Print "Failed to copy record with complex fields: " & strError
  End If
  EmptyDestTables

  ' Copy data from one recordset to another when there are no complex fields or large fields (> 32K)
  Set rstSource = mdbs.OpenRecordset(mcstrTableSimple)
  Set rstDest = mdbs.OpenRecordset(mcstrTableSimple2)
  fOK = CopyRecordSimpleDAO(rstSource, rstDest)
  Set rstSource = Nothing
  Set rstDest = Nothing
  Debug.Print "Record without complex fields duplicated: " & fOK
  EmptyDestTables

  ' Copy specific records from one table to another
  ' This example shows copying data from one table to another in the same database, but this can also copy data to another table
  fOK = CopyTableDataSimpleDAO(mdbs, mcstrTableSimple, "", mcstrTableSimple2, "")
  Debug.Print "Simple table records copied: " & fOK
  EmptyDestTables

  ' Clean up database object
  mdbs.Close
  Set mdbs = Nothing

End Sub

Private Sub EmptyDestTables()
  ' Comments: Empty destination tables

  mdbs.Execute "DELETE * FROM [" & mcstrTableComplex2 & "]"
  Debug.Print mdbs.RecordsAffected & " records deleted from " & mcstrTableComplex2

  mdbs.Execute "DELETE * FROM [" & mcstrTableSimple2 & "]"
  Debug.Print mdbs.RecordsAffected & " records deleted from " & mcstrTableSimple2

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

Reviews

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

Question

 

 

Free Product Catalog from FMS