|(Declarations)||Declarations||Declarations and private variables for the modTableCopyDataADO module.|
|CopyTableDataADO||Procedure||Copy all the records from one table to another using ADO. Does not support complex (Attachment and MultiValue) field types, which require using DAO/ACE. The destination table must have all the fields of the source, but the order may be different and it may have additional fields.|
|CopyRecordADO||Procedure||Copy a record from one recordset to another based on field names. The destination table/recordset must have all the fields of the source, but the order may be different and it may have additional fields. Does not support complex (Attachment and MultiValue) field types, which require using DAO/ACE.|
|CopyLargeFieldADO||Procedure||Copy contents of one large field to another due to max 32K chunk.|
|CopyRecordSimpleADO||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.|
' Example of modTableCopyDataADO ' ' 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_modTableCopyDataADO() ' Comments: Example of using the modTableCopyDataADO module to copy Microsoft Access data using ADO in VBA and VB6. ' Assumes Microsoft Access 2007 or later is installed and that you have this sample database installed: Const cstrSampleDB As String = "C:\Total Visual SourceBook 2013\Samples\Sample.accdb" Const cstrTable As String = "Customers" Const cstrTable2 As String = "Copy of Customers" Dim cnn As ADODB.Connection Dim rstSource As ADODB.Recordset Dim rstDest As ADODB.Recordset Dim strError As String Dim fOK As Boolean Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & cstrSampleDB ' Clean up dest table (may be leftover from a previous run) Call EmptyDestTable(cnn, cstrTable2) ' Copy table from one table to another strError = CopyTableDataADO(cnn, cstrTable, cstrTable2) If strError = "" Then Debug.Print "Table data copied successfully to another table" Else Debug.Print "Failed to copy table: " & strError End If ' Example of copying records Call EmptyDestTable(cnn, cstrTable2) Set rstSource = New ADODB.Recordset Set rstDest = New ADODB.Recordset rstSource.Open cstrTable, cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable rstDest.Open cstrTable2, cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable fOK = CopyRecordADO(rstSource, rstDest) Set rstSource = Nothing Set rstDest = Nothing Debug.Print "Record without complex fields duplicated: " & fOK ' Clean up Call EmptyDestTable(cnn, cstrTable2) cnn.Close Set cnn = Nothing End Sub Private Sub EmptyDestTable(cnn As ADODB.Connection, strTable As String) ' Empty table cnn.Execute ("DELETE * FROM [" & strTable & "]") 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