MS Access VBA Copy Table for versions 2000, 2002, 2003, 2007

Go here To Check if Table exists in VBA.
Go here To use VBA to get all Tables.
Follow the Update Field in VBA Link to update records in a table.
Follow the Delete a Table with VBA Link to delete a Table in VBA.

The VBA Copy Table Sample is available for download here in a zipped MS Access Database.

The following Access VBA Copy Table code snippet will use VBA to Make a Table within the current database and append the date to the new Table's name.

Please be aware that when you create a new table with the following function that the Field index will start at 1 and not 0, should you reference the new table’s fields in VBA.
Thanks goes to Jan Lichenbelt for pointing this out to me.

You should be able to copy/paste the below code right into a Module.

Function CopyTable( ByVal TableName As String ) As Boolean

   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   ' Creates a copy of a Table with a given TableName in the database and Appends
   ' the date to the new Table's Name
   ' Accepts
   ' TableName: Name of table to copy
   ' Returns True on success, false otherwise
   'USAGE: CopyTable "TableName"

   On Error GoTo errhandler

   Dim Db As DAO.Database
   Dim strSQL As String

   Set Db = CurrentDb()

   'Create the Make Table SQL Code from our string
   strSQL = "SELECT " & TableName & ".* INTO " & TableName & "_" & Format(Date, "mmddyyyy" ) & _
         " FROM " & TableName & ";"

   'Print the SQL so we can paste into the query builder if there are errors
   Debug.Print strSQL

   'Run the SQL
   Db.Execute strSQL

   'If no errors return true
   CopyTable = True

ExitHere:

   Set Db = Nothing
   'Notify the user the process is complete.
   MsgBox "Copy Table Complete"
   Exit Function

errhandler:
   'There is an error return false
   CopyTable = False

   With Err
      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "CopyTable"
   End With

   Resume ExitHere

End Function

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.

For further MS Access tutorials go here: MS Access tutorials

Did you find what you were looking for? What would you suggest? mail@eraserve.com


Your Name:
Your Message:
Your Email:

Custom Search