Working with Access databases that live outside of a Web site

  • Article

We had a customer recently, who needed to access an Access database from both a classic ASP page and from another Access database. To prevent the database from being locked by IIS, the customer wanted to know if they could move the database out of the Web site directory. By default, FrontPage expects the database to be in the Web site. However, when they did this, it seemed to break FrontPage’s ability to work with the database connection as Front Page.

Bernardo Iturriaga Dubost, one of the FrontPage testers, came to the rescue of this customer. Here's his response:

The main reason that FrontPage stores the .mdb files in the web is so that you don’t need to access to the Web server to access the data in a database. However, you can provide dual access to a database by using a system DSN, and then using the DSN in FrontPage to connect to the database. Here's how:

First, you need to create a system DSN on the Web server that hosts the Web site:

  1. From the Start Menu, click All Programs.
  2. Select Administrative Tools, and click Data Sources (ODBC) .
  3. Go to the System DSN tab.
  4. Click Add.
  5. Select the appropriate driver type (for example, MS access driver).
  6. Click Finish.
  7. Provide a name and a description for the DSN.
  8. Click Select the navigate to the actual .mdb file located in another folder on the server.
  9. Click Ok when you're finish selecting the database.
  10. In most cases this is enough, but if the database requires a password, you'll need to provide one. To do this, click Advanced and provide the password.
  11. Click Ok again to create the DSN.

After you're finished, you'll see your new named DSN listed on the System DSN tab of the ODBC Data Source Administrator.

Next, you need tell FrontPage to use the DSN to connect to the database.

  1. Start FrontPage and open the Web site.
  2. From the Tools menu, click Site Settings.
  3. On the Database tab, click Add.
  4. In the dialog select System Data source on web server.
  5. Click Browse. FrontPage displays a list of system DSN on the Web server.
  6. The connection that you created in the previous steps should be there, select it and click Ok.
  7. Click Ok to the Site Settings dialog box, this will add the connection string to the global.asa file in the Web site.
  8. You can now use this connection to the database for database features in FrontPage for both ASP and ASP.NET.

Please note that in order to do this, you need to have access to the actual server that hosts the Web site. One benefit of this configuration is that you can have different permissions for the Web site and for the folder that contains the .mdb file. Please also note that for the users to get data when they browse, MACHINENAME_IUSR needs to have the appropriate read/write access permissions to the .mdb file; otherwise there will be an error.