Database-ASP-ConnectionTypes

Tutorial: Database - ASP -
Access Database Connection Types

Dr. Thomas E. Hicks
Computer Science Department
Trinity University

Access Database - Security.mdb

For purposes of discussion, let us suppose that an Access Database, called Security.mdb. We shall place this file several places, but at the moment, let us assume that it initially resides at the root of drive C. (See Below!)

In order to keep our example as simple as possible, our Security Database contains only one tables, (See Below!)

My first thoughts were to include fields Name, No, Password, and IDNo inside the Users table; at first glance this appears to be ok, but experience with multiple databases has taught me that some databases use No, User, Date, and Password as reserved words. (See Below)

No and Password have been renamed in the table below.

The Users table may be seen below.

The Security Database above was is ultra trivial application which had only one database table. Relational databases often have many tables; this is true of Access databases as well. This database may be downloaded.

Security.zip

The ODBC Connection #1

ODBC is an acronym for Open Database Connectivity.

One of the connection options that shall be discussed in this tutorial is the ODBC connection type. When a database is installed on the web server, the necessary ODBC drivers are also installed. During the Microsoft Office install of Access, the ODBC drivers are automatically made available to your web server.

If the web server does not have the database installed, then the ODBC drivers can be downloaded and installed; this process differs from database to database and is beyond the scope of this tutorial. Microsoft Access and the appropriate ODBC drivers have been installed on the server illustrated below.

Configuration of the ODBC applet is almost the same in every environment.

Launching The ODBC Applet From Windows NT(4) and Windows 98

The ODBC applet can be found in the control panel. Using the mouse, double-double click on ODBC. (See Below!)

Launching The ODBC Applet From Windows 2000 Pro, Windows 2000 Server,
Windows 2003 Server & Windows XP Pro

Somewhere in the Start Menu will be some Administrative Tools; you may have to configure your Start Menu to make these visible. Select Data Sources (ODBC) to launch the ODBC Applet. (See Below)

Configuring The ODBC Applet To Connect Access Database Security.mdb

All connections must be placed in the System DNS tab in order to be accessible by the Internet. Using the mouse, select the System DSN tab. (See Below!)

Using the mouse, select the Add button. (See Below!)

Using the mouse, select the MS Access Database Driver. Using the mouse, push/select the Finish button to configure the new ODBC client. (See Below!)

Enter Security for the Data Source Name. Enter Access Database Users Security for the Description. Using the mouse, select the Select button. (See Below!) The Data Source Name may not match any of the other Data Source Names on the web server; it must be unique. Make note of the Data Source Name [Security]; it too will be needed later. The Description may be anything you choose.

Select the database to be associated with this Data Source Name. Using the mouse, select Drive C. Using the mouse select Security.mdb as the Database Name. Using the mouse, select/push the OK button. (See Below!)

It is now time to save the new User Data Source. Using the mouse, select/push the OK button. (See Below!)

Security should now be included as a User Data Source in the ODBC Data Source Administrator. The configuration is complete. Using the mouse, select/push the OK button. (See Below!)

ASP ODBC Connection Code

The ASP code to connect to the Access Security database is as follows:

'------------------------------------------------------------------------
' Establish ODBC Connection To The Server
'------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")

'Open Security ODBC with the Connection Object
Conn.Open "Security"

Note the simplicity.

Example ASP Code - ODBC-AccessConnection-1

<%@ LANGUAGE = VBScript %>
<% Option Explicit %>  
<% Response.Expires = 0 %>

<%
'===================================================================================
'===================================================================================
'====                       ODBC - Access Connection # 1                        ====
'===================================================================================
'====                                                                           ====
'==== Purpose    : Demonstrate the ODBC connection of the Security              ====
'====              database which is located at C:\Security                     ==== 
'====                                                                           ====
'==== Written By : Dr. Thomas E. Hicks                         Date: 3/22/2005  ====
'===================================================================================
'===================================================================================

'------------------------------------------------------------------------------------
'                                      Declarations
'------------------------------------------------------------------------------------
Dim Conn, SQL, RecSet

'------------------------------------------------------------------------------------
'                             Declare ASP Database Constants
'------------------------------------------------------------------------------------
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4 

Const adModeReadWrite = 3

'------------------------------------------------------------------------------------
'                          Establish ODBC Connection To The Server
'------------------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")

'Open Security ODBC with the Connection Object 
Conn.Open "Security"

'------------------------------------------------------------------------------------
'                        Read All Of The User Names
'------------------------------------------------------------------------------------

SQL = " Select *    " &_
      " From Users  "

'------------------------------------------------------------------------------------
'                      Create & Fill The ODBC Record Set 
'------------------------------------------------------------------------------------

'Create a Record Set Object
Set RecSet = Server.CreateObject ("ADODB.RecordSet")

RecSet.Open SQL, Conn, AdOpenDynamic, AdLockOptimistic
%>

<html><head><title>ODBC-AccessConnection-1</title></head>
<body>

<%
'------------------------------------------------------------------------------------
'                      Cycle Through The Files Until The End 
'------------------------------------------------------------------------------------
Do While NOT RecSet.EOF
%>

Name: <% = RecSet.Fields("Name") %> <BR>
No: <% = RecSet.Fields("Number") %> <BR>
Password: <% = RecSet.Fields("Passwd") %> <BR>
ID No.: <% = RecSet.Fields("IDNo") %> <P>

<%
'------------------------------------------------------------------------------------
'              Move The Record Set Pointer To The Next Record & Repeat Loop 
'------------------------------------------------------------------------------------
	RecSet.MoveNext
	Loop 
%>
</body></html>

Rendering Of - ODBC-AccessConnection-1.asp

The ASP code to connect to the Access Security database is as follows:

ODBC Applet Requires Administrative Privileges

In order to configure ODBC, one must have administrative privileges. All users using Windows 98 have administrative privileges. Windows NT, Windows 2000 Pro, Windows XP Pro, Windows 2000 Server, and Windows 2003 Server can be configured for individual log in accounts; in order to set up ODBC on these configurations, one must have administrative privileges.

Required ODBC Database Access Permissions

The output above can be generated with either of the following permissions on the Security database. The ODBC enables the developer to place the database in a directory more secure than the Inetpub directory. Users thicks and Administrators have full control in order to edit, move, delete, etc. It is sufficient to give everyone Modify permission in order to retrieve, update, and query information from the database using ODBC.

The Internet Guest Account is IUSR_ appended to the name of the computer. The name of my compute is DR-HICKS; therefore the Internet Guest account must have Modify permission in order to retrieve, update, and query information from the database using ODBC.

Required ASP Page Access Permissions

The output above was generated with the following permissions on the ODBC-AccessConnection-1.asp page. The Internet Guest Account is IUSR_ appended to the name of the computer. The name of my compute is DR-HICKS; therefore the Internet Guest account must have Read and Execute permission in order to access the page via a browser without entering a password.

I have given the Administrators and thicks full control over this page so that they can edit, rename, delete, etc.

Moving The Database? ODBC Flexibility!

If the database needs to be moved because it is too big for the current drive, the ODBC client can be opened and re-pointed to the new location. No changes need be made to the pages. The site can be operational in seconds.

ODBC Security

ODBC allows you to place the database outside the IIS directory; this increases security.

ODBC Database Driver Availability

There are at least 140 database with ODBC connectivity. Just as there were small differences in the FoxPro and Access configurations, there will be slightly different interfaces for many of the other database configuration applets.

The MAPPATH Connection #2

MapPath can be used to point to the database relatively with respect to the page.

MapPath Connection Code

Let us assume that the Microsoft Access Security database is in the same directory as ASP page MapPath-AccessConnection-1

The connection code is as follows:

'------------------------------------------------------------------------------------
'                          Establish MapPath Connection To The Server
'------------------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")

ConnString = "DBQ=" & Server.MapPath("Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString

Example ASP Code - MapPath-AccessConnection-1

<%@ LANGUAGE = VBScript %>
<% Option Explicit %>  
<% Response.Expires = 0 %>

<%
'===================================================================================
'===================================================================================
'====                      MapPath-AccessConnection-1                           ====
'===================================================================================
'====                                                                           ====
'==== Purpose    : Demonstrate the MapPath connection of the Security           ====
'====              database which is located in the same directory              ====  
'====                                                                           ====
'==== Written By : Dr. Thomas E. Hicks                         Date: 3/22/2005  ====
'===================================================================================
'===================================================================================

'------------------------------------------------------------------------------------
'                                      Declarations
'------------------------------------------------------------------------------------
Dim Conn, SQL, RecSet, ConnString

'------------------------------------------------------------------------------------
'                             Declare ASP Database Constants
'------------------------------------------------------------------------------------
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4 

Const adModeReadWrite = 3

'------------------------------------------------------------------------------------
'                          Establish MapPath Connection To The Server
'------------------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")

ConnString = "DBQ=" & Server.MapPath("Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString

'------------------------------------------------------------------------------------
'                        Read All Of The User Names
'------------------------------------------------------------------------------------

SQL = " Select *    " &_
      " From Users  "

'------------------------------------------------------------------------------------
'                      Create & Fill The ODBC Record Set 
'------------------------------------------------------------------------------------

'Create a Record Set Object
Set RecSet = Server.CreateObject ("ADODB.RecordSet")

RecSet.Open SQL, Conn, AdOpenDynamic, AdLockOptimistic
%>

<html><head><title>ODBC-AccessConnection-1</title></head>
<body>

<%
'------------------------------------------------------------------------------------
'                      Cycle Through The Files Until The End 
'------------------------------------------------------------------------------------
Do While NOT RecSet.EOF
%>

Name: <% = RecSet.Fields("Name") %> <BR>
No: <% = RecSet.Fields("Number") %> <BR>
Password: <% = RecSet.Fields("Passwd") %> <BR>
ID No.: <% = RecSet.Fields("IDNo") %> <P>

<%
'------------------------------------------------------------------------------------
'              Move The Record Set Pointer To The Next Record & Repeat Loop 
'------------------------------------------------------------------------------------
	RecSet.MoveNext
	Loop 
%>
</body></html>

Rendering Of - MapPath-AccessConnection-1.asp

The ASP code to connect to the Access Security database is as follows:

MapPath Requires No Administrative Privileges

In order to configure MapPath, one needs no administrative privileges.

Required MapPath Database Access Permissions

The output above was generated with the following permissions on the Security database. The Internet Guest Account is IUSR_ appended to the name of the computer. The name of my compute is DR-HICKS; therefore the Internet Guest account must have Full Control permission in order to retrieve, update, and query information from the database using MapPath.

Required ASP Page Access Permissions

The output above was generated with the following permissions on the MapPath-AccessConnection-1.asp page. The Internet Guest Account is IUSR_ appended to the name of the computer. The name of my compute is DR-HICKS; therefore the Internet Guest account must have Read and Execute permission in order to access the page via a browser without entering a password.

I have given the Administrators and thicks full control over this page so that they can edit, rename, delete, etc.

MapPath Security

The path to the Security database is C:\Inetpub\wwwroot\ASP\Tutorials\Security.mdb. If any user points the browser to location http://localhost/asp/Tutorials/Security.mdb, they will be given an opportunity to download the entire database. If a user is using WebWhacker, or one of the other site extraction tools, they will get a copy of the database. This is very insecure!

Relative MapPath Database Placement

In Server 2000, 2000 Pro, and before, the operating system would allow MapPath to navigate up the directory tree.

Assume that path to MapPath-AccessConnection-1.asp is
C:\Inetpub\wwwroot\ASP\Tutorials\MapPath-AccessConnection-1.asp

Assume that path to the Security database is
C:\Security

'------------------------------------------------------------------------------------
'                          Establish MapPath Connection To The Server
'------------------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")

ConnString = "DBQ=" & Server.MapPath(".../.../.../.../.../Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString

All IIS systems allow MapPath to navigate down the directory tree.

Assume that path to MapPath-AccessConnection-1.asp is
C:\Inetpub\wwwroot\ASP\Tutorials\MapPath-AccessConnection-1.asp

Assume that path to the Security database is
C:\Inetpub\wwwroot\ASP\Tutorials\DB\Security.mdb

'------------------------------------------------------------------------------------
'                          Establish MapPath Connection To The Server
'------------------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")

ConnString = "DBQ=" & Server.MapPath("DB/Security.mdb")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString

Moving The Database? MapPath Lack Of Flexibility!

If the database needs to be moved because it is too big for the current drive, each and every page must be changed to re-pointed to the new location. Anytime you start changing the code, there is the potential for errors and omissions. The process of moving the database, using MapPath connections, is not as flexible as it is with ODBC connections.

This is much more of a hassle than ODBC connections, but it makes database interface possible to those leasing web site space on a commercial Internet Service Provider (ISP) This often makes it possible for students to do database interface on a university web server; the administration is not going to provide or manage ODBC for the students. This often makes it possible for small companies to do database interface on an ISP web server; the ISP is not going to provide or manage ODBC for the customers. This often makes it possible for teachers to do database interface on a school web server; the administration is not going to provide or manage ODBC for the teachers

The Absolute Path Connection #3

The direct/absolute path may be used in the database connection.

MapPath Connection Code

Let us assume that the Microsoft Access Security database is in the directory C:\Security.mdb. The connection code is as follows:

'------------------------------------------------------------------------------------
'                          Establish AbsolutePath Connection To The Server
'------------------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")


ConnString = "DBQ=c:\Security.mdb" 
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString

Example ASP Code - AbsolutePath-AccessConnection-1

<%@ LANGUAGE = VBScript %>
<% Option Explicit %>  
<% Response.Expires = 0 %>

<%
'===================================================================================
'===================================================================================
'====                      AbsolutePath-AccessConnection-1                      ====
'===================================================================================
'====                                                                           ====
'==== Purpose    : Demonstrate the absolute path connection of the Security     ====
'====              database which is located at c:\Security.                    ====
'====                                                                           ====
'==== Written By : Dr. Thomas E. Hicks                         Date: 3/22/2005  ====
'===================================================================================
'===================================================================================

'------------------------------------------------------------------------------------
'                                      Declarations
'------------------------------------------------------------------------------------
Dim Conn, SQL, RecSet, ConnString

'------------------------------------------------------------------------------------
'                             Declare ASP Database Constants
'------------------------------------------------------------------------------------
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4 

Const adModeReadWrite = 3

'------------------------------------------------------------------------------------
'                          Establish AbsolutePath Connection To The Server
'------------------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")


ConnString = "DBQ=c:\Security.mdb" 
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & ConnString

'------------------------------------------------------------------------------------
'                        Read All Of The User Names
'------------------------------------------------------------------------------------

SQL = " Select *    " &_
      " From Users  "

'------------------------------------------------------------------------------------
'                      Create & Fill The ODBC Record Set 
'------------------------------------------------------------------------------------

'Create a Record Set Object
Set RecSet = Server.CreateObject ("ADODB.RecordSet")

RecSet.Open SQL, Conn, AdOpenDynamic, AdLockOptimistic
%>

<html><head><title>ODBC-AccessConnection-1</title></head>
<body>

<%
'------------------------------------------------------------------------------------
'                      Cycle Through The Files Until The End 
'------------------------------------------------------------------------------------
Do While NOT RecSet.EOF
%>

Name: <% = RecSet.Fields("Name") %> <BR>
No: <% = RecSet.Fields("Number") %> <BR>
Password: <% = RecSet.Fields("Passwd") %> <BR>
ID No.: <% = RecSet.Fields("IDNo") %> <P>

<%
'------------------------------------------------------------------------------------
'              Move The Record Set Pointer To The Next Record & Repeat Loop 
'------------------------------------------------------------------------------------
	RecSet.MoveNext
	Loop 
%>
</body></html>

Rendering Of - AbsolutePath-AccessConnection-1.asp

The ASP code to connect to the Access Security database is as follows:

Absolute Path Requires No Administrative Privileges

In order to configure Absolute Path, one needs no administrative privileges.

Required Database Access Permissions

Required ASP Page Access Permissions

The output above was generated with the following permissions on the AbsolutePath-AccessConnection-1.asp page. The Internet Guest Account is IUSR_ appended to the name of the computer. The name of my compute is DR-HICKS; therefore the Internet Guest account must have Read and Execute permission in order to access the page via a browser without entering a password.

I have given the Administrators and thicks full control over this page so that they can edit, rename, delete, etc.

Absolute Path Security

Other Tutorials

Internet Database Tutorials

Database Need For Web Applications

ASP - ODBC Connections

ASP - MapPath Connections

ASP - Display Security Users With Access Database

ASP - Display Video Customers With FoxPro Database

ASP - Display Video Movies With FoxPro Database

ASP - Display Video Transact With FoxPro Database

ASP - Display Video Relationships With FoxPro Database

ASP - Display Video Who Checked Out What With FoxPro Database

ASP - Search Security For Users By Name With Access Database

ASP - Search Security For Users By No With Access Database

ASP - Delete Security For Users By Name With Access Database

ASP - Delete Security For Users By No With Access Database

ASP - Add Security Users With Access Database

IDC-HTX-ODBC Database Applications

Database Form Guidelines

May be accessed through URL: http://www.cs.trinity.edu/~thicks
May also be accessed through URL: http://carme.cs.trinity.edu
This Document May Not Be Printed or Reproduced Without Written Permission.
2003 Copyright : Dr. Thomas E. Hicks
Permission granted : Professional Educators & College Students may print one copy of this page!

	Dr. Thomas E. Hicks Computer Science Department Trinity University "Dr. Web"