Managing Access 2000 database

[gthang]

Hi,
Could anyone tell me how to well manage an access database running on a vb 6.0 application such that after a given number of records a backup is made automatically to the archive and all records in the current table are deleted to give room for quicker data access and retrieval. Mark you it should be such that whenever you want to access records whether they are on the current table or the archive you should just do it normally as if the records are in two different locations. I am using ADO's to connect to the database.

[kishore.kr]

hi gthang,

you can use databasecompact option in access thru vb commands. so it will be compacted. and i guess you have to maintain a count of records in the db itself or in a file that had been added. so after the count has been reached you can backup. and in backing up you should write code for retrieval and adding in the backup db. and after that delete the records from the current one.

maintain a date at which the last backup has been created so that when datas are required before that date you can retrieve them from backup db.

you can also give an option to the user to backup themself. so they do it on regular interval.

my suggesstion is deleting the records from the main db and backing up stuffs make your coding more complex. since access can handle data even in MegaByte, you can do the backup once in 3,6, or 12 months. but again it depends on the amount of data transaction occuring on the application. so you have to decide..

-- Kishore..

[Ecniv]

Hi,

Easiest (possibly) would be
-vb app
-template mdb
-current mdb
-archive mdb (date time name?)

The problem then is retrieving as your files increase in number. Then you'd be linking them all together.

The alternative is to perhaps 'back up' overnight/over the weekend?
This would mean
- putting all the data in the current mdb into the archive one
- compacting the archive one
- copying the template file mdb over the current mdb (the template being already compacted and empty)
--- or you delete all records, compact <-- no record id problems

I suggest the evening/weekend dependong on your data being entered because to compact an mdb you'd need exclusive rights--- I vaguely recall, which means no one else can use it whilst its getting smaller.

[gthang]

Hi,
The explanation sounds fine to me but do you have some sort of code to guide me through or a link