• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

Copy table before Importing new table

Experts, before I import "Import_FC.xlsm", I first need to copy the tblImport_FC as it resides in the db, but I would like to append the current date to the end of the tbl name.  
ie tblImport_FC ==>tblImport_FC_161102

How can I modify the below to first copy the tblImport_FC as append the date as described?

Private Sub cmdImport_Click()

         Dim strName As String
         Dim xl As Object
            Set xl = CreateObject("Excel.Application")
            xl.Workbooks.Open ("C:\Users\johnsoat\Desktop\Import_FC.xlsm")
            xl.Run "Transpose"
            xl.ActiveWorkbook.Close (True)
            xl.Quit
            Set xl = Nothing
            CurrentDb.Execute "delete * from [Import_FC]"
            strName = "Transpose"

        DoCmd.TransferSpreadsheet acImport, , "Import_FC", "C:\Users\johnsoat\Desktop\Import_FC.xlsm", True, strName & "!"


End Sub

Open in new window

0
pdvsa
Asked:
pdvsa
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try
Private Sub cmdImport_Click()

         Dim strName As String
         Dim xl As Object
            Set xl = CreateObject("Excel.Application")
            xl.Workbooks.Open ("C:\Users\johnsoat\Desktop\Import_FC.xlsm")
            xl.Run "Transpose"
            xl.ActiveWorkbook.Close (True)
            xl.Quit
            Set xl = Nothing
            CurrentDb.Execute  "select * into  [Import_FC_" & format(date,"yyMMdd") & "] from [Import_FC]"
            CurrentDb.Execute "delete * from [Import_FC]"
            strName = "Transpose"

        DoCmd.TransferSpreadsheet acImport, , "Import_FC", "C:\Users\johnsoat\Desktop\Import_FC.xlsm", True, strName & "!"


End Sub

Open in new window

Regards
0
 
Dale FyeCommented:
Why do you feel the need to create multiple tables in your database with identical structure and different names?  Is this for some archive purpose?  If so, you might want to consider a single archive table that includes all of the other field of the original table and an ArchiveDate.  Then you can simply write an append query to append all of the current records to that Archive table with the current date added.  This would rapidly grow, but would give you the ability to quickly compare "similar" records across time as well as restore from the Archive if it is necessary.

Just my 2 cents!

Dale
0
 
pdvsaProject financeAuthor Commented:
Dale that is a brilliant idea.  I am going to close this question and award pts Rgonzo because his solution works and i might need it later.  Any objections let me know
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now