Is it reasonable to have 2 million posts in a table

[didi]

I want to know the capacity of access database 97 and 2000.
I have an access database which is used for OLAP
solution. One of table has over 2 million rows these days
and I experience significant degradation in the process
speed.
Is it not reasonable to use access when the table gets so
long and large?
Ids there anyone to know the max row count which access
can handle very well?
Of course I understand everything is depend on how large
each row are and what hardware we user etc.

Any suggestion is most welcomed!

 

[Grumpy Aero Guy]

Table/Database size is more a function of memory on the machine than it is
an access limitation (unlike excel, which has a row limit of somewhere
around 67,000 I think).

I believe the rule of thumb is to keep your mdb below 1Gb.

More experienced folks may speak up here, but I think I'm right.

Otherwise, split the db.

 

[Ric Passey]

access database limit is 2GB I believe.

I you're really stuck upsize it to msSQL server.

Ric

 

[Brendan Reynolds]

There's no maximum number of rows. Maximum size of an MDB is 1GB in Access
97, 2GBs in later versions.

Look at the indexes. Are all fields used in joins, in criteria, and in
sorting and grouping operations indexed?

Look at the queries and code used to manipulate data. Can they be further
optimised? Do forms and reports include the minimum records and fields
required?

Look at the environment in which the application runs. Are the client PCs,
the server, and the network up to spec? Is the app fighting for bandwidth
with other network-intensive applications?

If none of those possibilities seem to offer a solution, then it may be time
to move to a server database such as SQL Server, or a redesign of the
database and application may be required - even when using server databases
such as SQL Server, it is, so I understand, common practice to aggregate
data into summary tables for OLAP operations.