ASP.NET and Access as online database?

[James]

Hello,

I have a relational database well written if I do say so myself About
75,000 records in one table, linked to an examinations table with details of
the students exams etc.

I am thinking of putting results online, very easy to do and it works very
well with a login and security etc from an Access Membership provider. (I
have quite some experience in ASP.net.)

What I would like to know is the limitations of Access, how many people
could log into this database and read their results before it crashes/becomes
slow/wont let anyone else log in etc.

Access say the maximun users is 255 but is this using a frontend-backend
scenario and would this number increase if it is only a datasource on a ASP
webpage? Basicaly does it mean users as in connections?

Any advice would be very welcome

Many thanks
James

 

[mscertified]

If they are ONLY reading, it should be workable. If they need to update, I
would use another database. The max simultaneous users (connections) is much
less than 255, but since you will (hopefully) be opening a connection,
reading data and then immediately closing the connection, that should not be
an issue.
You can run into problems if you try to access the back end for maintenance
while online users are accessing it.
There are a number of products on the market that claim to be able to webify
an Access DB, you will find them if you use Google.

-Dorian

 

[James]

Hi

Thanks, I think I will move away from access as I will probably add pages to
update details and enter for new exams etc and will probably be getting
around 2000-3000 visitors loggin in per working week and editing stuff.

I guess a logical move would be SQL server as I have it installed with Small
Business Server 2003?

Thanks
James

 

[mscertified]

SQL Server would be the best choice unless you want to go with an open source
database like MySQL. You could even keep an Access front end for local
maintenance. If you have a small number of users you might be able to get way
with using MSDE which is free. It's a subset of SQL Server.

-Dorian

 

[Pat Hartman]

Upsizing to SQL Server can be trivial or terrible. When you move the be
from Jet to a database server, you may need to make some modifications to
your front end. The biggest is that forms should be bound to queries with
selection criteria to limit the data retrieved from the server. You can
retrofit this by creating a search form that opens the main form. The
OpenForm method takes a "where" argument that can be built from the
selections captured on the search form and Jet will attach this where clause
to the form's recordsource query when it sends it to the server so that only
the requested records will be returned.

Also find the article on Access client/server optimization in the KB.

 

[mscertified]

here's a couple of good links about Access on the web:

http://authors.aspalliance.com/PeterJohnson/JetAdvice.aspx
http://www.adopenstatic.com/faq/whyOLEDB.asp

-Dorian

 

[James]

Hi

Thanks guys I will look into this,

Cheers for all your help
James

 

[Tony Toews [MVP]]

SQL Server would be the best choice unless you want to go with an open source
database like MySQL. You could even keep an Access front end for local
maintenance. If you have a small number of users you might be able to get way
with using MSDE which is free. It's a subset of SQL Server.

Or even a medium number of users. One person stated he had 75 users
on a MSDE (SQL Server 2000) database.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/