|(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
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!
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"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