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)
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.
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."
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.
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.
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.
Figure 1. Connection Properties dialog box
Figure 2. Enabling Scripting Object Model
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.
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 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.
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.
Figure 3. Copy Web Application dialog box
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.
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.
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: