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: TableCopyDataADO in Category Access/Jet Databases : Table and Records from Total Visual SourceBook

Copy records (excluding complex fields) using ADO in VBA and VB6.

Procedure List

Procedure Name

Type

Description

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

' 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

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