Is Access a Suitable Database for a Web app

Should we use Access or sequel Server

[Red_Eye]

We currently have a piece of standalone software that is written in VB6 with an Access database behind it. We are looking at migrating this to a web based service for our customers to use on a larger scale. We are intending to use ASP.Net on a windows 2000 server. This seems fine so far however there has been some questions raise as to the suitability of access as a web database.

The reason that we would like to stay with access are that we would not like to pay the Sequel server Licence fees (This would increase the cost of our service). It would mean rewriting some areas of the code. It would require more testing (Only because we haven't used this software with a sequel server before).

Could any one give me some indication as to whether or not we should stick with access.
1. What are the pro's and con's of the 2 systems.
2. Has any one successfully used access behind a large website.
3. What are the practical limits for access with think that there could be anywhere from a 100,000 to 1m records in the database.

Thanks in advance for your help.

[davemarks]

Access can be and has been used by many a website. In fact a know a few busyish forums running on access and they seem ok.

The problem is access is limited. In that it can only do so many reads and writes at the same time.

It really depends on what sort of traffic you expect to be getting and what you will be using the database for. ie how often will you need to access it in your application

[lazerzubb]

From what i know Access can only handle 255 connections as maximum, and by the time you hit 200 you will be experiencing very slow database requests.

[Red_Eye]

Thanks for your quick replies. davemarks Could you sticky me a link to one of the sites using access. It would help me to cite and example.

[bateman_ap]

From experience I have found Access to be fine as long as you aren't writing to the database. For simple reads it seems to hold up very well, even in quite busy sites (+3m page views a month)

However as soon as you start writing to the database access really starts to fall over and you should look at SQL

[jimmykav]

Depends on a number of things:

1 Number of concurrent visitors
2 type of data access - how many writes v. reads
3 whether your site will be on a dedicated server or on a shared host supplied by a third party.

I have a tiny website that makes extensive use of access and is on a shared host. With only a handful of concurrent users the site slows down dramatically.

[Red_Eye]

Depends on a number of things:
1 Number of concurrent visitors
2 type of data access - how many writes v. reads
3 whether your site will be on a dedicated server or on a shared host supplied by a third party.

1. about 500 concurrent visitors (Thats not concurrent transactions)
2. I would way about 1 write for every 2 reads.
3. Dedicated server.

Also I just been reading about bout MSDE, a cut down version of sequel server that would allow an easy migration path to the full sequel server at a later date. The development cost and licence costs are much more attractive and certainly in the short term (2 years) would offer us all the scalability we need.

Has anyone had any experience of using MSDE

[jimmykav]

From what i can remember Microsoft does not allow MSDE to be connected to the Internet.

It is a capable small scale engine if it is legal to go that route. I take it that the machine in question will be controlled by you. I doubt that commercial prividers will install it if I am right about the restricted licensing.

[txbakers]

Access is no more a web database than FileMaker Pro claims to be.

It can't handle writes and updates for more than 6 - 10 users simultaneously.

mySql is a free alternative, which can handle the heavy lifting of web traffic.

If all you want is to serve up dynamic pages without too many writes, then Access will be fine. but if your site is truly data driven, then Access will crash quickly.

[Rhadamanthus]

The other alternative is to take my path. I'm currently using MySQL for my web database with every intention of migrating to SQLServer as soon as I can afford it. But since MySQL and SQLServer both support ODBC just fine, I have a clear and relatively painless upgrade path.

MySQL lacks some of the advanced features of SQLServer, but it's far better suited towards a web solution (multiple concurrent access, especially) than Access is. Even though it supports more, Access is really meant for one user at a time access.

[Gibble]

Access as a standalone client side database works as intended. Online it fails...miserably.

It can only handle a few ~10 simultaneous connections before slowing down.
When performing writes Access uses TABLE LOCKING which means when you are inserting or updating it locks the entire table, nobody else can access it.

SQL Server, MySQL, Oracle, and PostgreSQL all use Record locking and only the record that is being inserted or updated is locked.

I would highly recommend using PostgreSQL or MySQL if costs are a concern.

Access does NOT scale well to the web.

[txbakers]

Has anyone tried to use FileMaker Pro as a web database?

[powerstar]

If you use Access make sure you use the XP version. Much more stable.

[chris_f]

txbakers
Has anyone tried to use FileMaker Pro as a web database?

Ok ... Ok ... you win there. I really can't tell if your being sarcastic, however, you have got to be kidding. :)

Truth be told, I do know a web developer who tried this. It fell over almost immediatly. It bearly coped with 1 user.

Chris

[chicagohh]

If you have good code Access will handle *many* more users than the 6 - 10 often quoted. If you are just reading you have handle *lots* of users.

coveryourasp.com is an Access driven site with a decent amount of traffic.

I run 3 eComm sites on Access databases. My sites are not huge, but I run over $60,000 a month in sales with no problems at all....

MySQL is a great choice. SQL Server is better.

Just my experiences,

Joe

[txbakers]

Ok ... Ok ... you win there. I really can't tell if your being sarcastic, however, you have got to be kidding. :)

Not being sarcastic at all. I'm running into competition for my online DB program from an organization giving away free templates to FileMaker Pro, and claiming that it can work over the web as well.

I had a good laugh as well.

[Woz]

>Has anyone tried to use FileMaker Pro as a web database?

I have a collegue who uses FileMaker Pro exclusively. Several sites ranging from pure information through online sales to a very sophisticated site to service clients for a legal firm.

Back on Topic, I use Access to serve a LOT of pages per month, and I mean a real lot! There can be problems as database size increases but if you are careful in the pre-planning stage it can be done. For example, on one site, I use one database to serve the pages, and use a seperate database to collect click through stats. Also, the click through database automatically changes each month so it is only ever about 1Mb in size each month.

Obviously, as traffic gets to a certain point you need to migrate upwards, but if you plan well and Access can be your friend.

Onya
Woz

[Red_Eye]

Thanks for all your posts, they have been a great help. Essentially I think that we can use Access, however as the site grows we would need to move to SQL Server (To offer a more stable system). Therefore to offer the best migration path I think that I will use MSDE. According to Microsoft you can use MSDE in a production capacity. The only limitation is the amount of simulations users (It appears to have the same limits as access). MSDE seems to have been designed for our application. Starting off small and offering a clear migration path at a later date.

Thanks again for your help