Reporting with Access 2000

Take advantage of Access 2000/SQL Server integration

When you’re searching for a Web reporting tool, don’t overlook Access 2000. You can use Access to generate Active Server Pages (ASP), HTML, or traditional Access reports. Access’s ASP support is somewhat weak compared to other tools, but the Access Export wizard gives you a jump start in creating an ASP page that you can modify to fit your needs. Also, the integration of Access with SQL Server makes it easy to start an ASP page.

In this article, I show you how to use Access to generate a new ASP page from a table and a new report. I’ll step you through the process of creating the database, linking it to SQL Server, and generating the output. Because Access requires you to work from the context of a database, you first need to create an Access database. You can do this by starting Access, selecting the Blank Access Database radio button, and clicking OK. Next, choose File, Get External Data, and Link Tables to link the database to the tables in the Northwind sample database. To create this link, select ODBC Data Sources from the Files of Type list on the Link dialog box, then select the data source that points to the Northwind database. Follow the prompts to enter your server and security information. The result is a series of Access tables with dbo_ prefixes, each linked to a corresponding SQL Server table.

Creating an ASP File

The next step is to create an ASP file to generate a report from the Categories table. To do this, right-click the dbo_Categories table in the database window, then select Export from the shortcut menu. Next, enter the name of the ASP file. The default name is the table name with an .asp extension Then select Active Server Pages from the Save As Type list and click Save. This results in the dialog that Screen 1 shows. The options that Screen 1 shows are for my server. Change the data source name and server name to match your server. When you’re finished, click OK. This action will generate an ASP file in the directory that you selected in the Save As dialog box.

When the ASP code accesses SQL Server, it looks for dbo_Categories rather than Categories, which is the table name in SQL Server. You'll receive an error message that refers to an invalid object name. To avoid this error, open the .asp file in the editor, then use the find-and-replace feature to delete all references to dbo_. Listing 1 shows the resulting code, which is the code that Access generates when it saves the page and the deletions of references to dbo_. Now the page works correctly. The next thing I did was change the linked table names in Access and delete the dbo_ prefix. Now any ASP code that Access generates will work with no changes.

Now let’s use the Access Report Wizard to create a report that lists order by product. You can export reports to a series of HTML files. You can export a report to HTML by selecting the report in the Report window and right-clicking the report name, then selecting Export. In the Export dialog box, select HTML documents and click Save. If you need a dynamic set of pages rather than a static set, this solution won’t work. Here’s how to create a similar query and export it to ASP. I used the Access Query Designer to create the following query (OrdersByProduct):

SELECT Products.ProductID, Products.ProductName, [Order Details].OrderID,[Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity,Orders.OrderDate, Customers.CompanyName

FROM ((Products INNER JOIN [Order Details] ON Products.ProductID = [OrderDetails].ProductID) INNER JOIN Orders ON [Order Details].OrderID =    Orders.OrderID)INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerIDORDER BY Products.ProductID, Customers.CompanyName;

Next, I right-clicked the query in the Queries window, then selected Export and followed the same steps as before. This generated a new ASP file named OrdersByProduct.asp. Next, I used Windows Explorer to copy OrdersByProduct.asp into the Visual InterDev project mentioned earlier.

If you need to create a header for the products on the page, you can revise the code that Access generates. First, you can add a new variable called sCurrentProduct, which Callout A in Listing 2 shows.

Next, you can create the code that Callout B shows to control the product name header. First, the code clears the sCurrentProduct variable. The next line is the original rs.MoveFirst from the Access-generated code. MoveFirst moves to the first record in the recordset. Next, the DO WHILE statement, also generated from Access, loops through the recordset.

Next, you can add an IF statement to test the sCurrentProduct variable. If this variable doesn’t equal the current product name, then it’s set to the current product name in the IF statement and a Table Detail line is output. This line adds the data from the ProductName variable to the table. If the If statement fails, the ProductName value isn’t output and renders blank.

Screen 2 shows the resulting page. This page doesn’t include any paging controls that let the user navigate through the recordset or use other sophisticated features found in many Web applications. You can add these controls, but you’ll need to add significantly more code.

Reporting Integration

Access 2000 includes Visual Basic for Applications (VBA) 6.0. When you create Access reports, you can use VBA to control the report data, perform filtering, and conduct other tasks. The tools available in Access, and its integration features with SQL Server, let you create reports that provide a high level of interaction and provide rich features to users.