Access Databases Crashing under load

Is this normal?

[jgar]

Our wesbite has been crashing recently every couple of days.

Looking at our databases on the server shows filename.ldb versions alongside the filename.mdb versions, and these seem frozen.
When trying to move these files, the ftp program tells us the file is busy and nothing can be done.

Getting the site going again requires a call to the hosting company to get the site restarted.

Obviously we are not happy :-( with this (and neither,of course, is our hosting company). Our hosting company believes the problem occurs because too many users try to acces the site at the same time during peak periods (and especially when aggressive spiders are about).

This problem has only started recently. The only thing we changed recently was to change the databases from Access97 format to Office2000 versions.

The hosting company recommends we move to SQL databases and a deicated server, acknowledging we would need to change hosting company to do this.

However, dedicated servers are much more expensive, and technical support for mySQL on a Windows2000 platform (we use ASP 3.0) is hard to find. (SQL, for which there is technivcal support in general, seems to require an expensive licence)

Some questions I am struggling with are:

1)Is it normal for Access to crash like this? (shouldn't it not just slow things down until fewer users are present, and not crash?)
2)Can we solve this problem more economically with Windows2000 (we use ASP 3.0) shared hosting and SQL databases?

If anyone in this great forum has any tips, I would be very grateful.

Also, if anyone has any tips about a hosting company (UK),please stickymail me.

Jgar

[ukgimp]

Have you got loads of connections that are not required? ie have you closed unused ones down?

Could be a reason

[korkus2000]

access creates a ldb file while it is being used. If you try to do anything with this file you will have problems. If you are seeing a lot of traffic then you may need to upgrade to sql. There are hosts out there that do sql very inexpensively. What are your traffic loads daily? Access will crash if it has enough concurrent users.

Also are you killing the database connection and object. You could be creating a memory leak. Are you trying to upload a new copy of the database when it locks?

[jgar]

<There are hosts out there that do sql very inexpensively>

Any recommendations you can stickymail me with?
(we are Europe-based, but would consider a US company)

Regarding open connections, we feel we are quite safe on that on, but will check again. We are getting about 1 new visitor every 15 seconds at peak-times (ignoring spiders), so the overload seems likely.

Thanks for your responses

Jgar

[aspdaddy]

As well as what Korkus said , there is also a problem called oppurtunistic locking (opLocks), on NT4. If a user has a bad connection it can cause this.

I would go with Win2K, clean up the open/execute/close functions using best practices and then re-assess it, may not need SQL afterall.

[jgar]

Thanks Aspdaddy,

When you say "best practices", do you know of a good source of info.?

Jgar

[txbakers]

Yes, this is normal. Access isn't designed for server use. It's a great little desktop database, but can't handle multiple users and constant traffic.

I was using Access when I first started my webbased database applications and when 6 users were logged on simultaneously it would crash. I upgraded to mySQL and everything was fine.

Get rid of Access and you'll be fine.

If you want a hosting recommendation, sticky mail me and I'll share.