Reliability if MS Access

[Stuff]

I have read in several places informaton alluding to Access' unreliability
with many users writing to a table. No metrics were ever given.

I am mainly concerned with implementing a simple Access-based hit counter,
where a hit simply increments a field a two-field table...but, this is a
busy busy busy site with many many pages.

Where can I find such information?

ThanX for you input,
"Howie" Adams

--

"Before I end my letter, I pray Heaven to bestow
the best of Blessings on this House and all that shall
hereafter inhabit it. May none but honest and wise
Men ever rule under this roof."
John Adams, in a letter to Abigail,
on his 2nd night in the White House.

 

[Arvin Meyer]

Access is reliable enough when there are up to about 50 concurrent users and
a well designed and indexed system. BUT an asp interface turns Access into a
server based database, which it doesn't handle well with lots of concurrent
hits. I have had an asp interface corrupt a database with as few as 6
impatient users requesting a report that took 30 seconds to generate, but
they kept clicking repeatably.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[timmso]

Is there a way to limit the number of "hits" to the db at one time?

 

[Stuff]

ThanX for the reply. I can see my application is not as critical as the one
you mention.

 

[david epsom dot com dot au]

This is difficult to do with Access, unless you only use
one connection to the database, which would be a bottleneck
on a busy site.

Access connections independently cache data: unless you carefully
flush and refresh the cache (which makes the system run slower),
you won't be able to keep track of the current data value.

(david)

 

[david epsom dot com dot au]

This was known to be a problem with the Access ODBC drivers:
is it also a problem with OLEDB?

(david)

 

[Arvin Meyer]

Perhaps in IIS, but definitely not in Access. Access (JET) itself is not a
server based database and thus has very few server based processes.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

I'm not sure, David. I never use OLEDB against an Access (JET) database,
only against SQL-Server where is is faster. SQL-Server, being a true
server-based engine has no problem at all with multiple continuous
connections.

I do have an Access database in use on a web server, but there's only about
a thousand hits a day, and they are spread out throughout the day. That
database has never corrupted.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Stuff]

Arvin -- hearing your web based Access database with 1,000 hits/day has
never corrupted is music to my ears...thanX!

 

[david epsom dot com dot au]

Our Jet 3.5 web site worked happily until it was mentioned in
the press -- then collapsed and was replaced with a SQL Server
7.0 back end.

Many years ago, the database classes of MFC were not thread
safe, and anything built with them, including apparently the
Access ODBC driver, was not thread safe. (Yes, people trying
to use MS C++ to build multi-threaded applications against
other database engines had the same problems).

It's not normally the kind of problem you see while doing
multi-user access: even if you had a thousand people using the
database at the same time they would still each have their own
copy of the ODBC driver. It only showed up as a problem when you
had IIS trying to run ODBC connections on separate threads.

It's hard to see exactly what happened after that: We never tried
again, MS never came out and said that the problem with the Access
ODBC driver had been fixed, but on the other hand, nobody has said
that the later versions of the MFC still had the problem, so a
simple re-compile of the Access ODBC driver might have fixed it.


(david)

 

[david epsom dot com dot au]

Is there a way to limit the number of "hits" to the db at one time?

It's called 'serialization' and is generally considered a Bad Thing.
People spend time and effort avoiding serialisation in the process,
because it makes the load increase much more dramatically as the
number of web hits increases.

(david)

 

[Arvin Meyer]

Our Jet 3.5 web site worked happily until it was mentioned in
the press -- then collapsed and was replaced with a SQL Server
7.0 back end.

Exactly the reason I will never give out the URL of any of Access driven
sites as examples of how it works. I do know several that will function
quite nicely unless they are hit with continuous concurrent requests.

MS never came out and said that the problem with the Access
ODBC driver had been fixed, but on the other hand, nobody has said
that the later versions of the MFC still had the problem, so a
simple re-compile of the Access ODBC driver might have fixed it.

But I wouldn't count on that, or base my business on that either.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

Arvin -- hearing your web based Access database with 1,000 hits/day has
never corrupted is music to my ears...thanX!

A thousand hits evenly spread throughout the day is less than 1 hit per
minute. It would depend upon the number of requests, how long it took to
satify those requests, and how/when they bombarded the server. During off
peak server usage, and fast query response time, you might get away with 3
or 4 hits a minute, but 3 or 4 during the same second or 2 will crash it
faster that you can blink. The point is, you cannot count on it!
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access