Troubleshooting ASP and Microsoft Access Databases
There can be a number of problems that you find when you are dealing with Microsoft Access Databases from ASP and this article tries to show you some of them and the methods to get around the problem. This article will focus on using Microsoft ADO (ActiveX Data Objects) as the method to communicate with the databases.
Problem #1 - Creating an ADO connection string
Depending on the database you are connecting to you need to use different ADO connection strings. These are quite simply to create by hand but I find it much better to use a utility that I wrote to generate the ADO connection strings using a wizard.
This utility including full source code in Delphi is available at http://www.iisfaq.com/Articles/307/ .
Even with the utility above we need to determine the correct driver to use to connect to the database.
| Microsoft Access 97 | - | Microsoft Jet 3.51 OLE DB Provider |
| Microsoft Access 2000 | - | Microsoft Jet 4.0 OLE DB Provider |
| Microsoft Access XP | - | Microsoft Jet 4.0 OLE DB Provider |
Note: The latest versions of Microsoft MDAC which includes Microsoft ADO does not include the Microsoft Jet 3.51 OLE DB Provider.
To download the latest version of Microsoft MDAC please go to http://www.microsoft.com/data/download.htm ; you can also download the latest version of the Jet Components from the same location.
Example Connection String
Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=c:/testing/TestForWebServer.mdb;Persist Security Info=True
The example connection string above first specifies the driver that we are going to use. The database is an Access 2000 database so we are using the Microsoft.Jet.OLEDB.4.0 provider.
In the example above we have a blank password as shown by the Password="" in the connection string. We need to be awear that the above connection string is not valid in ASP as it stands now.
Using this connection string from an ASP page will result in an error because of the Password=""
|
Result of above command
|
The reason that the above code fails is because if you look closely at the provider string what the ASP engine actually parses is not what you think it is.
The provider string above is actually rendered to :
Provider=Microsoft.Jet.OLEDB.4.0;Password=";Data Source=c:/testing/TestForWebServer.mdb;Persist Security Info=True
If you look closely at the Password= portion of the provider string we see only 1 dbl-quote (") and not the two sets of dbl-quotes the provider string originally contained. This is because the dbl-quote (") is a string delimiter in VB Script. We must escape the quote; this means we need to replace the single dbl-quote (") with a two dbl-quotes ("").
This change to our ASP code is shown below.
|
Result of above command
|
Problem #2 - NTFS Permissions
NTFS permissions play a big role in Microsoft Access Databases and ASP. The reason for this is the fact that the web server runs under a specific user account; this user account is by default the IUSR_XXXX where XXXX is the name of the computer the web server is running on. If the computer name is CCROWE then the web server account by default will be IUSR_CCROWE.
If the web server is configured to run out-of-process which means its Application Protection is set to High (Isolated) on the Home Directory tab of the web server properties then it runs as a different user account. The out-of-process user account is called IWAM_XXXX where XXXX is the name of the computer the web server is running on. So again if we use the computer name of CCROWE the out of process user account is called IWAM_CCROWE.
Note: It is very important to remember the IUSR_ and the IWAM_ accounts when dealing with access permissions.
In our sample below the database is stored at c:/testing/ttestforwebserver.mdb
You may see the following error if you just create a database and try to access it through ASP.
|
Result of above command
|
This error message is quite good at explaining the problem; but there are other permission based error messages that can be returned that are very hard to decipher if you have never seen it before.
|
Result of above command
|
The above two error messages relate to the fact that the web server account does not have the rights to access the database.
Setting the following NTFS permissions will cure the errors above.
| ||||||||||||||||||||||||||||||
Notes:
- If you set the FOLDER permission first and then create your database the database will by default inherit the permissions from the folder.
- The Modify NTFS permission in Windows 2000 is Change under Windows NT 4.
- Change the IUSR_CCROWE used above to the user account that the web server is using
- I have also found that the JET Engine creates temporary files as well, these normally get stored in the Temp directory (you can find the temp directory by going into Control Panel -> System -> Advanced - Environment Variables, I would set both the TMP and TEMP folders to allow Change Permissions for the IUSR account.
Other References that may be helpful in your database problems.
- Misc Database Related Information on www.iisfaq.com
http://www.iisfaq.com/DATABASES/
- Operation must use an updateable query
http://www.iisfaq.com/Articles/50/
- Error Message: 0x80004005: General Error Unable to Open Registry Key
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q295297
- "Disk or Network Error" or "Unspecified Error" Returned when Using Jet
http://support.microsoft.com/support/kb/articles/Q251/2/54.ASP
1万+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
