CopyObject

[makewise]

Is there a way to prompt a user for the new table name using the CopyObject in a macro.
We import data every day and need a backup of the table for auditing purposes. We rename the table when copying it using the date as the table name.

Thanks!

 

[mikebaldam]

Auto Abckup of Tables with Date in Names

Using a module you can quite easily...

Create a new Module then insert this bit of Code
(It Doesnt matter what you call the module but something like ModBackUp would be sensible.)

Replace the YOURTABLENAME with your table name i.e. tblProducts
Replace the YOURBACKUPFOLDER with the folder to backup

To call this module from your DB use

Call BackupExcel

on a command button or timed event....
Hope this helps

Public Function BackupExcel()

DoCmd.Hourglass (HourglassOn) 'Show timer working

'1st declare vars and get date
Dim stDate As String
stDate = Format(Now, "yyyymmdd") 'in this order for easy sorting by date

'declare filename variables
Dim stTABLENAME As String

'set up variables filename
stTABLENAME = CurrentProject.Path & "\YOURBACKUPFOLDER\" & stDate & "_TABLENAME" & ".xls"

'export em
DoCmd.OutputTo acTable, "YOURTABLENAME", "MicrosoftExcelBiff5(*.xls)", stTABLENAME, False, "", 0

DoCmd.Hourglass (HourglassOff)
MsgBox "All Details successully backed up to " & CurrentProject.Path & "\YOURBACKUPFOLDER\", vbOKOnly, "Backup Procedure"

Exit Function

DoCmd.Hourglass (HourglassOff)
End Function

Let me know if you have any hassle and Ill post an example