Access Tips – Creating a temporary copy of a Table

The following are some samples of VBA code that may prove useful.

If you need to create a copy of a table for any reason, pass the table name to this public function. If the table were something like tblNames, the call would be

subCreateTableCopy (“tblNames”)

Public Sub subCreateTableCopy(strTableName As String)
‘ Creates a temporary copy of a table with the prefix ‘temp’

Dim dbs As Database
Dim tdf As TableDef
Dim strTempTable As StringOn Error GoTo Error_subCreateTableCopy

Set dbs = CurrentDb

‘ Create name temptblOldTableName
strTempTable = “temp” & strTableName

‘ See if the table exists and delete it
For Each tdf In dbs.TableDefs

If tdf.Name = strTempTable Then

DoCmd.DeleteObject acTable, strTempTable

GoTo DeletedTable

End If

Next

‘ Create a copy of the table before update
DeletedTable:

DoCmd.CopyObject , strTempTable, acTable, strTableName ‘ Create the copy

Exit_subCreateTableCopy:

Exit Sub

Error_subCreateTableCopy:

MsgBox “Error in subCreateTableCopy ” & Err.Number & ” – ” & Err.Description
Resume Exit_subCreateTableCopy

End Sub