How to prove the limitations of using MS Access as a database?

Associate
Joined
6 Jan 2006
Posts
1,020
Location
Midlands
Hi,

One of our client has an old application built using Classic ASP and Access back-end.

Lately they are having a lot of problems with the application i.e. pages not loading, really slow.

Never had any issues with it before and I suspect it is due to the database (being Access) and the size of it. It is around 200mb.

Is there an easy way to find out if the database is limiting the application?

Thanks.
 

~J~

~J~

Soldato
Joined
20 Oct 2003
Posts
7,558
Location
London
I don't think you should 'prove' it, I think they should ultimately accept that you're knowledgeable enough to advise correctly.

Rather than waste time, could you not quickly knock up a mirror of the database in SQL and just change the connection from Access to SQL and show them the difference?
 
Soldato
Joined
16 Feb 2004
Posts
4,767
Location
London
Depending on how they are using it one of the main issues is it can start to struggle with anything more than about 10 concurrent connections.

Have a look on this page which has links to MS KB's on Access

what-are-the-limitations-of-ms-access

ie
"ASP also supports using the Microsoft Jet database engine as a valid data source. The Access ODBC Driver and Microsoft OLE DB Provider for Microsoft Jet are not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as web, commerce, transactional, messaging servers, and so on."

Oh and some more info, ie what happens if Access errors? you need to restart iis apparently (if your using it for web projects)
Also it locks the db for all reads when it does a write and can only support one write at a time apparently.

more access info
 
Last edited:
Associate
OP
Joined
6 Jan 2006
Posts
1,020
Location
Midlands
Thanks for the replies.

As it was working fine when it was launched, i suspect its due to the size of the database. Would you classify 200mb Access database as big?
 

~J~

~J~

Soldato
Joined
20 Oct 2003
Posts
7,558
Location
London
I think as DanF said, it's probably not really the size it's the number of concurrent users trying to access the database at any one point.

200Mb I'd say is relatively small for an Access database, considering that, IIRK, Access has a 2Gb limit on the MDB files.

Could be a combination of bad coding, but I'd hazard a guess towards the number of users trying to connect to it at once.
 
Man of Honour
Joined
17 Oct 2002
Posts
50,384
Location
Plymouth
Last time I did it, it exploded. :/

Just show them that link.. 2GB max size limit. That's not a lot.

It is when you are only using 200mb.

While Access does have its issues, the problem here, as has been said, is likely to be the number of concurrent users undertaking transactions, rather than the size.

You could look at MS SQL server Express as an alternative, which is better than Access and probably more suitable for the task at hand without requiring huge changes to the application itself.
 
Soldato
Joined
8 Oct 2005
Posts
4,185
Location
Midlands, UK
Thanks for the replies.

As it was working fine when it was launched, i suspect its due to the size of the database. Would you classify 200mb Access database as big?

Absolutely not, in fact in my experience Access can handle that database size fine. I really started to see it slow down to a grind when I was using a database a tad under the 2GB limit - the efefct was worsened when people tirted to access this over a local network.
 
Associate
Joined
6 Jul 2004
Posts
995
Location
England
Is there an easy way to find out if the database is limiting the application?

Depends what you call "easy" but I would add some code to the pages that showed how long it takes to query the database, and how long it takes to render the page.

The output might look like this:

Code:
Database query: 500ms
Page render: 20ms
Total: 520ms

If it really is the database that's slowing it down then you should have some evidence.

As others have suggested, you could also try replicating a couple of tables in SQL Server and then run the tests again.

When it comes to performance stuff you really need proper scientific results. Saying that Access is cr*p isn't really enough. (It is cr*p though ;) ).
 
Back
Top Bottom