ConnectWeb NT - Using Access Databases

All NT web-hosting clients have the facility to use MS Access databases to add functionality to their Web site.
Note: AAPT will not create File or System DSN connections on the web server. All connections must be set up within clients' scripts. (Often called "DSN-Less" connections) Reasoning for this can be found here.

Basic example: 

 <%
 ' first create a recordset object
   set myRecordsetObj=Server.CreateObject("adodb.Recordset")
 ' next set up a connection string, specify driver and path to DB
   connectme = "Driver={Microsoft Access Driver (*.mdb)};" & _
   "Dbq=" & server.mappath("/../database/samples.mdb")
 %>

What applications can Access Databases be used for?
There are many things you can use Access databases for. Popular uses are for tracking users to enter a site, and for creating a guestbook type application. A good rule of thumb is to use Access databases for small, non-critical databases.
There are some points that you should be aware of when using Access databases that may have a very significant impact on your site.

  • Access databases are saved as text files, which means they can be subject to corruption
  • Access databases have a connection limit of approx. 20 users, which is not alterable. If your site is expecting many concurrent users, you should not be using Access databases on heavily hit pages. This is demonstrated when the error "ODBC "too many client connections" appears.
  • If you think that your database may be affected by this, we would suggest that you consider using a MySQL database.

Important:Access 97 vs Access 2000
There is a known incompatibility between Access 97 and Access 2000 database formats.
Microsoft changed the format of the database files in Access 2000 to accommodate the full unicode set, which has unfortunatly caused incompatibilitys between the two.
Due to this, all customers hosted on Windows 2000 are only able to use Access 2000 databases. This is an unfortunate side effect which cannot be rectified at this point as Microsoft has not released backwards compatible drivers for Windows 2000.
As of 1 Dec 2001, Nt-hosts 1 thru 5 are Windows NT4, and NT-hosts 6 up are Windows 2000 based.
There are subtle differences in the way you connect to your database on Windows2000, please refer to the demonstration scripts for the correct syntax.

AAPT has constructed a small set of demonstration files available for download, which demonstrate how to view, add to, and delete records from an Access Database.

Please ensure you download the correct set for the host your web site resides on. These files can be obtained here: AccessDBdemo.zip (Access 97),, AccessDBdemo2k.zip (Access 2000) and are demonstrated at http://nt-host2.hosting.connect.com.au/dbdemo/

It is highly recommended that all database files be put in the provided "database" directory (located outside the web root). This directory has been set-up with database security, and usability in mind. AAPT will only provide very limited support for clients that are not using this provided directory. This is also the only directory with write access available.

Please be aware that AAPT will not provide support for structuring SQL queries in customers scripts. If you are having problems with syntax, we suggest you reference the sample scripts, or look online for help. There are many online resources for clients to use.

Some excellent resources that provide examples of using DSN-less connections and/or Access Databases are:
  Active Server Pages - Access Database Tutorial
    http://www.web-savant.com/users/kathi/asp/samples/database/samples.html
  ASPLearn.com - DSNless connections tutorial
    http://www.activeserverpages.com/learn/dbopen.asp
  ASPLearn.com - All Database tutorials
    http://www.activeserverpages.com/learn/database.asp
  Article: System DSN or DSN-less Connection?
    http://www.4guysfromrolla.com/webtech/070399-1.shtml


For information on what the error codes that appear in your browser mean, Always try the Microsoft Knowledge base first.
http://support.microsoft.com/search/


Understanding the server.mappath command.

Many people have trouble understanding the concept of how the server.mappath command works. Primarily you must remember that the path you are describing to the server must be in "web" format, which is why the slashes used are forward-slashes (/).
The "web-root" of your server is:
a) In a directory sense, the "htdocs" directory
b) In a web browser sense, what you view when using http://www.bob.com/this, where "this" is a filename, and not another directory.
c) In a scripting sense, referred to as: server.mappath("/")

The portion of the connection string in question is this: 

	"Dbq=" & server.mappath("/../database/samples.mdb")
Step through the red text section by section, like so:
  • Start at the web-root (/)
  • Go up a directory (../)
  • Go down into the database directory (database/)
  • In this directory, use samples.mdb (samples.mdb)

Why will AAPT not set up DSN connections??

  • DSN Connections are stored in the registry of the web server machine. This means that the registry must be read EVERY time the connection is used. This results in the connections being somewhat slower.
  • DSN-less connections are portable. This means that if you write a script with DSN-less connections, you can pick it up and move it to another NT web-server at any time, without changes.
  • DSN-less connections can be modified at ANY time by the webmaster, without needing assistance from the server administrator. (Eg: when adding a new database, or in the case that a database is re-named)
  • In the case of a catastrophic failure on behalf of the web cluster, we cannot guarantee that a DSN would be restored at time of rebuild. Priority would be with restoring all web services, which could mean an extended downtime for a site requiring DSN connections (whereas a site with DSN-less connections would work immediatly upon file and web service restoration.)





Copyright © AAPT Limited