Total Visual SourceBook

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 2007 and VB6

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

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"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

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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 List

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 Code for Using Module: TableCopyDataDAO

' 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

Microsoft Access Module LibraryOverview of 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.

Additional Resources