how to best update/archive old data back-end of database

[rmulder]

ok, so it's been a long time since i made this database for our small business. im by no means great in access but i learned quite a bit in the process of making it about a year ago.

i now want to get back into it and make some major upgrades. but im wondering how to best work on adding to the backend when the business needs to have it in use all day. i could copy it but then if they add data, it wont be there when i copy my changed backend back. I'm guessing there's a way to like copy it work on it, and then a script that compares the two and adds the new data to your modified back-end???

second question is that our database is getting quite large. 168 mb approximately. i know the limit is like 4 gigs or somethin but i would like to come up with a way to archive the old data we dont need anymore. is there any resources for a somewhat automated way to move old data to a separate archive "backup" db of sorts?

Thanks for any and all help in advance!

 

[DCrake]

It sounds liek you have not split your database into Front End back end. Big sin. Apart from reducing the risk of corruption it means you can work on the front end improvements whilst the company still uses the application. Then you deploy the updated front end to each user.

Also have run a compact and repair lately?

 

[rmulder]

no i def did split front end back-end. I have done lots of improvements to the front end and it's automatically distributed when users close and reopen. but i need to add some tables for some new things i want to add aka i need to work on the back-end without kicking everyone out the program.

i have not done compact repair.

 

[DCrake]

Ok it's the back end you are improving, obviously the front end does not know about the new tables. If you are only adding tables then this should e smooth, however if you are adding fields to existing tables then this is something different.

If you are adding fields to existing tables you first need to clearly document which fields need adding to which tables. Then take everyone out of the system. Make the changes and make a backup of the back end before and after the process.

Copy the revised layout to your developement machine and update the front end. do all the testing and now when you delploy the new front end it will have the revised tables alrady in place, abeit empty of data.

 

[rmulder]

ok so i guess i do need to have everyone out then. makes sense. i thought there may be some way to get around that like i can with the front-end.

how about archiving old data? our system os made up of a lot of "orders." I would love to write some sort of vba in conjunction with a form where i filter all orders that have been "delivered" and move them to a backup database. as we do not need them there everyday slowing our system down. does that sound practical or am i on the wrong track?

 

[DCrake]

If you want to archive records make a backup of the current backend then use delete queries to delete completed orders etc. Next run a compact and repair. Again you will need everyone out to run the C&R.

These are administrative tasks that should be done when only you have access to the back end.

 

[rmulder]

Thanks DCrake for pointing me in the right direction. Really appreciate it. Sure I'll be back with more questions.