Using MS Access Databases

Part:   1  2  3  4  5 

(Continued from previous part...)

Here is what I did to test those steps:

I used the same MS Access database as my previous test. The database file is located in same place: "c:\inetpub\wwwroot\hello_access.asp".

My ASP script for this test was very simple, insert_access.asp:

<script language="vbscript" runat="server">
'  insert_access.asp
'  Copyright (c) 2005 by Dr. Herong Yang, http://www.herongyang.com/

   Set oConn = Server.CreateObject("ADODB.Connection")
   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      Server.MapPath("/cgi-bin/hello.mdb")
   
   Set oRes = oConn.Execute("INSERT INTO message VALUES ('I am here.')")

   Set oRes = oConn.Execute("SELECT * FROM message")
   Do While NOT oRes.EOF
      Response.write(oRes("text") & "<br>")
      oRes.MoveNext
   Loop

   oRes.close
   oConn.close 
</script>

This script was also copied to my local IIS server as "c:\inetpub\wwwroot\insert_access.asp".

Then I opened Internet Explorer with http://localhost/insert_access.asp. This time I got a page crash message:

Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/insert_access.asp, line 9

What's wrong? I had no idea when I saw this message. But searching on the Internet helped me. This error was caused by lack of "write" permission given to the IIS server.

By default, IIS server is given "read" permission to almost all files on server's hard disk. But it has no "write" permission any files. Here is how to fix this on Windows XP system:

1. Making sure that IIS directory setting on /cgi-bin/ has "read" and "write" checked. Go to Control Panel > Administrative Tools > Internet Information Services > local computer > Web Sites > Default Web Site > cgi-bin. Right mouse click to select "Properties". On the "Directory" tab, you will see "read" and "write" checked.

2. Finding out that IIS is using which Windows account to access cgi-bin directory. Following same process as in step 1 to invoke the "Properties" dialog box window of the cgi-bin directory. On the "Directory Security" tab, click the "Edit" button in the "Anonymous access and authentication control" section. The "Authentication Methods" window shows up. The "Anonymous access" should be checked. "IUSR_computername" should be in the "User name" field.

3. Making sure that "simple file sharing" is turned off on you system. Go to Control Panel > Folder Options. On the View tab, "Use simple file sharing" should be unchecked.

4. Giving "write" permission to "IUSR_computername" on "\inetput\wwwroot\cgi-bin\" directory. On Windows Explorer, right mouse click on "\inetput\wwwroot\cgi-bin\" to select "Properties". On the Security tab, click the "Advanced" button. The advanced security settings dialog box shows up. Uncheck the "Inherit parent permission entries that apply to child objects." Click "Copy" when prompted to copy all permission from parent directory. Click "OK" to close advanced security settings.

Back on the cgi-bin directory properties dialog box, click "Add" to add a new user account called "IUSR_computername". Remember this user account must be the same used by IIS to access this directory, see step 2. Now "Internet Guest Account" should be listed on the "Security" tab. Select "Internet Guest Account", Make sure the "Read" and "Write" are both checked under the "Allow" column.

4. Giving "write" permission to "IUSR_computername" on "\inetput\wwwroot\cgi-bin\hello.mdb" file. Repeat the same process listed in Step 3 for "hello.mdb" file.

Then I tried again with Internet Explorer on http://localhost/insert_access.asp. I got:

Hello world!
I am here.

Perfect, right? If you refresh the page, a new line will be added to the end.

Notes on this test:

• Persisting data back to MS Access database file requires "write" permission on the database file and directory.
• IIS server is using user account called "IUSR_computername" to access all files. Of course, this account is total different than your login account.

(Continued on next part...)

Part:   1  2  3  4  5