How many records can Access handle?

[Samrasr]

Hello,

Whats the maximum amount of records that Access can handle, my form is used for data entry with a memo field.

 

[DJkarl]

Hello,

Whats the maximum amount of records that Access can handle, my form is used for data entry with a memo field.

Access doesn't have a record limit that I'm aware of, it has a size limit of approximately 2GB. So how many records you can fit into that 2GB depends on record size.

 

[dfenton]

Whats the maximum amount of records that Access can handle, my form is used for data entry with a memo field.

Your question is not well-worded.

Access is a tool for building front-end applications and can connect to a whole host of back-end data sources, including Jet MDBs and SQL Server and MySQL and Oracle and so forth. Thus, the limitations on record storage will depend on what back-end database engine is in use.

Of course, everybody (including Microsoft) uses "Access" interchangably with "Jet," which is a very bad thing, in my opinion, as it's important to understand the differences between Access the application platform and Jet the database engine, precisely because they are independent of each other.

Now, Jet has no record limits, but an MDB is limited to 2GBs in size, so theoretically, you could store millions of records in a Jet database. However, in an application, that might not work very well.

It all depends on how your data is structured and how it is indexed.

For what it's worth, one of my clients' apps has 350K records in the main table and 450K in the main child table and at one time had 600K in another child table (since purged down to about half that -- we removed legacy data that was useless). The app never loads a complete table, but only one record or a small group of records at a time, and is shared by about 12 simultaneous users (most doing read-only lookups, though). It works just fine -- no complaints on speed at all.

I consider that to be not even close to stretching the capabilities of a well-designed Access app with a Jet back end.

But the more records you have, the more usability is going to be affected by how well-designed and efficient your application is. Here's an excellent discussion of performance issues:

http://www.granite.ab.ca/access/performancefaq.htm

--
David W. Fenton
David Fenton Associates
http://dfenton.com/DFA/

 

[GaryPanic]

I think that has been answer quite well.

Access has a size limit of 2 g
upgrade to ms sql express 4 g
then full sql server

however you can archive a lot of stuff - i have used access for stuff and in over 8 years I think we only archive as a matter of bookkeeping and not a size issue we were handling estmated 500,000 records all tickerteeboo