Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jul 6, 2004

MS Access for the Business Environment: "Upsize" MS Access Reports to MS Reporting Services - Page 4

By William Pearson


Let's assume, for purposes of our practice exercise, that we are practitioners in today's harried business environment, and are faced with providing management with options for making our reports "better, stronger, and faster," preferably, of course, at minimal or no cost. (The budget has been impacted negatively this year because management has engaged high-priced consultants to provide options for lowering overall IT costs by off shoring).

We are currently upsizing our application databases to MSSQL Server 2000, and decide to "test upsize" the reports to the Reporting Services platform, as well. We are well aware that the import feature in Reporting Services may not result in a perfect duplication of all reports, complete with existing functionality. The way we see it, anything not automatically converted is probably a good case for examination for enhancement anyway. We can easily adapt the reports to function within the context of Reporting Services, delivering rapidly upgraded information products with a little process improvement as a side dish.

We will convert a complete set of cloned reports, and then investigate any incompatibilities - managing "by exception" any rework that is appropriate, and creating a list of the tasks involved as an automatic byproduct of the process. Sound too good to be true? Let's do it!

First, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to the Microsoft Visual Studio .NET 2003 in the Programs group, as appropriate. The equivalent on my PC appears as shown in Illustration 12.

Illustration 12: Navigate to the IDE in Microsoft Visual Studio .NET 2003 ...

Once we enter the IDE, we will need to create a project to house the new imports. Beginning from the Start page, Projects tab, we will take the following steps:

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 13.

Illustration 13: Creating a New Project

The New Project dialog appears. We note the appearance of Business Intelligence Projects as an option in the Project Types tree, as shown in Illustration 14, indicating an installation of Reporting Services (the folder was added by the installation of Reporting Services, as it established the Report Designer in Visual Studio .NET).

Illustration 14: The New Projects Dialog, with Business Intelligence Projects as a Project Type

5.  Click Business Intelligence Projects in the Project Types list, if necessary.

6.  Click Report Project in the Templates list.

7.  Navigate to a location in which to place the Report Project files.

8.  Type the following into the Name box, leaving other settings at default:


The New Project dialog appears, with our additions, as shown in Illustration 15.

Illustration 15: The Completed New Projects Dialog

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 16.

Illustration 16: The New Project Appears in the Solution Explorer

We have now created a Report Project, and are ready to import the reports in the MS Access database that we have prepared, converting each to a Reporting Services .rdl file in the process.

10.  Right-click the Reports folder in Solutions Explorer. The Reports folder, together with the Shared Data Sources folder, was automatically put in place when we created the new project in our last steps.

11.  Select Import Reports from the context menu that appears.

12.  Click Microsoft Access ... from the cascading menu, as shown in Illustration 17.

Illustration 17: Select Import Reports ---> Microsoft Access

The Open dialog appears.

13.  Navigate to the location where you placed the OrgReports.mdb database file we prepared for this exercise.

The Open dialog appears, with OrgReports.mdb, as depicted in Illustration 18.

Illustration 18: The Open Dialog, Showing OrgReports.mdb

14.  Select OrgReports.mdb within the dialog.

15.  Click the Open button at the bottom of the Open dialog.

An MS Access window opens (MS Access 2003 users will likely see the warning dialog appear - if so, simply click Open), and the reports begin processing immediately. We see the reports appear in the Reports folder of the Solution Explorer, as shown in Illustration 19.

Illustration 19: The Imported MS Access Reports in the Reports Folder

NOTE: As we can see, the new reports are placed into the folder where we request their import via right-click. The new reports become .rdl files, with no obvious means of distinguishing them from reports that may already exist in our Reports folder, if we are importing them into an existing Project. This is one reason to set up an "intermediate" Project to contain the reports, as we have done in this article, at least until they can be tested, etc., and are ready for "admittance to the general population" of reports. (I once called this folder "Ellis Island," for obvious reasons ...).

Even if we are using an existing Project, it is often a good idea to create a folder within the reports folder, named in a way that allows identification of the newly converted files, so as to prevent confusion if names are similar to those in development in an existing project, etc. Once cleared of exceptions detailed in the Task List and otherwise tested, and perhaps renamed to fit our Reporting Services naming conventions, they can be easily moved to another project, etc., before publication / deployment in the routine manner.

Our reports are now in place and ready to be verified for correct operation.

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM