Problems with Access Database...

[cusimar9]

I'm writing an access driven website and I'm already having problems with it :(

I come in this morning to find this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC Microsoft Access Driver] Too many client tasks.

There is only me using this at the moment so I don't see how this could be the case.

This is how I connect to the database:

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db1.mdb")
Set rsDatabase = Server.CreateObject("ADODB.Recordset")

And whenever I finish with the database I do this:

rsDatabase.Close
Set rsDatabase = Nothing
Set adoCon = Nothing

Has anyone any suggestions? I don't expect the customer's website to be particularly high traffic so I thought Access could handle it, but if its struggling with 1 user I can't imagine how its going to cope with 10...

[cusimar9]

It's probably not related, but I added the following lines and its decided to start working again now lol

Connection:

adoCon.ConnectionTimeout = 15
adoCon.CommandTimeout = 30

Close:

rsDatabase.Close
adoCon.Close
Set rsDatabase = Nothing
Set adoCon = Nothing

Could it have been because I wasn't closing the adoCon object?

[cusimar9]

Nope its still not working, this is ridiculous...

I've changed the script so basically the page that access the database caches the data and will only access the database if the cached data has expired.

I'm the only person using it, and it's been working find for an hour or so and now all of a sudden its giving the error message again.

I thought the Jet drivers are supposed to allow for about 20 simultaneous users??

Could it be that its a shared server and the 20 simultaneous users is shared on the server, not the database?

[minority]

how many of these database calls do you have on the one page access as i have been told is crap for multiple hits (Even from one user you can hit it more than once)

It can only handle like 10 connections at a time and i guess if you open three connections then that is 3 hits (Maybe).

Are you remembering to close your connections afterwards.

i know oyu are closing the recordset.


Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db1.mdb")
Set rsDatabase = Server.CreateObject("ADODB.Recordset")

rsDatabase SQL1, adoCon
rsDatabase.close

try that see what happens as with that code you close the open connection as well as the recordset.

[zingmatter]

It might be something to do with your host server. We have a site running an Access db which sometimes handles up to 100 or so users at any one time. We've been expecting it to break for ages but it just battles on. We were originally hosted by ActiveISP (who were pretty awful all round) and we were hitting db crashes. We then changed to Hostway and we've had no problems since, and that includes a significant increase in traffic since then.

We have this at the top of each asp page. Donno if this helps with buffering:

With Response
.Buffer = True
.Expires = -1440 * 400
.Clear
End With

Hope this helps

[cusimar9]

Damn we're using ActiveISP :(

I notice Hostway is more expensive... which plan are you using? I notice with the Silver Plan it says the Access DSN is optional?

What exactly do those buffering lines do?

I've changed the script some more... I was looping through the recordset after a query and obviously that's keep the connection open for longer. I've rewritten a page to just use 'getrows()' and close the connection as soon as possible, and it does seem to be working more reliably. I'll rewrite the rest of it in the same way.

I ideally need to stick with it as rewriting for MySQL would take ages and getting hosting for SQL Server would be much more expensive and to be honest I don't think the client needs it...

[cusimar9]

I've just done a little test and I'm pretty sure we can only have 1 or 2 users on at a time...

[Bullschmidt]

And here's an interesting, related link (although this person is a little hard on Access which has worked well for me on many Web sites):

http://www.aspfaq.com/search.asp?q=80040e4d&x=11&y=11

[cusimar9]

Yeah I read that, doesn't paint a pretty picture! lol

I've rewritten ALL the code and optimised DB accesses wherever I could, I naively ignored it before. Now customers should only access the DB twice - once to retrieve all the products, and once when they make their order, just to write their order to the database.

In addition I'll be going by Zingmatter's ISP Hostway (though I'm happy to hear any other success stories) rather than Active 24, who I believe is responsible for my problems.

In short, if it doesn't work for this site, its never going to!

[zingmatter]

Let us know how you get on. I'd be interested to hear if it was the host or not.

[cusimar9]

Will do :)

[sajjad27s]

If you are opening the connection more than 1 time in a page, dont do this. It might get trouble. It is better to open the DB connection in Global.asa and assign it to some session or better to some application variable.

like this;


Sub Session_Start()
' --------- Open connection object here --------
set session("cn") = objConnection
End Sub

Sub Session_End()
set session("cn") = nothing
End Sub

[cusimar9]

I think I only connect once per page, though sometimes I close the recordset, reopen it and run another query...