Working with Access Projects in Access 2007

Microsoft Access 2007 features significant differences from earlier versions. One of these is the lack of support for data access pages. Projects become important in this environment. This tutorial will show you how to create an Access project and more.

Introduction

Microsoft Access projects are based on a file with the adp extension. They provide efficient native mode access to SQL servers through an OLEDB interface. In the earlier versions you could have created web-based applications using data access pages as well as client/server type applications using forms/reports.  Access 2007 does not have data access pages but supports Access projects.

Projects can use only SQL databases (desktop version or otherwise) on a local machine or on a networked machine. You can also work with projects created in earlier versions of MS Access.

Access projects are better than just linking to tables in the database.
Links can only be made to tables or views and you cannot change the table structure or change the design of the table used in creating the view. On the contrary, in Access projects you can make changes or even create new objects on the database. The database objects are stored on the SQL server, however you have the support of tools such as table designer and a query designer as well as a SQL text editor that can be used to make changes. You can modify, create, delete or display any database object.

In this tutorial you will learn how to create an Access project and create and execute a stored procedure.

{mospagebreak title=Creating an MS Access 2007 Project}

In Access 2007 an Access project is created in a roundabout manner. The reason for this is not obvious. Click the Microsoft Office button and from the drop-down click on New…. This opens a number of options, none of which says Access project except for a canned project on the Microsoft web site. In the Blank Database form that opens at the bottom right, type in a suitable name such as the one shown here, Acc07Proj. Do not click on the Create button; instead, click on the Browse button and browse to any folder.

This opens the File New Database window with the name you typed in appearing in the File name’s textbox. If you click OK it will not create a project. Click on the Save as type drop-down below File name,  pick Microsoft Office Access Projects and then click OK.

This changes the Blank Database to New Project. Now click on the Create button.

This brings up the Microsoft Office Access message box. If you click on Yes, you will be connected to an existing SQL server database, and if you click on No, you can create a new database on one of the available servers. Here Yes is clicked to connect to an existing database.

This opens the Data Link Properties window where you need to select a server and provide other necessary information. Here you see three SQL servers: XPHTEK is a SQL 2000 Server; HodentekMYSORIAN is a SQL 2005 server on one machine; and Hodentek2/SQL Express is a SQL Express server on another networked machine.

The connection with one of the servers is made by providing the required Information, the authentication information and the database to which you require a connection as shown here. You can test the outcome of this information by hitting the Test Connection button. As you can see the three pieces of information provided on this window produced a successful connection. Here the provider tab is missing since it is an OLEDB provider, the native type for SQL servers.

Since Allow saving password was checked when you clicked on the OK button, you get the next informational message from MS Access.

Here the Save Password option was chosen and of course the message shows what will happen in this case. When the Save Password button is clicked it opens a window to the database objects.

Here a word about the Windy database is in order. Windy has only the tables from the Northwind database brought over to SQL Express using the Export/Import wizard on the SQL server HodentekMysorian. Hence you are seeing only the Tables. However as mentioned earlier you can create, modify, delete, and/or view database objects.

{mospagebreak title=Creating a Stored Procedure in the Project}

With the database objects added you will be able to find the stored procedure icon in the Other section, right next to the Reports as shown here.

;

Clicking on the Stored Procedure Icon, you will be able to create a New Object, a stored procedure.

This opens the Add Table window where you can add one or more tables (use the shift key) to the fashioning of your stored procedure which uses a Query Editor.

When you close the above window after choosing the table or tables, the objects will be added to the design pane. This works very much like the query editor in other, older versions of MS Access. You can pick the columns you want, sort them and order them and impose constraints to filter rows using criteria. Right clicking an empty area in the design pane lets you access the Grouping option as well as a few other tasks you can do as shown.

We will be developing a stored procedure which asks for the name of a city and displays related information for that city. What was typed in was a question mark(?) in the criteria column  which becomes =@Param1, the parameter for a search query.

When you right click the design area you can access the Properties window for this stored procedure as shown.  You can choose to display all columns or the chosen ones; you may, if you want, choose to show only distinct values; and so on. Choices made here will go into a stored procedure SQL script as seen later. The description is optional.

If a Default is chosen for the @Param1 as shown in the Stored Procedure Parameters tab, then the stored procedure takes a hard coded value for the parameter and displays just the selected columns for that procedure.

The Data tab can be used while scripting a stored procedure which depends on multiple tables with Master/Child relationships.

Before you see the results of the procedure you need to save the procedure.

The stored procedure is saved as ByCity in the project.

When you click on the OK button the stored procedure will be saved and it immediately appears under the All Access Objects window as shown under Queries.

If you right click the query named ByCity, you will pop open a drop-down where you can see the design view.

When the query is highlighted using the View drop-down you can access various views, one of which is the SQL View. When this is chosen, a tab opens with the SQL script of the stored procedure. This SQL appears to be the case when the default city Bern was chosen. You can alter this SQL by removing =N’Bern’ and reverting back to the parameterized query where you need to provide a parameter at run time.

When the SQL View is chosen you can access other related icons for task management as shown in the next picture, like Verifying SQL Syntax, running the procedure, and so on.

You may also right click on the tab of the tabbed window and access the various views as shown.

With the parameterized version, if you "run" the procedure you will be asked for a parameter in the Enter Parameter Value box.

 

When you enter "London" for the city and click on the OK button you will retrieve the columns for the "City" filter as shown.

{mospagebreak title=Stored Procedure in SQL Server}

The next picture shows the stored procedure ByCity on the SQL Express server.

Summary

By using Access projects you are leveraging a great back end database with an extremely user friendly front end. Access Project, which was not supported in Access 2003, is now supported except for special features like attachments, multi-valued fields etc.

Creating a project is not very obvious (a function which should have been user friendly). Also the rationale for saving the stored procedure as a query is also not clear. Furthermore when the parameterized query was run with a default value for the "City," the columns displayed appeared to be duplicated. A modify button or control would have been very useful to modify the stored procedure.  However a beta version of Access 2007 was used in this tutorial and perhaps the final version may have some of these problems modified.

 It will be to the reader’s advantage to read my earlier article on Access projects.

3 thoughts on “Working with Access Projects in Access 2007

  1. You will learn to create an Access project using the latest version(the article was written with the beta)as well as working with stored procedures and therefore it is a must for those who want to work with Access Projects. It can be faster than the previous version after skipping the OleDB interface.

    I look forward to your comments and suggestions, sincerely, Jay

  2. I notice this sentence “Access Project, which was not supported in Access 2003, is now supported except for special features like attachments, multi-valued fields etc.” Could you please verify
    I believe you can create access project from Access 2000 and maybe in earlier version as well (in access 2000 you choose “New” and then “Project (existing Database)”. this will create an ADP database that asks for an sql database with all options available (view, Stored proc) etc…

[gp-comments width="770" linklove="off" ]