We help IT Professionals succeed at work.
Get Started

MS Access Archive tables

Last Modified: 2013-11-27
Sorry, quite hard to explain this. I have an Access application that contains a table called RMCosts. Every fortnight we update all tables in this application from our ERP system. I've been asked to retain an archive of the previous 24 RMCosts in another access file which I've managed to do OK. The code I wrote simply runs Create table SQL that effectively copies the current RMCosts to the other Access file and datestamps it (RMCostsToYYYMMDD) before creating the new RMCosts file in the live application. A new requirement has been given to me. I now have to include the last 24 RMCosts in a huge select query. Easy enough to do manually by attaching the last 24 RMCosts tables and pulling in the relevant fields. Doing this manually though makes it more likely that it will be overlooked or mistakes will be made.

I'd much rather find a way of getting the query to work out the that it should obtain RMCost-1 (2 weeks ago) from table RMCostsTo20080703, RMCost-2 (4 weeks ago) from table RMCostsTo20080620 etc etc. Expecting that this wasn't likely to be easy (or even possible) I considered writing a simple routine to manage tables called RMCosts-1, RMCosts-2 thru 24. It goes something like this in my head.

On update

1. CopyObject RMCosts-23 to RMCosts-24, 22-23, 21-22 etc.
2. Copy RMCosts to RMCost-1 and RMCostsToYYYYMMDD
3. Create RMCosts from ERP as before.

I know how to do this within the existing application and the effect would hopefully be to have 48 tables - 24 datestamped and 24 with names that represent ageing i.e. -1 to -24. I could then connect my select query to tables RMCost-1 to RMCosts-24 and run without the need to edit first.

This may not be the best approach in any case but I could make it work. That said I can't live with the tables in the application from which I run the update. The tables are very large and the application is issued via email. I need to create the and manage the naming of these 48 tables in an external mdb. I don't see how I'm going to be able to do that with commands like CopyObject

Does anyone have a good way of doing this?



Watch Question
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE