Microsoft Access, Office/VBA, VB6 Modules royalty-free source code library

Microsoft Access Modules royalty-free source code library

Microsoft Access VBA and VB6 Module Library

Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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

Copy table structures (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 modTableCopyDAO module.
CopyTableStructure	Procedure	Copies the structure of a table to a new table, including all properties and indexes. If the named table already exists in the destination database, this function will not copy the table and returns a value of False. If running in Access, the DoCmd.TransferDatabase command should be used instead. DAO does not allow the creation of certain Access-defined field properties. These include ColumnWidth, ColumnOrder and ColumnHidden. Therefore, this routine may not recreate all the existing property settings in the destination table. After using this function, be sure to check the resulting table for accuracy and any needed manual changes.
CopyTableFields	Procedure	Copy the field definitions from an existing table to a new one.
CopyTableProperties	Procedure	Copy the table properties from an existing table to a new one.
CopyIndexes	Procedure	Copy the index definitions from an existing table to a new one.
CopyLinkedTable	Procedure	Copies a linked table from one database to another database. This copies the link rather than the actual data.
AddFieldToTable	Procedure	Adds a field to a table in any Jet database by creating the field object, setting its properties, and appending it to the Fields collection of the specified table.
ChangeFieldType	Procedure	Changes the data type of the named table field. DAO does not allow you to programmatically change the data type of a field once it exists in a table. To get around this limitation, this procedure uses SQL Data Manipulation Language (DML) queries to create a new temporary field with the specified new data type, copies the data from the original field to the new field, deletes the original field, and renames the new temporary field to the original fields name.
CopyRelationsAll	Procedure	Copies all relations from one database to another (tables must already exist in the destination database).
CopyRelationsOne	Procedure	Copies all the relations for one table from one database to another (tables must already exist in the destination database).

Example Code for Using Module: TableCopyDAO

' Example of modTableCopyDAO
'
' 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_modTableCopyDAO()
  ' Comments: Example of using the modTableCopyDAO module to copy Microsoft Access table structures and properties in VBA and VB6.
  '           See the results in the Immediate Window.

  Const cstrSampleDB As String = "C:\Total Visual SourceBook 2013\Samples\Sample.mdb"
  Const cstrLinkedTable As String = "LinkedTable"
  Const cstrLocalTable As String = "LocalTable"
  Dim strNewTable As String

  ' Example of copying a linked table to another database
  If MsgBox("Are you sure you want to copy linked table'" & cstrLinkedTable & "' to '" & cstrSampleDB & "'?", vbYesNo) = vbYes Then
    Debug.Print "Copying '" & cstrLinkedTable & "' to '" & cstrSampleDB & "'"
    CopyLinkedTable CurrentDb, cstrLinkedTable, cstrSampleDB
  End If

  ' Example of copying a table's structure within the current Microsoft Access database.
  ' To use another database, use substitute CurrentDB with a database pointer to that database.
  strNewTable = cstrLocalTable & "_Copy"
  If MsgBox("Are you sure you want to copy '" & cstrLocalTable & "' to '" & strNewTable & "'?", vbYesNo) = vbYes Then
    Debug.Print "Copying '" & cstrLocalTable & "' to '" & cstrLocalTable & "1'"
    If CopyTableStructure(CurrentDb, cstrLocalTable, strNewTable) Then
      Debug.Print "Table structure copied successfully."
    Else
      Debug.Print "Copy failed."
    End If
  End If

End Sub

Overview 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.

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!