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 3

By William Pearson

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

Illustration 2: Recreating the Northwind.mdb Sample

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.

Illustration 3: Open Windows Explorer

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.

Illustration 4: Select Copy from the Context Menu

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.

Illustration 5: Paste the Copy of the Northwind.mdb Database in the New Location

The copy of Northwind.mdb appears in its new location as depicted in Illustration 6.

Illustration 6: The Northwind.mdb Database Copy Appears

8.  Right-click Northwind.mdb once again.

9.  Select Rename from the context menu that appears, as shown in Illustration 7.

Illustration 7: Select Rename ...

10.  Type OrgReports.mdb as the new name for the database, as depicted in Illustration 8.

Illustration 8: Renaming the Northwind Database

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.

Illustration 9: Inside Access, Northwind Main Switchboard

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.

Illustration 10: The Reports Link in the Objects Pane

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.

Illustration 11: The Database Reports Collection

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.

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