ACC97: How to Use IDC Files to Query a Secure Microsoft Access Database

MORE INFORMATION

• Use ODBC Administrator to create a System DSN that points to the workgroup information file (System.mdw) you use with your secured database.
• Create an HTML form that requests a username and password. The HTML form passes the values to parameters in your IDC file.
• Modify the IDC file to use the username and password parameters to authenticate user access to your database.

• Creating a Secure Copy of Northwind.mdb
• Creating a System DSN for a Secure Microsoft Access Database
• Creating the HTX/IDC Files and the HTML Logon Form
• Customizing the IDC Files
• Testing the Query

Creating a Secure Copy of Northwind.mdb

1. Copy the sample database Northwind.mdb to C:\My Documents\Northwind2.mdb.
2. Start the Workgroup Administrator by executing the Wrkgadm.exe file. The file is installed to your Windows\System folder by default. You can find a shortcut to this file in the C:\Program Files\Microsoft Office folder.
3. In the Workgroup Administrator dialog box, click Create.
4. In the Workgroup Owner Information dialog box type the following information, and then click OK:
   

           Name:  <Your name>
           Organization: Northwind Traders
           Workgroup ID: 1234
   						

5. In the Workgroup Information File dialog box, make a note of the path and file name that appears in the Database box so you can rejoin that workgroup file when you are finished. Then type C:\My Documents\Northwind2.mdw, and click OK.
6. In the Confirm Workgroup Information dialog box, verify that the information is correct, and then click OK.
7. Click OK when you see the message that you have successfully created the workgroup file, and then click Exit in the Workgroup Administrator dialog box.
8. Start Microsoft Access and open C:\My Documents\Northwind2.mdb.
9. On the Tools menu, point to Security, and then click User And Group Accounts.
10. Click the Change Logon Password tab, and type Admin in the New Password and Verify boxes. Click OK.
11. Quit Microsoft Access.
12. Copy the Northwind2.mdb and Northwind2.mdw files to a folder on your Web Server computer, or to a network location that you can access from your Web Server computer. Be sure to retain a copy of Northwind2.mdb on your local drive for use later in this example.
    
    At this point, you can rejoin your original workgroup information file.
13. Start the Workgroup Administrator program following the procedure in step 2 of this section.
14. Click the Join button.
15. In the Workgroup Information File dialog box, type the path and file name of the system database that you noted in step 5, and then click OK.
16. Click OK when you receive the message that you have successfully joined the workgroup, and then click Exit in the Workgroup Administrator dialog box.

Creating a System DSN for a Secure Microsoft Access Database

1. Double-click the ODBC icon in Control Panel on your Web Server.
2. In the ODBC Data Source Administrator dialog box, click the System DSN tab.
3. Click the Add button.
4. Select Microsoft Access Driver, and then click Finish.
   
   NOTE: If the Microsoft Access Driver does not appear, it is not installed on your Web server. For information about installing the driver on your Web server, search the Help Index for "Microsoft Access Desktop driver," or ask the Microsoft Access 97 Office Assistant.
5. In the ODBC Microsoft Access 97 Setup dialog box, type NorthwindIDC in the Data Source Name box.
6. Click the Select button and browse to select Northwind2.mdb. Click OK.
7. In the System Database box, click Database, and then click the System Database button. Browse to select Northwind2.mdw, and then click OK.
8. Note that you have the option to click the Advanced button in the ODBC Microsoft Access 97 Setup dialog box, and set a default Login name and Password for the System DSN. Any of your IDC files that do not provide a username and password will use the default settings.
9. Click OK to close the ODBC Microsoft Access 97 Setup dialog box.
10. Click OK to close the ODBC Data Source Administrator dialog box.

Creating the HTX/IDC Files and the HTML Logon Form

1. Start Microsoft Access.
2. Open Northwind2.mdb.
3. Create a new query called SecureIDC based on the Customers table:
   

           Query: SecureIDC
           ----------------
           Type: Select Query
   
           Field: CustomerID
           Table: Customers
   						

4. On the Query menu, click Parameters.
5. Type the following in the Query Parameters dialog box, and then click OK.
   

           Parameter          Data Type
           ----------------------------
           [UserParam]        Text
           [PassParam]        Text
   						

6. Save the SecureIDC query and close it.
7. Select the SecureIDC query in the Database window, and then click Save As/Export on the File menu.
8. In the Save As dialog box, click "To an External File or Database," and then click OK.
9. In the "Save Query 'SecureIDC' In" dialog box, select Microsoft IIS 1-2 (*.htx;*.idc) in the Save As Type box, and type SecureIDC.htx in the File Name box. Click Export.
10. In the HTX/IDC Output Options dialog box, type NorthwindIDC in the Data Source Name box, and then click OK.
11. Click OK in each of the two Enter Parameter Value dialog boxes that appear.
12. The HTX/IDC output creates three files: SecureIDC.HTML, SecureIDC.htx and SecureIDC.IDC

Customizing the IDC Files

1. Use Notepad or another text editor to open the SecureIDC.IDC file. The Password and Username fields show nothing entered next to them by default. You must add the parameters that will hold the values from the HTML Logon form. Change the SecureIDC.IDC file so it looks as follows:
   

         Datasource: NorthwindIDC
         Template:SecureIDC.htx
         Username:%[UserParam]%
         Password:%[PassParam]%
         SQLStatement:SELECT Customers.CustomerID
         +FROM Customers;
   						

2. Save the SecureIDC.IDC file and close it.
3. Use Notepad or another text editor to open the SecureIDC.HTML file. By default, the HTML form uses the GET method to submit its data. GET variables appear in the address box of Web browsers. Therefore, you must change the GET method to the POST method if you do not want your password to be visible in the address box of your Web browser. Locate the following line in the SecureIDC.HTML file
   

         <FORM METHOD="GET" ACTION="SecureIDC.IDC">
   						

   
   and change it to:
   

         <FORM METHOD="POST" ACTION="SecureIDC.IDC">
   						

   
   
4. Text boxes use an Input Type setting of Text by default. In order to prevent your password from being visible in the text box on your form, you must change the Input Type to Password. Locate the following line in the SecureIDC.HTML file
   

   [PassParam] <INPUT TYPE="Text" NAME="[PassParam]"><P>

   
   and change it to:
   

   [PassParam] <INPUT TYPE="Password" NAME="[PassParam]"><P>

   
   
5. Save the SecureIDC.HTML file and close it.
6. Copy SecureIDC.HTML, SecureIDC.htx and SecureIDC.IDC to a folder on your Web Server computer where you have both Read and Execute permission.