Access app kills SQL SERVER after upsizing

[roypython]

HI.
Your help is very appreciated.

*I upsized ACCESS 2002 (tables only) to use SQL SERVER 7.0 as backend.
(linked tables).
* Almost all the ACCESS forms are bound type forms.

Now, I have two problems:
1. Other applications (not ACCESS ones) that use different dbs on the same SQL SERVER , are slowed down / halted.
2. My SQL SERVER license is limited to 8 "user counts". When there are 8 instances of the ACCESS app, any additional connection request is refused (from any app).
(I can't afford buying more "user counts").


I suspect that beacuse the forms are bound, it hold connection open as long as the form is used.
Also, each ACCESS app instance, opens its own connection.

The solution that I see, is to use unbound forms(disconnected), and opening the connection in code using connection pooling.
Would you recommend on this solution?
do you have any suggestions?

Thanks a lot
Roy

 

[roypython]

Hi Pat.
Thanks for your advice. You right, changing it to unbound forms is a big task .
I will give it a go on MSDE MSDE.
As to your question, I'm upsized it because ACCESS had 20 concurrent users, it kept on crashing, loosing info, and to run terribly slow.
I had the option of trying to optimise, but still, ACCESS is just not reliable enough and has severe limitation derived from it being a file-share architecture.

 

[roypython]

Hi Pat.
Originally the ACCESS app wasn't split. 20 people were using the same mdb file (which was UI as well as db). I know, it sucks big time and a major source of problems.
However, even if it was splitted, it is still not reliable enough for my client's needs.
That's why I chose to upasize.
Regarding performance:
I was able to improve performance siginificantly (from 20 seconds to 1 second) in situation where recordset was opened in code, and then, find or filter(can't remember) method was called upon it.

rs.open SqlQuery
rs.find("somthing")
rs.close

Instead, I put the "somthing" filter in the query itself( "Where" clause), and the improvement in speed was unbeliavale.

 

[roypython]

Yes.
Sorry about that.