ACCESS 97 performance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, can anyboydy tell me how access 97 performs within a single
table/form/query environment if the table holds about 21,000,000 (english
millions) rows, 5 columns? no indices, 1 primary key. query on primary key to
return up to a max of 100 rows.
 
Hum, this newsgroup is about ADP and SQL-Server, so it's probably one of the
worst newsgroup to ask this kind of question.

However, I can tell that you are probably stretching A97 to its limit with
21 english millions of rows. Not only this number of rows will give you a
MDF near it limit of 2Gigs but don't forget that updating an Access database
often result in an increase of the MDB file; which raises the clear
possibility of breaking the file size limit of 2Gigs even if theoritically,
in a perfect world, you are under it.

Why don't you make the test yourself? You could build a test database and
fill it with dummy values using some VBA code or something like that.
 
I had a dozen tables, each with more than 5 million records in Access
MDB.. more than 5 years ago

so yeah; you might be off by a factor of a couple.. but if you use
small numerics; it's definitely possible to use MS Access for 21 m
rows.

But make sure you use small bytes, integers instead of long integers,
etc

and enforce referential integrity for sure

but seriously-- use SQL Server and Access Data Projects instead of
dealing with a 2gb limit.

SQL 2005 Express with ADP uses a 4gb limit- and it's FREE
 
and also-- for the record

Don't -EVER- use anything but an integer for a PK / FK.

using text for large tables like this is just flat out ridiclous

integers are always faster than text keys
 
Back
Top