Data Providers

Microsoft Access

Why?

We have chosen to use Microsoft Access databases for the following reasons:

• MS Access database are very portable and do not require "installation". They can be distributed, moved, backed-up, etc. very easily.
• MS Access databases can be operated successfully by users with limited technical knowledge.
• MS Access databases can be distributed without excessive costs and license fees paid to Microsoft (or other database authors).
• MS Access databases are well suited for use with ASP.
• MS Access databases are well suited for small-to-medium sized business applications and on web sites which receive moderate traffic.

Understanding its Limitations

MS Access was designed by Microsoft to provide basic database tools and funtionality to small-to-medium sized businesses. It is ideal for use in intranets or local network environments. It is also suitable for modest application on the internet.

Microsoft has imposed limitations on the Access database and its related drivers/providers. These limitations are designed to prevent problems not cause them. If your database suffers any of the following problems, then perhaps it's possible you need to consider up-scaling to a more robust database format.

Among these are the following:

The error message "Too many client tasks" occurs when more than 10 applications (executables) running on the same machine try to use the Jet database engine.

There are workarounds for this problem in intranet environments but when a database is in use on the internet then:

• this problem should rarely occur; and...
• if it does, it is a sign that the database driver on your web server is suffering from heavy loads (use a different driver, or upscale the database).

The database cannot handle more than 255 concurrent connections.

This topic is very complex and many programmers will argue that this number is misleading. Under the right conditions, if lock-type is managed carefully and each of the concurrent users are connected to different resources/tables in the database, and the users' connectivity is properly managed via OLE, then 255 distinct users can be connected simultaneously to the MS Access database. (As discussed in MS Knowledge Base article: 176670.)

However the number of available concurrent connections drops significantly when multiple tables are in use by a single connection and if the data is not sufficiently locked while communicating with the data.

This number of course does not represent "people"...it represents "users" which is often a different concept altogether. In a web-based application each connection to the database can be opened, queried, and closed within a few milliseconds, the number of "people" which can browse the web site simultaneously is not limited to 255.

In conclusion: this particular limitation is a concern but shouldn't worry you unless your web site consistently receives a significant amount of traffic. Perhaps more than a few thousand visitors per day.

Sean Nicholson of www.informit.com (Article) has this to say:

"Microsoft lists 255 as the maximum number of concurrent users to an Access database. This means that only 255 users can actively interact with the database at the same time. This might be theoretically true in Microsoft's labs under the ideal circumstances, but the reality of working with an Access database is that performance falls off sharply when more than 25 or 30 concurrent requests are made. This means that although Access remains suitable for small Web applications, those that experience growth where they are experiencing more than 25 concurrent connections should consider upgrading to a more robust database."

MS Access 2000 and 2002 are limited in size to 2 gigabytes.

(Access 97 is limited to 1 gigabyte.)

This limitation is very real but again we recommend that it's not an immediate concern unless you are building an enterprise-level application. The largest databases we have seen in use by our customers are less than 100 megabytes; those databases contained many thousands of records/items and hundreds of user accounts. Even our most demanding customers are not in immediate danger of reaching this limit.

Microsoft has made significant performance enhancements with each upgrade of MS Access and its related drivers/providers. Microsoft has recognized that MS Access is a popular tool among web-based database applications and they have responded by expanding the scope of MS Access and improving the tools and API's used to connect to and manage communication with the database. For example, the "Microsoft.Jet.OLEDB" provider (which became common in 2000) is more reliable and more powerful that the "Microsoft Access Driver {*.mdb}" (which was common in 1995-97).

MSSQL and SQLExpress

Why?

Note: In most cases, we suggest that customers should use the MS Access database that ships with 20/20 DataShed and should select "Microsoft Access" as their data provider on the "Setup" page of 20/20 DataShed. Under some circumstances, our customers have chosen to use MSSQL or MSSQLExpress with 20/20 DataShed. They have done this either to please their personal preference or to accomodate websites which experience extreme traffic and require the performance benefits of the MSSQL engine.

As of version 1.1.1, 20/20 DataShed can be installed and configured with a MSSQL database with greater ease. This is different from previous versions whereby 20/20 DataShed had to be installed first with the Microsoft Access database which could then be "upsized". We have implemented new tools and improved the setup process dramatically so that 20/20 DataShed can use the MSSQL database "natively" We have done this for two reasons:

• This was a "next logical step" for 20/20 DataShed. Previous versions were capable of communicating with a MSSQL database; we just had to tackle the task of rebuilding the "setup" process.

• As Microsoft has recently released their suite of "Express" developer's tools we expect that many webmasters will grow more comfortable using the SQLExpress engine and the "SQL Server Management Studio Express" software. Both of these tools are now free and Microsoft has loosened the license restrictions significantly on these technologies.

Other Databases (Oracle, FoxPro, mySQL, others.)

If you wish to use 20/20 DataShed with other data providers, then please respect the following:

• We cannot provide technical support in such endeavors. The reasons are covered in our discussion of customization versus modification.
• The price of 20/20 DataShed remains the same regardless of the data provider you wish to use.
• The "Database Utilities" tools included in 20/20 DataShed are designed specifically for MS Access. Should you incorporate a different data provider, these utilities will be unecessary and unusable.
• How To Upsize to Microsoft's SQL Server: We have written this article to demonstrate how the MS Access database which ships with 20/20 DataShed can be upsized to Microsoft's SQL Server using the "Upsize Wizard" in MS Access.

Things to Consider When Using a Different Database With 20/20 DataShed.

• We recommend that you use the "Upsizing Wizard" in MS Access. To do this: open your 20/20 DataShed database in Microsoft Access and then navigate to the "Tools" > "Upsizing Wizard" menu item. The wizard will produce necessary files that you may then use to build tables, indexes, and relationships in a new database format (such as SQLServer).
• We recommend that you carefully maintain your database. Be aware that we may release upgrades to our product which may require alterations in the database's design. As such we recommend that you respect the architecture of the database and change it as little as possible.

Send Feedback