large database question

[tim l]

I have an Access database with 1.8 million records in a
single table.

Am I pushing the limits of Access here?

Should I consider using a different program than Access?

 

[Albert D. Kallal]

It really depends on what you are doing with that data, and ESPECIALLY how
you are accessing the data.

if there is no network involved, and the amount of records you retrieve via
a query is not large amount (AND you can use a index), then that file is
certainly workable.

You don't mention how large the mdb is after a compact. I would guess...what
300 megs?

However, if you have a network, and 20 users..then likely it is time for
something more then JET.

So, you can work with that large of a table? Yes, but other issues such as
number of users, a network etc. would really sway the issue on if you should
upgrade to a system with a larger data ability.

There is no simply "yes" or "no" answer based on the info you given. It also
depends on what the application does, or how the data is to be used. I mean,
if it is to lookup a part descritipion based on a part number you entered,
the response of the system is likely the same as if you had only 10,000
records...

If this is a multi user application, then I would be looking to use a server
based system.

 

[Dirk Goldgar]

I have an Access database with 1.8 million records in a
single table.

Am I pushing the limits of Access here?

Should I consider using a different program than Access?

It's not the number of records that is significant so much as it is the
size of the database file itself. Access 2000 and later versions have a
maximum database size of 2GB, and one begins to run into trouble
performing some operations even before that. What version are you
running, and how big is your (compacted) database file now? If you're
getting near the limit, you may still be able to work well in Access,
depending on the sort of functions your database is performing, but
you'll have to watch it very carefully, and may have to find ways to
work around the size limit.

It's perfectly possible to have an Access application that uses multiple
..mdb files as its data store, with a single "application" .mdb that
holds only links to the tables in the various data files. This takes a
certain amount of design, however, and if you have a single table that
pushes the size limit you should probably be looking at some other
database for your data store. That doesn't mean you can't still use
Access as the user interface and reporting tool for that data store.