My knowledge of Access is quite old so I was wondering if someone with more recent experience could help me out. There is a system for which I may be asked to give some advice on and the data is held on an Access database, I know that at the moment it has about 260,000 records and I would guess that for the main table the records would be about 3-500 bytes in size (I've not seen the actual structure just a few screen shots and an informal chat with a non technical person). The final database needs to be designed to hold 10 million records (designed primary table size of I guess 3-5Gb). Finally records are being entered over a network, thus multiple people are entering data simultaneously.
From my ancient knowledge of Access, I feel that this is really beyond the capabilities of the database. However, I know that the engine has been improved. Am I right in thinking that using the Access database is a disaster waiting to happen, or can the latest engine manage these sort of demands?
I appreciate that this is not a lot to go on, but this is just to prepare me for further discussions, if they take place.

i can't say that from a technical perspective what access will actually do with that much data. i know i certainly wouldn't trust it with it... with multiple users and such you would quite likely come across issues of concurrent access, and also control and distribution of access to the system.

there is no positive reason to use acces at all, other than a path of least resistance, and is not a good reason any way. build it with mysql / postgresql / mssql and interface with php / asp / jsp and you'll get a system that is properly constructed and able to scale far beyond what access can do.

That's pretty much my initial reaction, it's just that it is a system that's already built and I don't think that they have the money to rebuild it... Maybe it would be possible to reuse their front end and slap a more appropriate RDBMS in the backend, but I just wondered, because I've been telling people not to use it for years, and I wondered if the situation had changed.

Some googling has shown me a 2Gb table limit which is useful ammo.

I'm sorry I'm not able to post a complete reply, since I'm at work and time is short.

If keeping to access (or something like it) is crucial (for the above reasons) perhaps you could have a look at trying to migrate to SQL Server Express edition.

It "might" go pretty easy and solve the concurrency issues.

I am not however sure, and will post again when I get home and document

Cheers!

Hi -

10 million rows? On Access? My vote is "disaster waiting to happen".

If you can give the app a web front end, dedicate a PC for Linux, and use Mysql or PostgreSQL - great. Otherwise, if your customer is committed to Windows (for whatever reason), copying the Access data to SQL Server is trivial. I agree with cuppaboy that SQL Server Express is definitely worth looking into:

http://msdn2.microsoft.com/en-us/express/aa718378.aspx

IMHO .. PSM