Will we be sorry we used an .mdb?

[ericShawnSentry]

We're using an Access .mdb for our enterprise.

Are we going to be sorry we used an .mdb down the road when it has gotten large?

I keep hearing horror stories about large databases made in Access suddenly becoming irretrievably corrupted.

thanks.

[pclement]

Well Access can be a bit more high maintenance, especially if you're using it in a shared network environment. But I guess it really depends upon how you are going to be using it. For example, will it be used on a network share? Multi-user? Number of concurrent users expected?

You may want to review the following article:

http://support.microsoft.com/default...b;en-us;303528

[Tonchi]

Hi There,

If you are going to use Access, you need also to consider the amount of concurrent connections. As far as I remember, which is version 97 of Access, you are limited to 64. In SQL Server 6.5, you could have, if I am not mistaking, up to 5000 simultaneous database connections. Also, in SQL Server, you can, or could, have a license per server, which is suitable for clusters of SQL Servers, for high-end or connection-demanding internet applications.

Tonci.

"I am on my way of making it... Big Time!!"

[Gorthog]

After archiving more than five years of sales history (approximately two million records) into an Access 97 database on our shared network, the database recently approached the maximum file size (800+ KB). Although corruption never occurred, performance degradation was an issue. In order to reduce the file size and increase performance, we exported a few of the larger tables to separate database files and established linked tables to access the data. This fix will suffice until we migrate the data to SQL Server.

Access is acceptable for small business and personal use where massive archiving is not required. With regular maintenance (record purging and compacting) Access can probably handle most small business database needs.

Specifications for Access are as follows:

Access 97
---------
Concurrent connections: 255
Maximum files size: 1 GB (less overhead)

Access 2000/2002/2003(XP)
-------------------------
Concurrent connections: 255
Maximum files size: 2 GB (less overhead)

(see http://office.microsoft.com/en-us/as...868081033.aspx for more information)

Hope this helps,


Gorthog
--You will be assimilated. Resistance is futile.

[ericShawnSentry]

Thanks, all. Do you have recommendations, code, etc. on how to perform purging?

[pclement]

You can use the CompactDatabase method supported by DAO and ADO (JRO).

[jay02]

The nice thing about Access MDB is that the info can be Exported to other formats like (the old plain text format) Text (fields separated by commas), and transferred to a new db format with ease.

database recently approached the maximum file size (800+ KB)

I've been using MS Access 97 SR2 OEM for approx 2-1/2 years, with only up to 2 people accessing the "MainDB" at any giving time and it is over 14.3 MB in size. And we've had no problems.
But there are other smaller MDBs that may access info off other MDBs and the "MainDB" on occassion (breaking up the workload).

[Gorthog]

maximum file size (800+ KB)

sorry, that should have read: 800+ MB