Archive Tables

[ashwinghanta]

Hello Everyone,

Is there a way to archive or delete tables after executing a Query? And also a way to save the result of this Query? I have made a Query out of 4 tables called report1, I need to update these 4 tables every day so I need to archive the old tables or delete them and save the queries as report1, report2 etc. Is this possible with Access?

 

[Joe4]

So, every time you do this, you are going to be creating a new Reporting table (i.e. Reporting2, Reporting3, Reporting4, ...)?
I don't know if that is such a good idea. Access has a size limitation of 2 GB, and I suspect that you may run into issues if you keep all of the Archive files in Access as Tables.
I would highly recommend exporting the table to an Excel or Text file, if you want to Archive it.

 

[ashwinghanta]

Yeah that's exactly what I need. I need to keep about 30 reporting Tables and I think its less than 2GB . Is there any other work around?

 

[Joe4]

You can use the TransferSpreadsheet or TransferText Actions to export your Reporting Table to a file. If it were me, I would probably do it via VBA instead of a Macro, because then you could make the file names dynamic and use date stamps in the file names to identify them.

Otherwise, if you wanted to stored 30 iterations of the Reporting Table in Access itself, I would create the SQL code you need via VBA, where you can make the Reporting Table name dynamic.

Regardless of what you do, because you are deleting a lot of data, you will need to regularly Compact your database, or else you will exceed the 2 GB limit. When you delete data in Access, it does not reduce the database size until you Compact the database (deleting the data just flags the data for deletion, but doesn't really remove it behind the scenes, even though you cannot see the data from within Access anymore).

 

[ashwinghanta]

If you could help with the SQL code via VBA to make the reporting name Dynamic that would be great. I will then try to compact the database as you have specified.

 

[ashwinghanta]

Or I just got another Idea. Is it possible to import the next 4 CSV files into the same tables but at the next free row of each table? In that case I don't need to Keep Deleting and making new Reporting Tables. I just have these 4 Tables as it is and i keep on adding the data to them.

 

[Joe4]

Or I just got another Idea. Is it possible to import the next 4 CSV files into the same tables but at the next free row of each table? In that case I don't need to Keep Deleting and making new Reporting Tables. I just have these 4 Tables as it is and i keep on adding the data to them.

You don't need to tell it to do anything in that case. It will to that automatically. As long as there are no primary key field violations or other field violations, it will import the records to the current table (imports do not automatically delete or overwrite any existing data).

 

[ashwinghanta]

But in that case how can I keep multiple CSV files with same name in the same folder. I mean how can I use this code to import the upcoming CSV files into the same tables

Option Compare Database
Option Explicit


Function DoImport()


 Dim strPathFile As String
 Dim strFile As String
 Dim strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean


 ' Change this next line to True if the first row in CSV worksheet
 ' has field names
 blnHasFieldNames = True


 ' Replace C:\Documents\ with the real path to the folder that
 ' contains the CSV files
 strPath = "C:\importcsv\"


 ' Replace tablename with the real name of the table into which
 ' the data are to be imported


 strFile = Dir(strPath & "*.csv")
 


 Do While Len(strFile) > 0
       strTable = Left(strFile, Len(strFile) - 4)
       strPathFile = strPath & strFile
       DoCmd.TransferText acImportDelim, "importspec", strTable, strPathFile, blnHasFieldNames




       strFile = Dir()


 Loop




End Function

 

[Joe4]

That code you posted will import any and all CSV files found in the "C:\importcsv\" folder, regardless of what their name is. So they don't all need to have the same name. They can be named whatever, and Access will import them.
After importing them, you will want to move them out of that folder so they are not imported again.

Windows will not allow you to have multiple files with the same name in the same folder. That is a Windows feature, not an Access one.