• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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