troubleshooting Question

MS Access Archive tables

Avatar of DAVIDFLINT
DAVIDFLINT asked on
Microsoft AccessSQL
2 Comments1 Solution224 ViewsLast Modified:
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?

Thanks


 




 
ASKER CERTIFIED SOLUTION
DAVIDFLINT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros