Limitations of Microsoft Access 2000

[Baris Demir]

Hi All

Where can I find the limitations of Microsoft Access 2000.

Is it OK to have 100s of thousand of records in a table?
Does this create a problem?

Is it OK to have an mdb which's size is more than 50MB
(even after compacting)?

What should be the parameter to decide between using
Access and SQL Server?

Thanks

 

[Cameron Sutherland]

There are no record number limitations. I have a database
with over 3 millions records in it. Each MDB file,
however, has a size limit of 2GB (2048MB). SQL Server will
handle your queries much faster than an Access database
however and there is much better secruity in the server.
Access also has a limit of 255 concurrent users which
might not work if you are using this for the Web. I read
somewhere that Microsoft suggests that when you reach
30000 that then you should think about upsizing but in my
experience that cost (initial purchase and administration)
of going to a SQL server put my personal limit much
higher. I would suggest that if you already have a SQL
server in your environment you should switch but if you
don't then I would stay with Access until your speed or
number of users is a problem.
-Cameron Sutherland

 

[John Vinson]

Hi All

Where can I find the limitations of Microsoft Access 2000.

By searching for "Specifications" in the online Help.

Is it OK to have 100s of thousand of records in a table?
Does this create a problem?

The largest production Access database of which I am aware has some
20,000,000 records in the largest table.

Is it OK to have an mdb which's size is more than 50MB
(even after compacting)?

Yes. You're limited to 2048MByte (2 GByte).

What should be the parameter to decide between using
Access and SQL Server?

For starters: Performance, number of concurrant users, necessity for
use over the Internet (where SQL is probably a better choice),
frequency of corruption, business criticality, budget, availability of
expertise in one product or the other...

That's a bit like asking whether you should buy a sedan or a pickup.
How will you be using it, and what are you hauling?