Fog Creek Software
Discussion Board




Really, how good is MS Access for web

I am a programmer just starting out on web development. I am just wondering how good  MS Access is for the backend. I am developing sites in VBScript and ASP.

But I seem to hear that Access crashes very often and cannot support multiuser environment stuff very well. I know MS SQL server is a better choice but none of my customers can afford the cost.

Please let me know your experinces with Access as a backend. I really appreaciate all feedbacks etc. Thanks.

suhu
Friday, July 26, 2002

You should determine this project's particular technology needs before you start deciding what technology is "good" or "bad". 

How many database tables do you expect to have?
How large do you expect your tables to get?
What types of queries do you need to execute?
What is the load you expect your website to handle?
Will most of your web traffic result in DB queries?
As far as multi-user concerns, look into connection pooling via ASP, and see if that handles your requirements.
etc etc etc

Also, this does not have to be a big concern right now.  Make sure you code to proper ODBC/ADO/ANSI-SQL  (or whatever the latest data access buzzword is)  As long as it's generic SQL, when you switch backends, you'll simply change your param file with the new database specs, and be in business.

Good luck.

Bella
Saturday, July 27, 2002

I have only seen Access crash when someone does something stupid(Cartesian Product, Text Search on an unindexed field witha million plus rows, etc...) make sure your program doesn't do this, and you should be ok.

Here is the real key question:


do you want/need business logic on the DBMS, why because if you dont, your answer is mySQL, but if you do go with Access.

The big limitiation for access is not types, or tables but concurrent connections, and data locks!
Access (at least access 2000) could handle a maximum of 64 concurent connections, the other limitation (I believe is that access has pessimistic page level locking(I believe that is what it is called), so if one person is changing a record, access to that record is disabled,but other records on the same "page" may also be disabled!

I am not sure what the numbers etc.. are for MySQL, but I am pretty sure they are better!!!!
Also check max table rows and columns for access make sure they are big enough for what yo need to do!

Another big advantage of Access is that you can create functions in VB, which extend SQL, this is not available in MySQL (at least not in VB)

Another big advantage with access is that a lot of database functions can be done via either SQL, or DAO, or OLE automation.

Daniel Shchyokin
Saturday, July 27, 2002

I built an intranet application in ASP using Access back in 2000.

Access was supposed to only be used during development and the application was supposed to be migrated to SQL Server. The migration never happened and 2 years later it's still running under Access.

In the last 2 years Access has never crashed or corrupted the data once, and there are hundreds of thousands of records in the database now. It get's plenty of use too, about 200 users use it at the same time all day.

I'm not a big fan of Access but it's proved to be find for this application.

Matthew Lock
Saturday, July 27, 2002

Daniel,

Disclaimer:  Take my post with a grain of salt, b/c this information stems from my experience with VB4/Access95 almost 7 years ago, which is the last time I really programmed a production system with Microsoft products.

Microsoft website seems to indicate that Access suuports record level locking, not just the old 4k page level locks.

But to clarify, pessemistic/optimistic locking only applies when using recordsets.  If he is going to issue straight CRUD SQL, (as when using connection pools)  then this does not apply.  In that case, you are on your own for enforcing data integrity.  I am not sure of the preferred method of doing data access from ASP pages, but would like to hear from people who code in ASP.

Anyways, "Pessimistic locking"  does not have anything to do with the granularity of the locking (page, record, etc).  "Pessimistic locking"  refers to WHEN the data is locked. 

Pessimistic will lock the data (be it page or row) as soon as you actually start updating fields in the recordsert, and will be released when you actually issue the update. 
Upside = You will never be denied your update. 
Downside = data is locked for a longer period.

Optimistic locking would only issue the lock when you issue the update. 
Upside = data is locked for a shorter period, but
Downside = a user's update could fail if the data was changed since the original select.

Bella
Saturday, July 27, 2002

Why don't you look at MSDE.  It is basicaly the SQL engine.  There are some liscensing issues but not too bad.  We are using it for our App right now and loving it.

Matt Watson
Saturday, July 27, 2002

My experience with Access as back end for a web site are limited to using this site: http://www.aenor.es, most of the time, when you do a query you get an error back.

Just now I gave it a try and this was the result:

Microsoft OLE DB Provider for ODBC Driverserror '8007000e'

[Microsoft][ODBC Microsoft Access Driver] System resource exceeded.

/scripts/buscar.asp, line 8

IMHO Access is great for making user interfaces to databases that aren't that complex and which are not going to be used by many users, for something like a web site, I think it is pretty much useless.

Andres
Saturday, July 27, 2002

Access is fine, its the code around the application that is usually the problem. A developer usually does something dumb and then blames the tools.

Access is fine, and in my opinion enourmously underated.

Lately I've been using Oracle and before that SQL Server, most of the time I just think that it would have been better with Access (storage limitations aside).

Alberto
Saturday, July 27, 2002

Check out the www.whirlpool.net.au forums.

This is the major broadband forum site in Australia, and is run off of Access and Cold Fusion.

For the most part, it runs just fine.

This is by no means a simple database, there are a lot of subtle things that Whirlpool does and keeps track of.

Evan
Saturday, July 27, 2002

If you need a real RDMS use PostgreSQL.  You can connect to it VIA and ODBC Drvier, it has all the features of an enterprise RDBMs (as opposed to the MySQL limitations) and it is Free Software.  You can run it on Windows under Cygwin

Adam
Saturday, July 27, 2002

Thanks guys for all the help. I definately am more informed after reading all the post.
I think that Access should be able to handle most of the stuff that I need for my clients at the moment.

suhu
Saturday, July 27, 2002

Bella, pessimistic locking does have to do with granularity,becuase if a Database locks a whole page while waiting for a transaction to fail, attempted transactions on other records will also fail!

Daniel Shchyokin
Sunday, July 28, 2002

excuse me change "waiting for a transaction to finish" to waiting for transaction to finish"

Daniel Shchyokin
Sunday, July 28, 2002

re: "I looked it on on MS Web-Site"
Also, bella do not confuse real world with microsoft's web-site!

Daniel Shchyokin
Sunday, July 28, 2002

First, you can’t use ms-access to server a web site. Shame on all of the posters here to not distinguish this!

I will repeat this again: You cannot use ms-access to serve a web site. You can’t use the forms, the queries, or the reports. In fact, none of ms-access will be used here. In fact, you don’t even have to install ms-access to run and use the data files created with ms-access/jet.

We are talking about using the JET engine. In other words, the only thing that ms-access will be used for here is to create the data tables and relationships. Once this is done, then you don’t need ms-access at all. So, one should not be asking if ms-access is good for the web, since in fact ms-access CAN NOT BE USED for the web. (xp version does have some web stuff now...but lets try and keep confusion down to a min here!).

Ms-access thus does NOT run when you use the web server, and it is not required. In addition, the last two versions of ms-access can run WITHOUT THE USE OF JET!

Ok, so now the real question here is really how good is JET?

The JET database engine that ships with Ms-access is used in all kinds of applications. The very successful Simply Accounting package is based on JET. So is the CityDesk web content management system that used THIS SITE!.

Even Microsoft right now has at least 25+ or MORE products that rely on jet.

Some of them are:
Ms-access (***I F *** you choose to use jet, and not sql server)
Windows NT (directory services)
Microsoft Money
Internet Information Server
Index Server
Microsoft Project.

A Jet variation even serves as the message store for Exchange Server.

I have no doubt that Jet will probably survive for years to come.

Most of the products listed above are now being migrated to sql server (or the free MDSE engine from MS). The Visio tools from MS require the MDSE engine now for example, and I believe never did use jet).

By the way, ms-access has shipped with a sql 7 compatible database engine for the last two versions. This engine is free from Microsoft, and is 100% computable with sql-7 server. Thus, with ms-access you can use a native OLEdb connection to a the server and NOT use jet. This means that the connection is the same as what a web site, or VB will use (VB, C++, asp web pages all use the same library now!).

MySql is a great database engine for a web server. It however does not even have basic things like referential Integrity!. Heck..even the little JET engine from MS has this. This really means that MySql is good for serving data, but it is not ready for using with a business application. If you delete a record in MySql, then you have to write code to actually delete any related records (yikes..this is like going back to old days of dbase II software 15 years ago!). Hence, just be aware that MySql cannot enforce any relations and you have to do it all by code.

I have to wonder why MySql is even called a relational database when it has no way of managing relational data for you?

There are some add ons from InnoDb that start to address these horrible shortcomings of MySql. Again, my point here is just to be aware of these limitations. For most Unix programmers a web site used to be a bunch of pearl scripts and text files. When they get their hands on something like MySql, it is a real revelation. However, MySql is not ready for use when writing a business application of any complexity.

If you need a free sql database engine with nice server side code, and one that enforces RI, then the free database engine from MS is good bet.

Jet is a fantastic little embedded database engine and is easy to setup (and it has RI). For a light weight web site it is just fine, but the free sql server engine is also available, and the only reason not to use it is that you have to learn another complex product.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Sunday, July 28, 2002

Wrong.  You can have pessimistic or optimistic locking whether you are using page or record level locking.  Therefore, these are 2 independent settings. 

Bella
Sunday, July 28, 2002

Another free alternative is PostgreSQL. It has all of the ACID semantics you want, and even referential integrity. It won't give you OLAP services, but if you don't know what they are, most chances you don't need them (or even if you did need them, probably wouldn't have used them anyway. And they're not free either).

Apart from that, PgSQL will give you everything you need for no money.

Ori Berger
Sunday, July 28, 2002

bella,
yes your latest post is true, but on some dbms you have no choice but to use page level!

Daniel Shchyokin
Sunday, July 28, 2002

A query for Matt Watson - just wondering what the licensing issues were with MSDE, which we are looking at using.

With regard to Access, I've found it surprisingly robust, given what some people say about it. However, I have found that it can get confused when running some update queries, especially ones involving VBA functions or even built in functions - eg I've seen it deny the existence of the Dlookup function. I've also seen it fail to run a query at all using the "Execute" method, but without reporting any error.

On the issue of data corruption, I've personally never lost any data with Access. I have had databases reported as corrupt, usually after a machine crash, but the recovery tool has always managed to patch things up.

Overall, I think Access is a really useful product, but I've seen enough odd behaviour from it that I wouldn't completely trust it.

Andrew Simmons
Sunday, July 28, 2002

I have not had any problems with Access/Jet, but performancewise, MySQL on BSD kicks it up and down the block on lowspec hardware.
As stated, it all depends on what you are serving and to how many.

And as someone on the Halflife team aparently wrote in a comment in the games source: "If it works, it works"

Joakim Kärrström
Sunday, July 28, 2002

Just one more voice for MSDE.
While functionality of the database engine in MSDE 2000 is similar to the database engine in the SQL Server 2000 Editions, the size of MSDE databases cannot exceed 2 GB (but you can have as many of those 2GB databases as you like).
MSDE 2000 has a concurrent workload governor that limits the performance of the database engine when more than 5 batches are executed concurrently, but this thing is FAST!

Look for it on your Office/Access/Visual Studio/SQL Server CD (separate install I believe).

One thing I never got straigtened out: It is clear that you can distribute it for free with your own client apps, but are you allowed to use it as a Web backend? I know very many sites do, but it is still unclear to me.

Just me (Sir to you)
Monday, July 29, 2002

Okay about MSDE liscensing:
First off a little history about our setup.  We are a retail outlet with about 5000 stores nationwide (I will let you figure out which one).  We use MSDE as the backend database to our Web application.  The web app controlls all of the managerial functions of our store.  It is written in JSP (not my choice), and hits the MSDE database.  We have one instance of MSDE running in each one of our stores.  We had consultants here from microsoft and they basically told us that as long as we were not redistributing to other customers (which we don't do) we were fine. 

From microsoft's site:
"You can also build MSDE 2000 into an application that was built with Microsoft development tools, such as Microsoft Visual Studio® and Microsoft Office Developer Edition, and distributed royalty-free. This allows developers to build enterprise-class reliability and advanced database features into their desktop applications"

So basically my answer is that I am not completely sure about the liscensing for distributing apps but if you are developing in house you are probably ok.  Keep in mind one major snag... if you are connecting to or "accessing data resources" on a full blown SQL Server box, you must buy a SQL server CAL for each MSDE Device.  Now if your company has an enterprise liscense then you are fine but if you don't you may have to buy that.

The best advice I can offer is to try and get ahold of your local Microsoft sales office and ask them.  They will be happy to know you are using it. 
BTW here is the site for MSDE on microsoft: http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

Matt Watson
Monday, July 29, 2002

One reason not to use MSDE: if the site is on a budget, and is going to be hosted on a webhosting company on a shared hosting plan, then you will end up having to pay to pay a lot of more $$$ a month if you go with MSDE.

Most ASP hosting packages allows you to upload and use an Access mdb-file, but none that I know of lets you use MSDE. Instead, you have to pay - at a minimum - 10 to 30 US Dollars a month extra for the full fledged the SQL Server option.

From what I have heard: if the code to access the Access mdb is well and efficiently written, and used mostly to read data, and if there usually will be no more than 5-10 concurrent open db connections, then Access should be sufficient. If there will be a lot of concurrent writing and updating to the db, then Access propbably is not mean enough for the job.

Janne
Monday, July 29, 2002

One major issue with using MS Access is doing backups. You can't back up a Access database when anyone is connected to it (as opposed to SQL Server/MSDE that can; I have no experience with MySQL, Progress etc). This is not a problem if you only use the database to read data from to display on your website, but when you are going to write to the database you'd like to make regular backups. I definitely advise against using Access for any website that involves financial transactions because of this.
But apart from that I would say that there is no problem with using Access for small and medium scale websites, specially if you use connection pooling in your application and don't return large (1000+ rows) resultsets.

Jacco
Tuesday, July 30, 2002

Janne wrote:

"One reason not to use MSDE: if the site is on a budget, and is going to be hosted on a webhosting company on a shared hosting plan, then you will end up having to pay to pay a lot of more $$$ a month if you go with MSDE."

Not always. As an example, see http://www.nexpoint.net/w2kspecial.php . I have not worked with this outfit (Anyone did? I'd like to hear of your experience), but as you can see, 30$/yr for SQL2000 is not what I would refer to as $$$.

Just me (Sir to you)
Wednesday, July 31, 2002

Hello People. I want to ask if it´s reliable to implement JET database access using an ADSL VPN Connection.
Thats is: the mdb file is in one computer while the client is accessing it using the VPN connection using UNC or a Network Mapped Drive.

The doubt arises to me because I don´t understand y for a simple query the entire MDB file have to be readed in the client computer or the JET engine uses it internal indexes al read de MDB file as needed.

I hope you can forgive me by my poor english.

Thanks in advance. Bruno

Bruno Masotta
Thursday, April 22, 2004

*  Recent Topics

*  Fog Creek Home