Number of rows in a table

[Meinia]

I'm developing a Access DB which will replace Quickbooks for about 60
customers. This involves about 36,000 - 40,000 trasaction per year.
Is there a limit to the number of records (rows) that a table can
include? Is this good practice? Anyone done something like this
before and how did it work?

[Peter Russell]

Unless you are proposing to include images or other external objects
(.xls, .doc file for example) in your data fields then you have no
problems.

Make sure you index any fields used for searching.

There is no limit on record numbers. Your mdb cannot be more than 2Gb for
A2000 onwards, although it's not a good idea to start out somewhere near
this limit. But I would hazard a guess that you'll be within 10Mb which
is no big deal.

Regards

Peter Russell


Meinia previously wrote:

I'm developing a Access DB which will replace Quickbooks for about 60
customers. This involves about 36,000 - 40,000 trasaction per year.
Is there a limit to the number of records (rows) that a table can
include? Is this good practice? Anyone done something like this
before and how did it work?

[Scott Mac]

Just 2 more cents worth... and to confirm from first-hand experience

Built an app that grew and grew - to 440,000 plus Inventory
Transaction records yearly. Bill of Material record count 380,000
active on average. File Size runs in the 100MB neighbourhood

No issues - 6 years running, not a byte of data lost.
Front-end/Back-end with NIGHTLY network backups, typ 20+ users.

A couple of thoughts from the BTDT (Been-There-Done-That)School:
- Archive Annually, use a rolling 6 month active history for instant
access
- Really scrutinize the size of EVERY field as you build...
the effects on file size are quite amazing as time goes by
- Good Indexes on this huge Bill of Material Table continue
to be lightning fast
ie. Literally, you blink, and it's on display (100 Mbps Fiber LAN)
- Lastly, beware the naughty integer that you learned about...

this one crept up on me from an innocent, well-hidden, "early days" counter field
32,768 is not a happy place...

Have Fun!
Scott
I'm developing a Access DB which will replace Quickbooks for about 60
customers. This involves about 36,000 - 40,000 trasaction per year.
Is there a limit to the number of records (rows) that a table can
include? Is this good practice? Anyone done something like this
before and how did it work?

I've heard of databases here with upwards of 500,000. The only
problem with keeping lots of records in one table is that querying may
slow. So make sure you index properly.

[Lyle Fairfield]

sm*******@sympatico.ca (Scott Mac) wrote in news:1a0934d4.0310022033.77c519e6
@posting.google.com:

Really scrutinize the size of EVERY field as you build...
the effects on file size are quite amazing as time goes by

UhHuh!

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)