SQL Server Desktop Engine

[Argovian]

I have just designed a Student Report DBMS using ACCESS 2002. There will be
a total of 50 users and probably about 20 users working on various forms at
the same time. There are about 400 students in total, each taking an
average of 6 subjects. I have read in a few books that the Microsoft Jet
database
Engine is unable to cope with more than 20 users - is this 20 users in total
or at the same time or?? If I use a SQL Server Desktop Engine, would it
solve this problem (if it is a problem)? do i have to redesign my database?
What about the SQL server? I need some up-to-date info quick!

 

[Roger Carlson]

The 20 user limit for Jet is a rule of thumb, not an absolute limit and it
refers to concurrent users. The MSDE has a much lower limit (4 or 5). It
is in fact a 'Desktop' engine, so it shouldn't be networked. SQL Server
would be the preferred solution, but will has greater hardware and
maintenance requirements. Another possibility is MySQL, which is much
cheaper (or free depending on how you are using it). Unfortunately, I have
no direct experience with this. Perhaps someone else here does.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Argovian]

thanks for the information - I have heard of MySQL and will investigate
further - I will put the database on the network for beta testing next week
and there will be at least 15 concurrent users inputting information in the
student forms. i have put in security measure such as only one user can work
on one form at any time and when a user has finished inputting information,
clicking the main menu button will first save the data in that form, close
that form and then open the main menu form. I sure hope it works smoothly
next week. Wish me luck!!

 

[Duane Hookom]

Whoa... hate to have you leave MS databases ;-). Consider looking at
http://lab.msdn.microsoft.com/express/sql/ SQL Server 2005 Express. I
believe it supports more users but doesn't support multiple processors like
MSDE would. It's worth a look see.

 

[Tim Ferguson]

i have put in security measure such as only one user can work
on one form at any time and when a user has finished inputting
information, clicking the main menu button will first save the data in
that form, close that form and then open the main menu form.

PMFJI but...

I really urge you to use a "split" database design, in which all the tables
reside in a single shared mdb file on the server, and all the forms,
reports, code and queries live in a separate mdb file, of which each user
will have a copy on the local hard drive.

In this way, there is no clash between users using the forms at the same
time; any record locking is (generally) handled automatically by Access
itself. You will also benefit from hugely better performance and, most
important of all, reduce the likelihood (sorry, for 15 users sharing an
Access file that should read inevitability) of file corruption.

Lots more information here in the NG or on the Access Web
<http://www.mvps.org/access> on splitting databases if you need it.

Hope that helps

Tim F