access and ASP.NET

[phil]

We have a small website using Visual basic and ASP.NET. We hold a database
in Access format which is opened with the following 3 lines

Dim m_dataBase As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\callidusWorld\app_data\webAccess.mdb"
Dim myConn As New OleDbConnection(m_dataBase)
myConn.Open()

most of the time this works as expected but every now and then it seems
unable to open the database and throws an exception.

Any subsequent call continues to raises the same exception and the only
remedy so far is to reboot the server which is far from elegent.

I cannot change to another database and never had this problem with ASP - if
any one has any ideas I would be very grateful.

Many thanks in advance
Phil

The rather unhelpful message from ASP.NET is that it's an unspecified error
Unspecified error
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Unspecified error

 

[Mark Fitzpatrick]

What kind of operations are you performing and what is the amount of
traffic? I ask because the performance of an Access database drops
significantly as you perform update/delete/insert operations and begins to
generate errors. Even a low count of 10 concurrent users will start to
degrade the performance. Selects don't have as much trouble.

If you're using DataReaders to fetch the data, make sure that you are
closing them and releasing them as soon as you're done as a DataReader
maintains a connection to the database until the end of the resultset.

You could try using the trace functionality to see write out some debugging
information, such as what exact operations are going on at the time. That
way, it may be possible to play with it until you can get it to crash and
view a trace log.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

 

[Guest]

Phil,
The best thing you can do for yourself is get rid of Access and switch to
say, SQLExpress. Since you indicated that for some reason you cannot do this,
the next step would be to make absolutely sure that all connections are
properly and promptly closed immediately after the unit of work using the
connection is complete. Access just was never designed to be a multiuser
database.

Peter

 

[phil]

I'd love to do that but I have a great big Access system with VBA and the
works all doing exactly what I need. The ASP.net is just to provide a public
display of some data - the traffic is very low and it seems overkill to
rewrite the whole thing when it was working perfectly in old ASP

Thanks for the info though
Phil

 

[phil]

I've used a session variable to 'trace' when this is being called and
hopefully next time I may get some indication as to what is going on/wrong

Seems ASP.NET is more fussy that ASP

Phil

 

[Mark Rae]

I have a great big Access system

There's the problem...

 

[Anthony Bollinger]

Rewrite? Is there a true business need to leave ASP? If so, fine. If not,
stay.

 

[phil]

Dear Peter

thanks for the help

I found a couple of places where I was not specifically closing an
OleDbDataReader and having fixed them it now seems very stable

A word in defence of Access

In our situation where most of the system is being used in house the design
features of Access (Forms, Reports, VBA) are a delight to use. We would not
have more than 5 people on at any time.

To provide a reasonable degree of public visibility the same database works
very well in ASP and ASP.NET so long as the connection times are as low as
possible.

To test the stability I opened 100 almost simultaneous connections to the
server and each one performed perfectly and pretty quickly too.

Every time I mention Access on these newsgroups people seem to hold up a
cross and predict all manner of problems. But the reality is that for the
right site and the right application Access is superb.


Once again thanks for the help you gave - it allowed me to fix the problem

All the best
Phil

 

[phil]

No problem any more

Access is fine for the right type of application

Thanks

 

[Mark Rae]

Access is fine for the right type of application

It certainly is.