Can an Access database be too large?

The ASP pages in my online shop crash out about once a day and my IT company tell me it is probably because the Access database is too large and I should change to SQL. The Access database is 147MB. Is this too large?

 

147MB is well under Access' max size of 2GB. Here's a list of the limits of access 2k and 2k2. Other concerns could be # of users and total # of records.

http://www.micronetservices.com/Access2kCapacities.htm

 

There's about 6,000 visitors on the site per day which i reckon relates to about 12 folk at any moment are browsing the online shop at any given time. The site doesn't necessarily crash out when its busy though, so it doesn't seem to be related to the number of users. What do you mean by records? Sorry if this is a dim question but my IT guy left my company to go work for another and i'm just doing my best to cope!

 

The number of rows in the database, also called records. The filesize limit is one, but total # of rows/records in the DB could play apart as well. Averaging 12 users is high for MS Access (I would assume it's ok though cause I doubt any are running hard queries). But if the crashes occur at off peak times it might not be the factor. This is just my opinion, if your site is growing and has that many visitors already, you probably should look to get off of Access. Other thing would be to look at your log files and see what's going on prior to the crashes. It might provide some clues as to what the problem is.

 

Sounds like we have some bad coding.

Was this home brew system or purchased scripts etc?

Have a look at the error logs and see what is causing the fault.

You might find that some drop down is causing an infinite loop in a query or something silly like that.

 

Home brew really. Chap that did the code used to work in an engine test place doing data logging code etc and then started out doing websites in about 1999. So he taught himself over the years how to do online shop stuff. I also used to do programming in an engineering environment and i modify his code as i need, so yes its all a bit added on here there and everywhere. The same basic code used to run a gifts business website which could handle an order per minute but with a much smaller database (about 1/100th).

 

Also, there's about 340,000 rows/records in the database. Is that ridiculous?

 

It is not rediculous but Access is not an enterprise database solution and whilst it can physically cope with both very large file sizes and moderate numbers of concurrent users it really isnt what it was designed for and will slow or crash much more readily than an SQL solution designed to take these levels of traffic.

Depending on how well coded the site is there shouldnt be the greatest amount of change needed to migrate from Access to an SQL server (MS SQL, MySQL, Oracle etc)

 

yes my ex-IT guy still does little jobs on the side for me and i keep discussing the crashing problem with him. he has tried various things but he is now suggesting moving to SQL. From what you guys are saying it sounds like he is right. My difficulty is that he often seems to do things by trial and error (that's why i'm now asking on this forum) and he's talking serious money to change over to SQL which i can't afford just now.

 

The size of your database is nothing extreme.
Access is intended as a user and developer friendly, multi-user database system. Unfortunately, it is riddled with bugs and if you're not making hourly backups of your database, susceptible to corruption.

You should consider migrating to a content management and data base system that are more robust and designed for use in web environments. These alternatives will make your life easier if you invest in the migration.

 

I have been running an online shop using Access database for around 10 years. I have split the product database from customer database (ie. two files ) - So the databases doe not grow that much. I can flush the old customers from time to time from system.
Also you can download the database and verify and compress using MS Access and then upload again. That should reduce your problems. By the way I am also now migrating to MSSQL - writing code now.

 

use sql yog man
you never access the file if > 20MB
CMIIW

 

I can speak with a lot of experience about Access. As stated above the file size limit on an Access DB is 2GB. Access is a great rapid development tool to build demo or test systems. But once your "proof of concept" is built out you are best moving your data to a MySQL or MS SQL database. Access just gets too buggy and is not reliable for a production system. Prepare yourself for lots of "compact and repairs" w/ Access...

 

How are Access a "great rapid development" tool? I don't understand it. It probably takes me longer to map out a proper setup in Access than it takes to make a few PHP-files and a MySQL-database.

 

This thread is 6 years old...