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 database.
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 Providermicrosoft Access 2000 - Microsoft Jet 4.0 Ole DB ProviderMicrosoft Access XP- Microsoft Jet 4.0 Ole DB Provider
NOTE: The Latest Versions of Microsoft Mdac Which Included 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 stringProvider = microsoft.jet.oledb.4.0; password = ""; data source = c: /testing/testfor webserver.mdb; persist security info = true
The Example Connection String Above First Specifies The Driver That We Are Going to Use. The Database So We are for useing 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
AdoDb.connection (0x800a0bb9) arguments area of the Wrong Type, Are Out of Acceptable Range, or Are IN Conflict with One Another./Users.asp, Line 15
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 actally rendered to:
PROVIDER = Microsoft.jet.Oledb.4.0; password = "; data source = c: /testing/testfor webserver.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
Nothing is returned because there is no errors our any output from the above command. If you get an error the web server may not have the right to open the database. See the next section for help on this problem.
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.
.
In Our Sample Below The Database Is Stored At C: / Testing/TTestForWeb Server.mdb
You May See The Following Error if You Just Create A Database and Try To Access It Through ASP.
Result of Above Command
Microsoft JET Database Engine (0x80004005) The Microsoft Jet database engine can not open the file 'C: /testing/TestForWebServer.mdb' It is already opened exclusively by another user, or you need permission to view its data./users.asp,. Line 13
This Error Message Is Quite Good At Explaining The Problem; But There Area Other Permission Based That Are Very Hard To Decipher if you have.com
Result of Above Command
Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft] [ODBC Microsoft Access Driver] General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x614 Thread 0x6c0 DBC 0x21dd07c Jet'. (FileName), (LineNumber) ................
Setting The Following NTFS Permissions Will Cure The Errors Above.
Task Permission User AccountsActual MDB Database File (* .mdb) Modify IUSR_Ccrown, IWAM_CCROWE
Folder where the * .mdb file is located modify iusr_ccrowe, iWam_ccrown
Temp Director Mookify IUSR_Ccrown, IWAM_CCROWE
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 Founters to Allow Change Permissions for the Iusr Account.
Other References That May Be Helpful in your database problex.
