Using Visual InterDev with Microsoft Access

Robert Flake
Microsoft Corporation

October 1997

Summary: In Microsoft Visual InterDev version 1.0, you can create dynamic Web pages displaying live data from various types of databases, including Microsoft Access. This white paper gives an overview of the Internet features available with Microsoft Access 97 databases and some advantages of using Visual InterDev to display that data. It then describes how to connect to a Microsoft Access database and how to display its data in Microsoft Visual InterDev®, and details some limitations of this functionality.

Overview

Microsoft® Access 97 contains a number of Internet features, allowing the user to display data from Microsoft Access tables, queries, forms, and reports on static or dynamic Web pages. The Microsoft Access 97 Internet features fall into three basic categories:

You can use the Publish to the Web wizard in Microsoft Access to create these types of Web pages from Microsoft Access data. For more information on these Microsoft Access features, see the Microsoft Access 97 Help topics.

These features of Microsoft Access will suffice for many of your Web publishing needs. If you only want to create static Web pages (for example, you have data that doesn't change often, and you don't mind republishing the Web page from time to time), you can use the Save As HTML feature of Microsoft Access 97. You can also publish the results of a Microsoft Access report to a static Web page, which you can't do directly in Visual InterDev. You can use the Save As IDC/HTX functionality to create Web pages which will show changes in the Microsoft Access data, and the Save As ASP functionality to create updatable Web pages based on forms. However, as discussed in the Limitations section later in this white paper, you can't directly customize the Web page, use ActiveX controls, or perform any scripting without manually editing the ASP file after it's created.

If you want more direct control over the appearance and functionality of your Web page, such as adding data validation or custom navigation, you should create a Visual InterDev project and connect to the Microsoft Access database from this project.

Connecting to a Microsoft Access Database from Visual InterDev

There are two steps required to connect to a Microsoft Access database from Visual InterDev:

• Install the Microsoft Access database on a server. This can be your Web server, a separate database server, or any computer to which all users of your Web page have access.
• Connect to this database from your Visual InterDev project.

You follow similar procedures to connect to other types of databases, such as Microsoft SQL Server™ databases, but you make specific choices when you connect to a Microsoft Access database. For example, you must select the Microsoft Access ODBC driver, and you don't use the SQL Enterprise Manager to install the database, as you do with SQL Server databases.

For more information on connecting to a Microsoft Access database from Visual InterDev, see the Up and Running with Microsoft Visual InterDev tutorial, available from the Samples and Tutorials section of the Microsoft Visual InterDev Web site, at https://msdn.microsoft.com/vinterdev/.

Installing a Microsoft Access Database on a Server

To install a Microsoft Access database on your server, follow these steps:

1. Create a directory on your server called C:\Database (or any folder name you choose).
2. Right-click the C:\Database folder in Windows Explorer, and then click Sharing.
3. Name the share Database (or any name you choose) so that it is available to Visual InterDev development workstations as \\ServerName\Database.
4. Copy the Microsoft Access database (.mdb) file whose data you want to use with your Visual InterDev project to the C:\Database folder on your server.

Note   To make the database data available to all users of your Visual InterDev application, the Microsoft Access database must be located in a directory on the server and your application must make a connection to the database in this location (and not, for example, to a copy of this database on your client workstation). If you don't install the database on your Web server, the share where the database is located must also be available to the Web server. You must make sure that the Internet Anonymous Logon user for your Web server has permission to connect to the server and directory where the database is located. If you don't want to deal with complex security issues involving different servers, it's easiest to install the database on your Web server.

Connecting to a Microsoft Access Database from the Client Computer

To connect to a Microsoft Access database from a client computer, follow these steps:

1. In Visual InterDev, choose File View and right-click the root of your Visual InterDev project
2. Click Add Data Connection. The Select Data Source dialog box is displayed.
3. Using this dialog box and the succeeding dialog boxes, add a new data connection, select the Microsoft Access driver, and specify the Microsoft Access database on your server as the database to use with this data connection.

**Note   **When you specify the Microsoft Access database in the Select Database dialog box, make sure you use the path to this database on the LAN, not a local version of this database or a path to a drive mapped to the server. For example, specify \\ServerName\Database\MyDatabase.mdb, not D:\MyDatabase.mdb, where the D: drive is mapped on the client computer to \\ServerName\Database.

Figure 1. Connecting to a Microsoft Access database

Microsoft Access and Visual InterDev use the same functionality to create data sources and the connection strings that define them. In Microsoft Access, you create data sources using the ODBC Data Source Administrator in the Control Panel. The connection strings (the DSN strings that define the data connection) that you create in Microsoft Access are equivalent to the connection strings created by the Select Data Source dialog boxes in Visual InterDev. If you've created a data source in Microsoft Access, it is shown on the Machine Data Source tab of the Select Data Source dialog box as a System data source.

Viewing a Connected Microsoft Access Database in Data View

Once you've created the data connection, it's displayed underneath the Global.asa file in FileView of your Visual InterDev project.

If you click Data View in your Visual InterDev project, you see the Microsoft Access database tables listed, with their fields. The Data View also shows any queries in the database, listed under the Views folder. You can open the tables and run the queries and browse their datasheets. You can also edit data in the table datasheets and query result sets (as long as the query includes all primary key columns from the table). For a description of the limitations of the Data View display of Microsoft Access objects, see the Limitations section below.

Microsoft Access data with the data types Memo, OLE Object, and Hyperlink are displayed in Data View with the word "<binary>". You can't edit fields containing these types of data.

Getting and Displaying Microsoft Access Data in Visual InterDev

Once you've created a data connection to a Microsoft Access database in your Visual InterDev project, you can use this connection to display data from the database on a Web page. There are a number of ways to display data in Visual InterDev version 1.0. One method is to create an ASP page and then insert a Data Command control.

Note   You can create a connection to a database and display its data by typing the appropriate script commands directly into your ASP page. Using the Data Command control with a data connection you've added to your project creates this script automatically for you.

In the Properties dialog box for the Data Command control, you can specify the data connection to the Microsoft Access database, and specify the set of data you want to see. You can use the SQL Builder to create an SQL statement that queries the Microsoft Access database, or run an existing query by selecting View in the Command Type box and selecting the query in the Command Text box.

Figure 2. Setting a query to an Access database in Properties

Note   You can also use the Data Form Wizard to create one or more ASP pages displaying data from a database. The user can browse, update, delete, and insert data on these pages. For more information on the Data Form Wizard, see the Visual InterDev Help topics.

Displaying Table Data

To display a set of records from a table in the Microsoft Access database, insert the Data Command control on your ASP page and use the SQL builder to select the set of records. For example, if you want to select the UserID, UserEmailName, and UserPassword fields from a Passwords table, you can use the SQL statement "SELECT UserID, UserEmailName, UserPassword FROM Passwords". The section of the ASP page containing the data connection information will look like this:

<%Set AccessDB = Server.CreateObject("ADODB.Connection")
AccessDB.ConnectionTimeout = Session("AccessDB_ConnectionTimeout")
AccessDB.CommandTimeout = Session("AccessDB_CommandTimeout")
AccessDB.Open Session("AccessDB_ConnectionString"), Session("AccessDB_RuntimeUserName"), Session("AccessDB_RuntimePassword")
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set DataCommand1 = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "SELECT UserID, UserEmailName, UserPassword FROM Passwords
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = AccessDB
DataCommand1.Open cmdTemp, , 0, 1
%>

Note that you can edit the SQL statement on the ASP page after the Data Command control has been inserted. For example you could add "WHERE UserID = 1" if you wanted to restrict the records to those for a particular user.

You can use the Data Range Header and Data Range Footer controls to display sets of records on the Web page. For information on these controls, see the Visual InterDev documentation.

Displaying the Results of Microsoft Access Queries

If you select View in the Command Type box and an existing Microsoft Access query in the Command Text box, the data connection section of your ASP page will look like this:

<%Set TestDB = Server.CreateObject("ADODB.Connection")TestDB.ConnectionTimeout = Session("TestDB_ConnectionTimeout")
TestDB.CommandTimeout = Session("TestDB_CommandTimeout")
TestDB.Open Session("TestDB_ConnectionString"), Session("TestDB_RuntimeUserName"), Session("TestDB_RuntimePassword")
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set DataCommand1 = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "`Query2`"
cmdTemp.CommandType = 2
Set cmdTemp.ActiveConnection = TestDB
DataCommand1.Open cmdTemp, , 0, 1
%>

As mentioned earlier, Microsoft Access queries are displayed as Views in Data View in your Visual InterDev project and you can run these queries. You can even run action queries and some parameter queries. Note that you can't run the following types of queries directly from Data View:

• Queries containing user-defined functions
• Parameter queries for which any of the required parameters is a numeric data type

In each of these cases, you can create a data connection to the table whose data you want to query, and then use scripting code to perform the same action the query would. For example, you can use the Update method to update the records in a table, or the Parameters collection to pass parameters to restrict the records in a table.

Generally, it's easier and more reliable to use direct SQL queries and scripting code to access and restrict the records in the Microsoft Access database. If you have simple stored Microsoft Access queries that you commonly need, you can run them in Visual InterDev. For more complex and varied querying capabilities, use the Visual InterDev functionality.

Publishing Microsoft Access Reports

As described earlier, you can publish a snapshot of the data in a Microsoft Access report to a static Web page using the Save As HTML command in Microsoft Access. If you want to display the most recent data, you must republish the report. This works fine if your data doesn't change frequently, or you just need to send a snapshot of your data to your audience at a particular time. Note that you can automate this process by using the OutputTo action or the OutputTo method of the DoCmd object in Microsoft Access.

You can't publish a Microsoft Access report to IDC/HTX or to ASP format. Similarly, you can't run an existing Microsoft Access report from Visual InterDev. However, you can use Data Range Header and Data Range Footer design-time controls (DTCs) in Visual InterDev to specify a set of records to print. For more information on these design-time controls, see the Visual InterDev documentation.

You can also use the SQL clauses ORDER BY and GROUP BY in your script to sort and group these records (although to achieve the complexity of the Microsoft Access report functionality, you'd have to create some very complex SQL statements).

Limitations

Once you've connected to a Microsoft Access database in Visual InterDev, you should be aware of the following limitations.

Using Template Files

In Microsoft Access, you can use HTML template files to determine how the Access data is displayed on the Web page. You can also use the OutputTo action or its corresponding method to automate this process. Visual InterDev also has template capability; however these two types of templates are not compatible. If you want to use templates to determine how your new Web pages will look, use the template functionality in Visual InterDev, or design your Web pages with FrontPage®. For more information on Visual InterDev templates, see the "Creating Your Own Visual InterDev Templates" article, available from the Technical Resources section of the Microsoft Visual InterDev Web site, at https://msdn.microsoft.com/vinterdev/.

ASP support

Microsoft Access 97 implemented only part of the ASP functionality. For example, only certain kinds of fields and certain objects can be displayed on ASP pages from Microsoft Access. In addition, a dynamic HTML version of a Microsoft Access form doesn't have all the capabilities of the original form in Microsoft Access. For example, Microsoft Access will ignore the form background picture or a macro used to validate data. Also, Microsoft Access does not generate any Microsoft Visual Basic® Scripting Edition code to replace Visual Basic for Applications code behind forms, expressions in controls, or properties. For more information on the limitations of Microsoft Access on ASP pages, search the Microsoft Access Help topics for "ASP" and read the topics, including "Supported and unsupported form controls for dynamic ASP format."

In addition, the ASP files generated for forms by Microsoft Access have some special layout code in them. If you open these files in Visual InterDev, this code is not shown. The Web pages for these ASP files can't be modified effectively in Visual InterDev. You can, however, use Visual InterDev to recreate the functionality of the Microsoft Access form and display and edit its data.

Visual InterDev has more extensive ASP support, including robust scripting and ActiveX controls. You may be able to control the display of your Microsoft Access data more fully in Visual InterDev.

Querying joined tables

You can't query joined tables in Microsoft Access from Visual InterDev. This is due to a problem with the ODBC Microsoft Access driver shipped with Microsoft Access 97.

Problems accessing a page that uses a Microsoft Access query and a linked table

You might experience Web server problems if you are trying to run a Microsoft Access query that uses a linked table on a Web page. Microsoft Access queries with linked tables on Web pages are not supported with the version of the Microsoft Access ODBC driver that is included with Visual InterDev version 1.0. This problem occurs only on a Web page since multiple users can access the database; this causes threading issues with the Microsoft Access ODBC driver. This problem will not occur with applications that do not use multiple threads against the Microsoft Access ODBC driver.

Setting Microsoft Access properties

The Visual InterDev Data View window shows the tables in the database and all their fields. It also shows queries as views. You can right-click a table, field, or query, and then click Properties to see a set of database object or column properties. These are not the same as the Microsoft Access table, field, or query properties; they are Visual InterDev properties that provide information about the database objects. These properties are read-only. You can't set any properties for Microsoft Access objects in Data View; you must set them in the Microsoft Access database.

Modifying Microsoft Access tables, queries, and fields

In Data View, you can't add or update tables or views (queries). You also can't change the structure of tables or queries. You can only browse and edit (add, update, or delete) data.

Tips and Tricks

You can use Visual InterDev and Microsoft Access together to create some powerful data-driven Web pages. This section gives you some handy tips that may make it easier for you to use Microsoft Access with Visual InterDev, and help you avoid common problems.

You Can't Use Movefirst, Movelast, or Moveprevious in Your Recordset

Make sure the CursorType and LockType properties of your Recordset object are not set to Forward Only and Read Only. These are the default property settings and give you a firehose cursor. This cursor does not support any method but MoveNext. You can set these properties in scripting code. For the DataCommand control, you can set them in the Cursor Type and Lock Type boxes on the Advanced tab of the properties dialog for the control.

This problem can also occur if you did not include the Active Data Objects (ADO) constants using a server-side INCLUDE tag. If these constants are not defined (there's no reference to the typelib in the .asp file), they all evaluate to 0, which corresponds to forward-only. This problem only occurs if you use the intrinsic constants instead of their integer equivalents. When Visual InterDev sets these constants in scripting code it generates, it uses the integers.

SQL Wildcard Characters Don't Appear to Work in the Microsoft Access Database

Microsoft Access doesn't use quite the same set of SQL wildcard characters as other SQL implementations. For information on the SQL characters Microsoft Access uses, search the Microsoft Access Help topics for "Comparison of Microsoft Jet Database Engine SQL and ANSI SQL." For example, Microsoft Access uses the wildcard characters "*" to denote "any number of characters at this position" and "?" to denote "any one character at this position." In Visual InterDev SQL statements, you must use "%" and "_" instead.

Detecting Required Fields in Microsoft Access Databases with the Data Form Wizard

There is a known problem with the Microsoft Access ODBC driver that prevents the Data Form Wizard from correctly determining which fields need to be included in the data form in order to properly update records. This means that the fields displayed in the Data Form Wizard as required might not be required, and that some fields that are not marked as required might actually be required. To work around this problem, use Microsoft Access to check which fields in your database are actually required (that is, each record must have a value in this field) before launching the wizard, and ensure that you've included the required fields in your data form.

Using Aggregate Functions with Queries in the Data Form Wizard

If the Data Form Wizard cannot determine the result columns in your query, check that you're not using aggregate functions (for example, Count or Sum) in the SQL statement you supplied to the wizard. To use aggregate functions with the Data Form Wizard, you must use a SQL alias in order for the data form to generate proper references for the query.

For example, the following Group By query does not work:

SELECT City, Count(population) FROM State GROUP BY City

You must change it to:

SELECT City, Count(population) AS Persons FROM State GROUP BY City

Problems Filtering with the Data Form Wizard Against Fixed-Length Fields

If you use the filter mode of the pages created by the Data Form Wizard, you need to specify a wildcard character ("%") after data you want to filter on if that data is contained in a fixed-length character field in the database. You must specify this wildcard character because ADO pads the field with spaces up to the field width. For example, if you want to search for all entries called "Cars" in a fixed-length field of eight characters, the filtering tries to compare "Cars" with "Cars    " which doesn't match. In this example, the workaround would be to specify "like 'Cars%'" as the search criteria.

Performance Tips

Microsoft Access works fine as long as you don't have too many users or a very large amount of data. However, as you add concurrent users and data, you might notice performance problems, and your system might eventually hang. If you find this happening when you're connected to a Microsoft Access database, you might consider upsizing your Microsoft Access database to SQL Server, and then connecting to the SQL Server database from Visual InterDev.

Not only can you use SQL Server to handle more users and more data, but you can also take advantage of such SQL Server features as dynamic backup, automatic recovery, and complex transactions. You can use the Microsoft Access Upsizing Tool to quickly convert your Microsoft Access database to a Microsoft SQL Server database.

Note that you should avoid mixing Microsoft Access and SQL Server data sources on the same Web page. This mix can cause serious performance problems.