Using Acess 2000

  • Thread starter Thread starter Bridget
  • Start date Start date
B

Bridget

I am having a huge problem. I have created a table for
work orders. I have also created a status field on this
table. I am looking to move records, once the status has
been changed to completed, from the active table to a
history table. I have been told I should be using macros
for this however I have no idea how to use them and
haven't found any help in books I've looked through.
Does anyone have a suggestion or can anyone tell me if
this is even possible?
 
I am having a huge problem. I have created a table for
work orders. I have also created a status field on this
table. I am looking to move records, once the status has
been changed to completed, from the active table to a
history table. I have been told I should be using macros
for this however I have no idea how to use them and
haven't found any help in books I've looked through.
Does anyone have a suggestion or can anyone tell me if
this is even possible?
Click to expand...

Ummm... WHY?

You can index the Status field and create Queries to select just the
active records or just the inactive records. Moving the data will
*very rarely* be necessary. If you have a *large* (meaning over a
million records) table it might be.

If so, it is not necessary to use a Macro. Instead, you would use two
Queries: an Append query (selecting the records with the desired
status value) and appending those records to the history table; and
then a Delete query to remove them from the production table. You can
(if you wish) call these two queries from a Macro, but it would be
better to do so from a VBA function, since you could improve safety
and reliability by using a transaction. Post back if you're interested
in seeing sample code.
 
Reason being that we are working with a system where
possibly in the next few years the work order numbers
will start to repeat themselves.. obviously I can change
the numbers in the system so that they can't be
duplicated but they are worried about two things... the
fact that the numbers WILL be repeating themselves and
the fact that the database will become to big to hold in
memory.
**********************************************************
 
Reason being that we are working with a system where
possibly in the next few years the work order numbers
will start to repeat themselves.. obviously I can change
the numbers in the system so that they can't be
duplicated but they are worried about two things... the
fact that the numbers WILL be repeating themselves and
the fact that the database will become to big to hold in
memory.
Click to expand...

If it were my business, I'd change the workorder numbers. Someday
you'll have WO 312 in both the production database and the archive -
and not too long after that you'll have WO 312 in the archive TWICE.
It's going to be a pain figuring out which is which!

That said... I'd run the Append and Delete queries, and am still
willing to post the code to do so, if you'ld post a few more details
about tablenames and such so my example fits your data.
 
Back
Top