Programmatically Archive Access Backend Tables

2
votes

I have an Access application that is now split into front end and back end databases. I would like the user to periodically archive the data from the back end tables to improve the database performance. What I have in mind is the following:

  1. Create a new empty database file
  2. Create empty copies of selected tables from original backend database
  3. Insert data from original backend database to the new database tables with certain criteria, for example date range
  4. Delete from original backend database tables the archived data

Can I achieve this programmatically so the user only needs to perform simple actions like clicking a button and input the data range?

I am using Access 2003 for my application.

Thanks in advance for the help.

1
sqldatabasems-accessvbaarchive
Yes, those steps can all be accomplished with VBA code and initiated by the click event of a command button. Which is the first step you need help with? – HansUp
Thanks @HansUp I need help with step 2 and 3. how do I create empty copies of tables in the new database? And how do I query from two different databases? – got2nosth
I think you may be able to use SELECT INTO statements for this. SELECT * INTO Table1 IN 'D:\SomeFolder\BackUp.mdb' FROM Table1; It creates the destination table and loads the data into it as one operation. So, if that is satisfactory, it accomplishes your steps 2 and 3. – HansUp
@HansUp Thanks, this works great! I didn't know it could be that simple. Another question, is there an efficient way for me to loop through my backend tables skipping those temp and system tables? – got2nosth
@HansUp I also got a problem when copying columns of Boolean values over. It seems the TRUE value becomes -1 and FALSE value becomes 0 in my new table. How can I resolve this? – got2nosth

1 Answers

0
votes

Concerning your Boolean field, if it bothers you seeing the -1 and 0, just open the table in design view, select your Boolean field, then in the properties for 'Format', just either double-click to scroll thru the options or click the drop-down arrow to select the desired format.
It will then display as either 'True/False' or 'Yes/No' or 'On/Off'