MS Access for the Business Environment: "Upsize" MS Access Reports to MS Reporting Services - Page 3
July 6, 2004
Practice Exercise: Convert an MS Access Report to Reporting Services
As we mentioned earlier, we will create a database containing reports to illustrate the import process. This will involve copying the Northwind sample .mdb file that potentially accompanies every installation of MS Access 2002. If the sample database is not on your hard drive, whether because you chose not to include it in the MS Access installation or because it was removed later, either obtain it from the CD or initialize MS Access, then go to Help --> Sample Databases, as shown in Illustration 2, and select Northwind Sample Database from the cascading menu.
Click for larger image
If Northwind.mdb is not installed, you will be given an opportunity to re-create it. If you take this route, close MS Access once the .mdb is created, remembering where it was placed. By default, it is typically installed in a location with a path (in an OFFICE10 or OFFICE11 folder) similar to the following:
[DRIVE]: \Program Files\Microsoft Office\OFFICE11\SAMPLES
Once we have located the Northwind.mdb on our hard drives, we will begin preparation for our practice exercise by taking the following steps.
1. Right-click the Start button on the PC, and select Explore, as depicted in Illustration 3.
2. Navigate to the Northwind.mdb located above.
3. Right-click the Northwind.mdb file.
4. Select Copy from the context menu that appears, as shown in Illustration 4.
5. Navigate to a location in which to store the copy for purposes of keeping it separate from the original database, as well as locating it easily through the steps of the exercise.
6. Right-click inside the folder where you wish to store the copy.
7. Select Paste from the context menu that appears, as shown in Illustration 5.
The copy of Northwind.mdb appears in its new location as depicted in Illustration 6.
8. Right-click Northwind.mdb once again.
9. Select Rename from the context menu that appears, as shown in Illustration 7.
10. Type OrgReports.mdb as the new name for the database, as depicted in Illustration 8.
11. Click anywhere outside the name to accept the changes.
Let's take a quick look at our new "report inventory," to get a feel for what we are about to convert.
12. Double-click the OrgReports.mdb file.
MS Access opens. If you are working with MS Access 2003, as I am, you may be greeted by the security warning screens. Simply okay the process of opening the database by clicking Open. (See the Online Help if there are any issues here). If this is the first time using MS Access, you may also see an initial dialog displayed, etc. If so, close it; our objective is simply to open the database to examine reports.
13. Close the Northwind splash screen, which appears on launch of the database, by clicking OK.
The main switchboard appears as shown in Illustration 9.
14. Click the Display Database Window button, to close the main switchboard.
We arrive at the Database Window.
15. Click the Reports link in the Objects pane, shown circled in Illustration 10.
We arrive at the Reports Window, where we can see the various reports that exist in the database we have copied for our practice example. The reports appear as depicted in Illustration 11.
16. Open and review individual reports as desired to get an idea of the various characteristics and components of each.
In exploring the reports contained in the database, we notice some of the reports have fully convertible features, while others contain components that are not supported / fully supported in Reporting Services. We will be notified of unsupported features as part of our upsizing process in the next section.