Access '97 .. too many records?

[MrTibbs]

I have a couple of tables that exceed 200,000 and 500,000 records respectively. What views does anyone have .. is the number of records excessive for an Access '97 split database?

Performance is adequate [defined as no complaints from users] so I wonder whether it is worth worrying about .. is there a hard number of records limit in Access '97?

 

[Len Boorman]

Think limit is 1 gig for a table and 2 gig mdb file.

have database with 5.5 million records in table.

Okay no probs and only about 200 meg so away to go yet if necessary.

Len B

 

[The_Doc_Man]

For Access 97, use keyword "Limits" to find out the limits on things.

Tables can get as large as 1 Gb and a DB can get as big as 1 Gb. The limit gets bigger for Ac2K, but in Ac97, it is still only 1 Gb.

However, since a DB can link to external DB files, you can have several tables as big as 1 Gb EACH and the central DB would just have to link to them. Of course, this limits certain actions, 'cause linked tables are not QUITE as versatile as included tables.

But there is certainly an issue about whether your DB has been repaired and compressed recently. With that many records, assuming them to be non-trivial, I would certainly be concerned that I cleaned up that DB on a regular basis. Of course, the issue is ALSO how much "churn" factor you have. Like, deleting and adding records in a table tends to "churn" it. If your tables are mostly just for lookup, your churn factor will probably be low. If you do a lot of inserting and deleting, it will be higher. The higher the churn factor, the more often you should copy, compress, and repair it.

 

[KenHigg]

....And

Other questions like:
How long has it taken to get that big?
Are you really hitting all those records on a regular basis?
Can you do a routine that would periodically archive older records to a separate .mdb, say yearly?

kh

 

[MrTibbs]

Limits .. found it! and other answers

Found Limits in Acc 97 help: There are many but they will not be hit any time soon! Thank you The_Doc_Man for the required search term.

It has taken 3 years to reach those record numbers. All the back-end tables totals 150Mb and have zero churn .. the data is appended in 70-150 record chunks each day and never deleted.
Recent record are retrieved for reporting at least daily and old records less frequently, around once per month.

The db is never compressed / re-indexed or anything but is backed up every night

Thank you all.

Tony