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?

 

[sheetspread]

Try a make-table, append, or update query

 

[Joe4]

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?

I also find it better to delete the data out of the table instead of deleting the whole table. Then you still have the table structure the way you want it and don't need to recreate it every time (which can get a little messy if you want key fields and indexes).

You can do this easily with a simple Macro that uses the RunSQL action, and just runs one-line of SQL code that deletes all records, like this:

Delete * From Table1;

 

[ashwinghanta]

I have imported 4 CSV files into 4 new tables and I have the following SQL Query code which produces a result into a new table called Reporting.

SELECT WebLife_Statistik.Antragsnummer, [Vs_annahme_police.LfdNummer] & [Vs_annahme_police.Sparte] & [Vs_annahme_police.KdNummer] AS VersicherungsNr, [WebLife_Statistik.Datum] & "-" & [WebLife_Statistik.UZeit] AS [Ausgang Datenstrom WebLife], [ZW_Testdaten.Datum] & "-" & [ZW_Testdaten.Zeit] AS [Eingang Datenstrom Z-Workflow], [Vs_anlage_schwebe.Datum] & "-" & [Vs_anlage_schwebe.Zeit] AS [Anlage Schwebe VSL], [Vs_annahme_police.Datum] & "-" & [Vs_annahme_police.Zeit] AS [Policierung VSL] INTO Reporting
FROM ((WebLife_Statistik INNER JOIN Vs_anlage_schwebe ON WebLife_Statistik.Antragsnummer = Vs_anlage_schwebe.Antragsnummer) INNER JOIN Vs_annahme_police ON Vs_anlage_schwebe.Antragsnummer = Vs_annahme_police.Antragsnummer) INNER JOIN ZW_Testdaten ON Vs_annahme_police.Antragsnummer = ZW_Testdaten.Antragsnummer;

The names of these 4 tables are ZW_Testdaten, Weblife_Statistik, Vs_annahme_police and Vs_anlage_schwebe. How do I Delete them and keep the Reporting table? As I have to reimport the CSV files at regular intervals. Is there any code that I have to add?

 

[Joe4]

As I mentioned you can create a macro, and run the RunSQL Acton four times, one for each table with the line of code like:

[B]DELETE * FROM ZW_Testdaten, Weblife_Statistik;[/B]

As long as you don't delete the data from the Reporting table, it will stay there. Just delete it from the other four tables.

 

[ashwinghanta]

Now it works! Thanks. Although I have another question I am relatively new to SQL and I don't know if there is a possibility to Update Reporting Table for every CSV import? Can I set a Variable value like Reporting(i) i.e for every CSV import can I have a new Reporting table automatically without manually changing the code everytime?

 

[Joe4]

a possibility to Update Reporting Table for every CSV import? Can I set a Variable value like Reporting(i) i.e for every CSV import can I have a new Reporting table automatically without manually changing the code everytime?

How is the Reporting table updated? If it is updated by an Action Query, just run the OpenQuery Action on the Action Query, and it will run the query/update the Table.

 

[ashwinghanta]

The Reporting table is a Make Table Query which is made out of the 4 Tables. Now when I delete these 4 tables and Import a 4 new CSV files as 4 new tables how can I Run this Query to make a new Reporting2 Table? How can I update this code so that it gives me a new Reporting Table for every time CSV Import.?

SELECT WebLife_Statistik.Antragsnummer, [WebLife_Statistik.Datum] & "-" & [WebLife_Statistik.Zeit] AS [Ausgang Datenstrom WebLife], [ZW_Testdaten.Datum] & "-" & [ZW_Testdaten.Zeit] AS [Eingang Datenstrom Z-Workflow], [Vs_anlage_schwebe.Datum] & "-" & [Vs_anlage_schwebe.Zeit] AS [Anlage Schwebe VSL], [Vs_annahme_police.Datum] & "-" & [Vs_annahme_police.Zeit] AS [Policierung VSL] INTO Reporting
FROM ((WebLife_Statistik INNER JOIN Vs_anlage_schwebe ON WebLife_Statistik.Antragsnummer = Vs_anlage_schwebe.Antragsnummer) INNER JOIN Vs_annahme_police ON Vs_anlage_schwebe.Antragsnummer = Vs_annahme_police.Antragsnummer) INNER JOIN ZW_Testdaten ON Vs_annahme_police.Antragsnummer = ZW_Testdaten.Antragsnummer;

 

[Joe4]

Just run the Make Table Query after you have imported all 4 files.
If you want to do it in a Macro or VBA, just use the OpenQuery Action to run that particular Make Table Query.
See: Create a Microsoft Access Macro that Opens/Runs a Query | Database Solutions for Microsoft Access | databasedev.co.uk
and Ways to call Action Queries - UtterAccess Forums

 

[ashwinghanta]

Ok but it overwrites the Reporting Table. I would like to have something like Reporting2. Any other ideas?