Publishing a Microsoft Access-Driven Web Site Using Visual InterDev

Visual Studio 6.0
 

Jeremy Bostron, Michael Graham
Microsoft Corporation

January 2000

Summary: This article describes server components important for development and production Web servers, how to configure them, the creation of data connections, the most popular ways to deploy Web sites, and some of the issues and problems you may encounter while using Microsoft Access with Visual InterDev to publish data-driven Web sites. (11 printed pages)

Introduction

Microsoft® Visual InterDev® and Microsoft Access can be used together to seamlessly create a data-driven Web site. Having knowledge and understanding of how these products work together can help you get up and running quickly. The first step in accomplishing this task is defining how you will set up your site. In most cases, you will want to avoid working on a production or live Web site because customers will be visiting the site and it is hard to version a site this way. Therefore, you would normally create a development or test site locally, and then copy different versions of the site up to the production Web site. This article is geared toward your development site being on the same machine as Visual InterDev. If, however, Visual InterDev is located on another machine then your development site, you will want to follow the additional steps in the section "Setting Up A Remote Project." Your production Web site can be your own machine or could be a machine at an Internet Service Provider (ISP).

There are a number of items that can make the transition from one site to another difficult. The more you understand the changes that will happen, the easier it will be able to publish your site and have it work as expected. First, we will investigate what server components are important for development and production Web servers and how they need to be configured. Next, we will discuss the creation of data connections and some issues you may encounter. We will then look into the most popular ways to deploy your site. Finally, we will take a look at some of the issues and problems you may encounter along the way.

The Server

Since the Web server is where the Web files will be located both during development and production, you must make sure that the servers are configured correctly. When you set up the production machine or choose an ISP to host your site, try to make the server configuration similar to the development server.

Visual InterDev uses the FrontPage® Server Extensions (FPSE) to communicate with the Web server, which makes FPSE a requirement on the development server. On the production server, you have a choice between using the FPSE and the Posting Acceptor. If you choose to deploy your Web using the Posting Acceptor, you will not need to install FPSE on the production server (see Deploying Your Project). Otherwise, you will need to install the extensions. There are different versions of FPSE available, but the versions of the extensions on the production and development machines do not need to match. The FrontPage 98 extensions are located in the Fp98ext folder on the Visual InterDev CD or disc 2 of the Visual Studio® CDs. The extensions are also available for download at the following sites:

Next, if we are going to be using the design-time controls (DTCs) to create our Web pages, we will also need the Visual InterDev Server Components installed on both the development and production servers. The Visual InterDev Server Components are found on disc 2 of the Visual Studio installation, or on disc 1 of the Visual InterDev installation. On the Web server, you can install the components by running the setup.exe file located in the VID_SS folder.

We are going to be working with a data-driven Web site, so we will need the Microsoft Data Access Components (MDAC) to interface our code with the database. There are several versions of MDAC available (see www.microsoft.com/data/), and you need to understand the important differences between versions. These differences mean that your ISP or production server must have the same MDAC version as your development machine.

If you plan to use Active Server Pages (ASP), you will want to make sure that your Web servers support ASP. Internet Information Server (IIS) 4.0 and Microsoft Personal Web Server both support ASP. Older versions of IIS and FrontPage personal Web server require an ASP upgrade, which can be downloaded from the Knowledge Base article "FP98: ASP Code Displayed in Browser."

Should I Use Active Server Pages (ASP) or HTML Pages?

Active Server Pages and HTML pages interact with your database differently. In an HTML page, the connection string that is used to communicate to your database is placed in the source code of the page. In an ASP page, the connection string is replaced by an application variable. This application variable is set in the global.asa file and is available to all ASP pages. With this in mind, consider the possibility of having to modify the connection string properties. In HTML pages, you would have to open every page and modify the line of code. This can be especially tedious even if you have just a few pages. However, with the ASP solution, you only need to change the connection string in the global.asa file and your pages will access the new value automatically. This article is written with the assumption that you are using ASP.

Setting Up a Remote Project

If you are planning to use Visual InterDev on the same machine as your development server, then you can move ahead to the next section. Otherwise, there are some extra steps that need to be taken. If you are using ODBC or OLE DB for ODBC, you will need to make a duplicate DSN on both the InterDev machine and the machine the development Web server is located on. You will also need to make sure that the path to the database is exactly the same on both machines. If you choose to use Jet OLE DB, then only the database paths need to be the same.

Creating Data Connections

When creating a data connection, you are essentially creating ActiveX Data Object (ADO) code to access your database. You will need to open or create a new project in Visual InterDev and then add a data connection using the instructions below. The steps used to create a data connection in Visual InterDev will be different depending on which Visual Studio service pack you have installed. You will find steps below to create your connections for Service Pack 2 and earlier, and Service Pack 3 and later.

Creating Data Connections with Visual InterDev Prior to Service Pack 3

  1. From the Project menu choose Add Data Connection.
  2. The Select Data Source ODBC dialog will appear. Choose the Machine Data Source tab and click New.
  3. Choose the option for a System Data Source and click Next.
  4. Select the Microsoft Access Driver and then click Next.
  5. Click Finish in the resulting dialog to bring up the ODBC Microsoft Access Setup. Enter a Data Source Name and then click the Select button to choose the database that you would like to use.
  6. Leave all the other default selections and click OK. This will bring up the Select Data Source ODBC dialog again, except this time the new data source will be listed. Click the data source to select it and then click OK.
  7. The Connection Properties dialog (Figure 1) will appear and you can give the connection a familiar name so that you can reference it later. Leave the default option of Use ODBC Data Source Name and click OK.

Creating Data Connections with Visual InterDev Service Pack 3

  1. From the Project menu choose Add Data Connection.
  2. Choose Microsoft OLE DB Provider for ODBC Drivers and click Next.
  3. On the Connection tab select the option to Use Connection String and click the Build button.
  4. The Select Data Source ODBC dialog will appear. Choose the Machine Data Source tab and click New.
  5. Choose the option for a System Data Source and click Next.
  6. Select the Microsoft Access Driver and then click Next.
  7. Click Finish in the resulting dialog to bring up the ODBC Microsoft Access Setup. Enter a Data Source Name and then click the Select button to choose the database that you would like to use.
  8. Leave all the other default selections and click OK. This will bring up the Select Data Source ODBC dialog again, except this time the new data source will be listed. Click the data source to select it and then click OK.
  9. If prompted for the database username and password, enter the proper credentials and click OK.
  10. You will now be back at the Connection tab of the Data Link Properties dialog. Enter the username and password that is needed to access the database and then click Test Connection.
  11. If the Test Connection succeeds, click OK. Otherwise, repeat steps 3 through 10.
  12. The Connection Properties dialog (Figure 1) will appear and you can give the connection a familiar name so that you can reference it later. Leave the default option of Use Connection String because it is required with Service Pack 3 (see Service Pack 3 Bug) and click OK.

Figure 1. Connection Properties dialog box

Create a Database-Driven Web Page

  1. From the Project menu choose Active Server Page from the Add Web Item submenu.
  2. Name your page with an appropriate name and then choose Open.
  3. In the Toolbox, select the Design-Time Controls (DTC) section.
  4. Drag a Recordset DTC from the Toolbox and drop it between the body tags. When prompted to enable the Scripting Object Model (Figure 2) click Yes.

    Figure 2. Enabling Scripting Object Model

  5. Drag a Grid DTC from the Toolbox and drop it between the body tags, but below the Recordset DTC.
  6. On the Recordset DTC, set the Connection to the newly created connection name.
  7. On the Recordset DTC, set the Database Object to Tables.
  8. On the Recordset DTC, set the Object Name to the name of the table from which you would like to show data in your page.
  9. Right-click the Grid DTC and select Properties, then choose the Data tab.
  10. Under Recordset choose the name of the Recordset DTC from the drop-down box.
  11. In the Available Fields box, you can now check the fields that you would like to display on your page. When finished, click OK. Now you are ready to view your page by right-clicking the page and choosing View In Browser.

What Deployment Issues Do I Need to be Concerned With?

Now that you have created a project locally, it is time to deploy it to the production site. It does not matter whether you used OLE DB or ODBC to make your connection, but it is likely that your connection string contains a local path to your database along with a Data Source Name (DSN). When using a file-based database such as Access, special consideration must be given to what is placed in the connection string. For instance, if we create a connection string that includes the path to the database and then we publish the Web path to another machine, what would the outcome be? If the database path on both machines is not exactly the same, then we will get errors like the example below saying that the database cannot be found:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

We are using Active Server Pages, so we can open the global.asa from the Project Explorer and find out what application variables are being set for our connection. In the global.asa, you will find the following line of code:

Application("YourConnectionName_ConnectionString") = "YourConnectionString"

Upon close examination of the string, you will find that it may contain a Data Source attribute that is set to the location of the database if using OLE DB. However, if you are using ODBC, it will contain a DBQ attribute instead. You may also find a DSN property that links to the DSN you created on your development machine. Remember, the properties just described are for your local machine. As soon as the Web is published to your production Web site, it will continue to look for the database paths in these locations. To overcome this, we need to know the options available to us on the production Web server. Once we know what is available, we can modify the connection string if needed, and then publish our Web site. The questions below will help you determine what you need to modify and how to do so.

Do We Know the Exact Location of the Database on the Server?

If we know the exact location of the database on the production server and can duplicate the path on the development server, the deployment of the site will go smoothly. When determining the location of the database, it should not be a mapped drive or a UNC path unless you make the appropriate structure or security changes (see Database Paths). If we duplicate the path on both machines, we will not have to modify the Data Source or DBQ properties of the connection string because the database is located in the same place on both machines.

Sometimes, you will not know the location of the database or cannot guarantee that the database will not be moved. If this is the case, you will have to remove or modify the Data Source or DBQ properties in the connection string. To modify the connection string, right-click the connection in the Project Explorer and select Properties. If you have Visual InterDev Service Pack 3 installed, you can modify or remove the Data Source or DBQ properties directly from the Use Connection String dialog and click OK. If you have service packs earlier than Visual InterDev Service Pack 3 installed, select the miscellaneous tab, then remove or modify the Data Source (OLE DB) or DBQ (ODBC) paths to reflect the new location and click OK. Modifying the connection string will temporarily disable the DTCs at design time and the property should be restored after the Web is deployed.

If Using ODBC or OLE DB for ODBC Connections, Does Your ISP Allow You to Create a DSN?

If you do have the option to create your own DSNs on the production server, make sure that you create them with the same exact DSN name as on your development server. I f you use the same name, the DSN= property of the connection string does not need to be modified because the DSN names match. However, it may not always be possible to have a DSN created on demand. If the Web server supports OLE DB for Jet, you do not need to have a DSN created. If you are using the OLE DB for Jet provider, you need only provide the location of the database, the username, and the password in order to be up and running.

Deploying Your Project

There are several ways to deploy a project using Visual InterDev. Both methods are accessible from the project menu. An understanding of the underlying differences between the deployment options is very important. If both the development and production server have the FrontPage Server extensions installed, the deployment can happen almost automatically; no other server configuration is required. However, make sure that you use the interfaces in Visual InterDev that are described below to deploy or you may damage the FrontPage server extensions and run into other problems.

The first option available to publish your Web is to use the Copy Web Application feature. There are definite advantages to using the Copy Web Application feature because it automatically configures your Web site as an application and updates the IIS metabase. You also have the option for copying only changed pages to your site. However, to copy your site successfully, you will need to choose the appropriate settings.

  1. From the Project menu, choose Web Project; in the submenu, choose Copy Web Application. The Copy Web Application dialog appears (Figure 3).

    Figure 3. Copy Web Application dialog box

  2. In the Source Web Server section, choose the Master Web Server option because it points to your development server.
  3. In the Destination Web Server section, type your production server name in the Server Name box. Then, in the Web project textbox, type in your project name or delete the contents and it will change to <Root Web>.
  4. If this is a new project on the production Web server, uncheck the option to Add to an existing Web project. (Note that, if you are publishing to the root Web, the project has already been created by default and so you would leave the Add to an existing Web project checked.)
  5. If this was an existing project, you will also have the option to Copy changed files only.

The second option available to publish your Web is the Deploy Solution option. This option will publish to servers that do not have the FrontPage server extensions, but have the posting acceptor installed. However, if this option is used with servers that have the FrontPage server extensions, the Deploy Solution option will use the FrontPage server extensions rather than the Posting Acceptor. Another advantage of this option is that it will allow you to deploy your application to any location on the Web server. With the Copy Web Application feature, you can only copy your Web to the root location or a sub Web under the root.

However, there are also disadvantages when using the Deploy Solution option. First, the site is not created in IIS. Therefore, a virtual directory is not created and the metabase for IIS is not updated. Secondly, since the site is not created in IIS, the project is not set up as an application. Therefore, you will need to make the project an application manually on the production site or your Web site application will not work. Finally, you do not have the option to copy changed pages only, so you must copy the complete site each time.

  1. From the Project menu, choose New Deployment Target.
  2. In the Deploy to (URL) box, enter a fully qualified path (that is, http://www.mysite.com/subweb/subweb) and then click OK.
  3. From the Project menu, choose Deploy and from the submenu choose the target you created in step 2.

Database Paths

Sometimes, when setting up paths to databases, you do need to use a mapped drive or UNC path to point to your database. If you need to use one of these path types and your database resides on the same machine as the Web server, you will need to set the security settings not only for the folder that contains the database, but also for the share that the mapped drive points to. If you are using Anonymous Access on your Web server, IIS will use the Internet Guest Account (usually IUSR_ServerName) to access those resources. This account will need full control so that it can access the database and also create the temporary files associated with an Access database. If the Internet Guest Account does not have the proper access to the resource, an error like the one below may occur.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.

As a troubleshooting measure, you may want to add the following code to the beginning of your ASP page:

<%Response.Write "User is logged in as:  " & Request.ServerVariables("AUTH_USER")%>

When browsing the ASP page, you should see one of two things. If there is no user listed, it means the Internet Guest Account is being used to access your resources. However, if a user is listed, it means that NT Challenge Response is being used to access the resource. If NT Challenge response is being used, then you will need to give those users browsing the site access to the resources.

If your database resides on a different machine than the Web server you will run into problems with delegation. The article "Authentication and Security for Internet Developers" describes in depth the security issues that are caused by this configuration.

Essentially, the delegation issue is resolved by creating a duplicate Internet Guest Account from the Web Server on the machine where the database resides. This is needed because the IIS server will try accessing resources on another machine using the Internet Guest Account. The Knowledge Base article "HOWTO: Set Up Duplicate Anonymous Account on Separate Server" has helpful information about setting up this configuration.

Troubleshooting

Once you have set up our site as expected, you may still encounter errors. In order to troubleshoot exactly what is happening you will want to make the simplest page possible and see if you can get that to work. The easiest way to accomplish this is to add an ASP page to your project and create a hard coded connection using the connection string from the global.asa. Below is a basic ASP example that will help you troubleshoot connection issues:

<%
Set CN=Server.CreateObject("ADODB.Connection")
'Place your connection string in the line below
CN.Open "DSN=Gallery" 

'Check if connection opened
Response.Write "Connection Opened!<BR>"

Set RS=Server.CreateObject("ADODB.Recordset")
'Place your SQL statement in the line below
RS.Open "Select * from Customers", CN, 3, 3   

'Display One Value to be sure the data has been retrieved
Response.Write "Value of first field's first record: " & RS(0)
%>

Save the page and view it in the browser. If the Web server has the system DSN called in the script, it should complete successfully. If the page does not complete successfully, it should return a good error to troubleshoot. The following Knowledge Base articles describe possible issues with Access and other file-based databases:

Service Pack 3 Bug

Path Issues

Additional Articles about Using Design-Time Controls

Show: