|(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 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
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