Emergency! Too Busy A Connection!

Hi.
I have a problem with an OLEDBConnection object that is supposed to serve for
a number of clients that need data from my Access(mdb) database.
These clients are placed in different threads and try to load data from this
connection. The problem is that when the connection is retrieving data for
one of them, another one may ask for data. This throws an
InvalidOperationException with the following message:
"ExecuteReader requires an open and available connection. The connection's
current state is Open, Executing."
I fully understand its meaning, but what can I do to solve this issue?
I tried a delaying loop, checking to see if the exception occurs again in
the loop,
and repeating the operation again. Although I used Application.DoEvents (you
know what that does) , but a loop takes all the CPU time available, and it
seems to me that the whole operation becomes very very slow and surprisingly,
memory consuming!
Any one got an idea?
Thank you all.

WJ
Mon Aug 29 20:31:55 CDT 2005


"Ehsan" <Ehsan@discussions.microsoft.com> wrote in message
news:3492D480-E1A2-4787-B1FD-3A12CF5CB66B@microsoft.com...
> Hi.
> I have a problem with an OLEDBConnection object that is supposed to serve
> for
> a number of clients that need data from my Access(mdb) database.
> These clients are placed in different threads and try to load data from
> this
> connection.

Access is a single user database, it may not be able to handle multi-threads
asynchronously

John

Bob
Mon Aug 29 21:32:19 CDT 2005

Ehsan:

WJ is right to a certain extent. Actually, it can have more than 3 users but
it needs to be opened and closed in your code. Such as:

OLEDBConnection.Open()

Load some records but not a whole lot.

OLEDBConnection.Close()
OLEDBConnection = nothing

Same thing if your adding or updating. Open the connection make the changes
and close it again. That will free up some processes for others.

Also, don't try to have everyone loading all the records from the tables.
Try to load the app and have some kind of filtering in your SQL queries.
Especialy if your trying to load more than say 10,000 records. Also check
your networking equipment. If your cat 5's are wired strait through on all
pairs, that can cause cross talk. I've only seen it once and it realy messed
with an MS access app. Sounds strange I know.

HTH

Bob

Peter
Tue Aug 30 07:54:21 CDT 2005


"Ehsan" <Ehsan@discussions.microsoft.com> wrote in message
news:3492D480-E1A2-4787-B1FD-3A12CF5CB66B@microsoft.com...
> Hi.
> I have a problem with an OLEDBConnection object that is supposed to serve
> for
> a number of clients that need data from my Access(mdb) database.
> These clients are placed in different threads and try to load data from
> this
> connection. The problem is that when the connection is retrieving data for
> one of them, another one may ask for data. This throws an
> InvalidOperationException with the following message:
> "ExecuteReader requires an open and available connection. The connection's
> current state is Open, Executing."
> I fully understand its meaning, but what can I do to solve this issue?
> I tried a delaying loop, checking to see if the exception occurs again in
> the loop,
> and repeating the operation again. Although I used Application.DoEvents
> (you
> know what that does) , but a loop takes all the CPU time available, and it
> seems to me that the whole operation becomes very very slow and
> surprisingly,
> memory consuming!
> Any one got an idea?
> Thank you all.

In addition to the other responses, it may be time for you to consider SQL
Server or another DBMS better suited to your environment and concurrent user
count. Access is strictly for a single user or a *small* workgroup. You
don't mention how many users are trying to access/update data concurrently,
but if it's more than five or so, consider SQL Server 2000 Developer edition
which is very reasonably priced, runs on both server and workstation
versions of Windows and includes GUI administration tools.

--
Peter [MVP Visual Developer]
Jack of all trades, master of none.

Ehsan
Tue Aug 30 17:20:03 CDT 2005

Thank you guys.

I can't redesign my app because it is nearly finished and everything is now
designed to work with Access.
What I really wanted was a way to understand when the connection is becoming
available again. It is true that multiple threads can't load from an mdb
database
at the same time, but I was thinking about putting all my requests in a
queue and
considering them one by one. Is there anyway to see if the connection is
availabe?
I tried the connection's StateChanged event, but it doesn't tell me if I can
continue my operation. (By the way, it seems that the connection's State
property currently can only report whether the connection is open or closed,
not anything else, is that right?)
Thank you again.

William
Wed Aug 31 12:15:11 CDT 2005

It's too bad you didn't do more research before you committed to using a
home/small office database to implement a serious application. You're trying
to deliver coal with a bicycle. Jet was never designed to be used in an ASP
environment. The problems you face now during testing will pale in
comparison to the problems you (and your customers) will face when you go
into production. It's true that a JET database file can be accessed by many
users--a number of users far less than SQL Server or any serious DBMS.
However, every aspect of the application needs to be written to take JET's
limitations into account. You can't (for example) fetch all of the rows of
the table(s) or expect the engine to perform complex JOIN operations and
still support multiple users. Actually, JET was designed to support multiple
users by permitting each user to have their own JET engine running on the
client system. In an ASP application, you have one JET database that's asked
to perform operations on its own. Because (by default) JET delays writing to
the database file until it's idle, when the load picks up it's never idle.
If you disable this feature, JET blocks all operations while writing thus
slowing down or stopping other access.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Ehsan" <Ehsan@discussions.microsoft.com> wrote in message
news:2FC987C2-3B0A-4BB4-9C76-199059A8344D@microsoft.com...
> Thank you guys.
>
> I can't redesign my app because it is nearly finished and everything is
> now
> designed to work with Access.
> What I really wanted was a way to understand when the connection is
> becoming
> available again. It is true that multiple threads can't load from an mdb
> database
> at the same time, but I was thinking about putting all my requests in a
> queue and
> considering them one by one. Is there anyway to see if the connection is
> availabe?
> I tried the connection's StateChanged event, but it doesn't tell me if I
> can
> continue my operation. (By the way, it seems that the connection's State
> property currently can only report whether the connection is open or
> closed,
> not anything else, is that right?)
> Thank you again.
>

Ehsan
Wed Aug 31 17:15:01 CDT 2005

Thank you William.
I know.
The idea of, say, five hundred clients trying to connect to a database would
inspire an ASP project. Although I may not know ALL the things of
programming, but i've got a couple of years of experience to know that in an
ASP project there are situations where ten thousand clients try to connect to
your site( and perhaps a lot more than that. ) In that case, you are quite
right.
Let's shade some light on things. This is a CyberCafe management program.
It's got to be able to create user accounts, add credit to them, and then
deduct credit based on the time the users are using the client computers.
500 computers trying to connect(at the same time)is the worst situation that
may happen in such a place! it is almost impossible. I know that Access CAN
handle such a thing. SQL is too big for it. Version 2 of my program, written
in Visual Basic 6.0 and using ADO could perfectly handle everything, although
it was never tested with more than 35 computers. So this one, well, it's
gotta work. There are no CyberCafes in the world that have more than 450
computers, you know! And never happens for those computers to connect to the
Server exactly at the same time.
So please tell me about the Connection and when I can find out it's become
available.

Thanx in advance.

WJ
Wed Aug 31 21:30:54 CDT 2005

"Ehsan"
> Although I may not know ALL the things of
> programming, but i've got a couple of years of experience to know that in
> an
> ASP project...Visual Basic 6.0...could perfectly handle everything...

You seem to be an amateur programmer !!! No, I mean "...VB can still
perfectly handle..." the world in the 21st Century ! I rest my case !!!

John Webb

Bob
Wed Aug 31 23:18:55 CDT 2005

WJ and Bill:

>You seem to be an amateur programmer !!! No, I mean "...VB can still
>perfectly handle..." the world in the 21st Century ! I rest my case !!!

>John Webb

Is that realy you John??



Gosh. Why so harsh? I'm completely apauled. Even discussed. This is a
programming lang, not your first born. I didn't think Ehsan's response
warented that. Or do you just think your sucking up to Bill?



Although I agree that maybe he should have scaled it to an SQL Server, SQL
Server Express 2005, or at least MSDE, it doesn't mean there isn't a
solution.


WJ:

>Access is a single user database, it may not be able to handle
multi-threads
>asynchronously

http://office.microsoft.com/en-us/assistance/HP051868081033.aspx

Bill:

All he's looking for is where to find the stop light with his bicycle load
of coal. There is no solution for him? I thought I read an article saying
somethig about ODBC and queing threads but you have to wait sometimes as
much as 5 mins to see your data in the DB

Ehsan:

Are you realy trying to use an Access DB behind an aspx app using JET and
OLEDB to connect? How long does it take for the customer to walk from their
station to the cash register?

Bob

Ray
Thu Sep 01 02:59:46 CDT 2005

Ehsan wrote:
> Thank you William.
> I know.
> The idea of, say, five hundred clients trying to connect to a database would
> inspire an ASP project. Although I may not know ALL the things of
> programming, but i've got a couple of years of experience to know that in an
> ASP project there are situations where ten thousand clients try to connect to
> your site( and perhaps a lot more than that. ) In that case, you are quite
> right.
> Let's shade some light on things. This is a CyberCafe management program.
> It's got to be able to create user accounts, add credit to them, and then
> deduct credit based on the time the users are using the client computers.
> 500 computers trying to connect(at the same time)is the worst situation that
> may happen in such a place! it is almost impossible. I know that Access CAN
> handle such a thing. SQL is too big for it. Version 2 of my program, written
> in Visual Basic 6.0 and using ADO could perfectly handle everything, although
> it was never tested with more than 35 computers. So this one, well, it's
> gotta work. There are no CyberCafes in the world that have more than 450
> computers, you know! And never happens for those computers to connect to the

You obviously havn't seen the Easy Internet Cafes in London and New York
have you? :)


> Server exactly at the same time.
> So please tell me about the Connection and when I can find out it's become
> available.
>
> Thanx in advance.

WJ
Thu Sep 01 14:07:47 CDT 2005

"Bob" <YeahRight@nospam.net> wrote in message
news:PivRe.27074$FL1.5383@trnddc09...
>
> Is that realy you John??
>
Yes. I am always "John" the Baptist by birth!

>
>
> Gosh. Why so harsh?

No. I was trying to give Ehsan the most honest anwser to the best of my
knowledge at first. Ehsan never mentioned that he is still doing VB 6.x and
he attempted to go into asp.net forums for help, which has nothing to do
with ASP and VB at all. At least, the fellow was not very honest upfront (to
identify his system platform).

> ...Or do you just think your sucking up to Bill?
>

Like you, I am a frequenter to help myself and hopefully I could contribute
my own once in a while (may be if I am good enough). I don't suckup to
anyboly here as you think. In fact, I know no body here and nobody here ever
gives me a penny ! You are very wrong indeed ! You must have been
so....???....

In general, I am a person with "thick skin/face". I scare of nobody but
God! If someones here are kind, I will respect them. Otherwise, I just
ignore them! In short I am not obligated to anything here...OK!

>
>
> Although I agree that maybe he should have scaled it to an SQL Server, SQL
> Server Express 2005, or at least MSDE, it doesn't mean there isn't a
> solution.
>

You know, I have been encountering so many types of managers who just say
"well, VB can just do the job and so on and so on...", some of my managers
did know a little of VB and ASP and PC to be dangerous, so they decided to
do it with asscess and vb and did not realize the impact later on. I do hate
these peoples and I hope they should not make any decision about computing.
Let the true developers to make IT decision. Shoddy works always cause
headache.

I hope I explain well.

John the "Webb"

Ehsan
Thu Sep 01 17:20:02 CDT 2005

Yeah!
I think I've made an upheavel here!
First of all, let me re-state:
THIS IS NOT AN ASP(+.net for that cutting-edge guy!) PROJECT!
And, I'm not using VB6.
I am telling you, an Access DB CAN handle it if I send all the requests to
one thread and have it process the messages and put them in a queue.
And that wouldn't take more than 15 seconds in the worst cases.
I've got the answer and am telling you the result. I hope my question and
its result would be useful for everyone.
Thanks to all who took their time answering me.