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

' 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

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

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)