First of all. Use a DSN-LESS connection if possible. They perform better and are easier to set up. Read this article and the related articles listed as well.

SYSTEM DSN Connection

If using a SYSTEM DSN connection a SYSTEM DSN must be set up on the server. This has to be done by the server admins.

Here is an example of accessing a SYSTEM DSN called "mydatabase"

<%
Dim DataConn
Dim CmdSimpleSelect
Dim MYSQL
   
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdSimpleSelect = Server.CreateObject("ADODB.Recordset")
   
DataConn.Open "DSN=mydatabase"
   
MYSQL = "SELECT ID, NAME, EMAIL, MESSAGE FROM some_table"
   
CmdSimpleSelect.Open MYSQL, DataConn
%>

<%= CmdSimpleSelect("ID") %><br>
<%= CmdSimpleSelect("NAME") %><br>
<%= CmdSimpleSelect("MESSAGE") %><br>
<%= CmdSimpleSelect("EMAIL") %><br>

<%
CmdSimpleSelect.Close
Set CmdSimpleSelect = Nothing
DataConn.Close
Set DataConn = Nothing
%>

To create a system DSN you basically do the following.

In the Control Panel, double click the icon for the ODBC Datasource Administrator. In Windows 2000, the ODBC Administrator is located under Start Menu|Settings|Control Panel|Administrative Tools. Click on the System DSN tab, then click Add. Select the Microsoft Access Driver and click Finish. You will now see a select database dialog. Enter the desired DSN in the Datasource name field. You can leave the description blank or put something there if you want. It doesn't matter. Now click on the Select button, then browse down to find the location of the web you have recently created. The path should be something like C:\inetpub\wwwroot\myweb. Once you locate the web's directory, continue to browse until you find the database for this web (inetpub\wwwroot\myweb\_database\mydatabase.mdb) When you open the folder the database is in the MDB file will appear to the left. Select the MDB file and click ok. Continue to click Ok until you have closed all open windows.

Remember that you will have to ask the server admins to create this for you if you do not have direct access to the server. They may prefer that you simply use a DSN-LESS connection and may not be willing to do this.

NOTE: There is also an advanced screen in the ODBC screens as well and if your Access Database has a password set on it you MUST go into that screen and enter the Username & Password information. It will not work otherwise. Even if you specify the Username & Password in the code the setting from the ODBC will override it.

DSN-LESS Connection

You put in the physical path to the database like so.

<%
DataConn.Open "DBQ=C:\Inetpub\wwwroot\aspprotect\_database\mydatabase.mdb;Driver={Microsoft Access Driver (*.mdb)}"
%>

You can also use Server.MapPath if you are familiar with that.

<%
DataConn.Open  "DBQ=" & Server.MapPath("_database/mydatabase.mdb") & ";Driver={Microsoft Access Driver (*.mdb)}"
%>

You cannot specify the physical path using http syntax like this.

This is an example on what not to do.
<%
DataConn.Open  "DBQ=http//www.mysite.com/_database/mydatabase.mdb;Driver={Microsoft Access Driver (*.mdb)}"
%>
This is an example on what not to do.

It just doesn't work that way so don't even try it.

IMPORTANT

If running on a WinNT/WIN2000 web server with the NTFS file system it is very important that permissions are set on the directory the database is in.
The bottom of this article has more info on permissions and how they are set.

Also, since this code is accessing a database your system needs to have the proper ODBC drivers installed. Most systems will already have this installed but if you need to install the drivers or get updated drivers please go to the following site.

http://www.microsoft.com/data/
Look for the MDAC download.

A very common and extremely bothersome error encountered when running ASP apps that connect to a database is the "80004005" error. It comes in many varieties. The error usually occurs if the Everyone or IUSR account does not have Read, Write, Delete permission ( Change permission on NT4; Modify permissions on Windows 2000) on the folder on the server where the database resides. This permission is needed because site visitors via the web browser must be able to access the database via the ASP code.

Generally, the required permissions on this folder are set by the server administrator. This means the person/people that setup the server and keep it running, the people that have physical access to it. Just because you are the admin for a particular site that doesn't not make you the server admin. It is important that you understand the difference. Correct permissions settings are critical to the operation of the ASP code and they must be set by the server admin. For the most part you can not set these permissions using settings in Frontpage or FTP programs. Don't use CHMOD from your FTP program either. It can overwrite things an mess up your web and it's security.

Also realize when testing and running ASP apps with PWS on Windows 95/98, you will not need to set any permissions on the database. You only need to worry about this when running on a NT or 2000 web server using the NTFS file system.

Below are some examples of the many forms the "80004005" error can take.

[Microsoft][ODBC Microsoft Driver] Not a valid password.

Microsoft JET Database Engine (0x80040E4D)

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x6a4 Thread 0x744 DBC 0x25fafe4 Jet'.

These errors are basically because the database could not be found or accessed. If you see any variety of these errors it is most likely because the permissions have not been correctly set or you have not specified the data path correctly. It is also possible something is wrong on the server. Most of the time it is not because of a server problem.

These permissions can for the most part only be set by a server admin who has direct access to the web server. If you are not the server admin you will need to ask for them to set these permissions for you. If using a DSN-LESS connection you may also need to ask them what the physical path info is though the following URL may help you figure that out.
http://www.powerasp.com/content/hintstips/physical-path.asp

How Permissions are set.

Below is a quick summary of how permissions work and what Server Admins need to do to set them in case you are curious.

ASP pages are accessed by anonymous users via the web browser
When users access these pages via a web browser IIS will use (by default) a Windows NT account called iUSR_<machinename> or "everyone".

To set the permissions you have to be on the server and browse to the folder the database is in. Then right click and choose properties. Then you give that account change or modify permissions on the folder that the database is in. Change or modify means... r w x d (read/write/execute/delete). In the future when you put another database in that same directory it will usually inherit the permissions and you will not have to ask again. If you delete the directory and recreate it you will lose it's permissions and they will have to be set again.

Hopefully this gives you a better idea of what is going on when you can't get your ASP scripts and databases working correctly.

Related Info


Using ASP to create, edit, or delete information in a database.
Windows Server 2003 and Permissions
Windows XP Pro and Permissions.

Microsoft Knowledge Base Article Links