Project Perfect
PROJECT  PERFECT
                 Project Management Software
                          Specialists in Project Infrastructure
Microsoft Access Development Tips

Home - Microsoft Access Development - Microsoft Access Tips

Sample Microsoft Access VBA

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 String

On Error GoTo Error_subCreateTableCopy

Set dbs = CurrentDb

strTempTable = "temp" & strTableName ' Create name temptblOldTableName

' 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

 

Return to the top