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

' 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!

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)