Access to SQL Server Part 3: Getting Started with Access Projects

Introduction

This is the final article of a three-part series discussing ways to use MS Access as a front end to SQL Server. In the first article, I demonstrated how to use the Upsizing Wizard to create a new SQL Server database from an Access database. In the second article I describe how to link an Access database to an existing SQL Server database. In this article I cover how to get started using Access Projects.

Designing a database, creating tables, forms, queries, and reports in Access are beyond the scope of these articles. I'm going to assume that you know that clicking "Next" moves you to the next step in a wizard and know to click "OK" to accept changes. I also will assume that you have the necessary privileges and permissions in SQL Server.

Create an Access Project

The Access Project type of Access application is tied very tightly to SQL. In fact, when using this type of application, the data can only be stored in SQL. Think of this as an alternate SQL GUI. Tables created in the Access Project are actually created in SQL. One big advantage of this approach over linking in a traditional Access database is the use of stored procedures. In fact, you can even create the stored proc within the Access Project.

To create an Access Project, follow these steps:

• Open Access. Click File --> New....

  Choose Project using existing data... to link to an existing SQL database.

  Or choose Project using new data... to create a new SQL database.

• The File Save dialog opens, enter a location for your Access ADP file.
• Fill in the server name, your credentials and the database name and then

  click OK.

View the steps to create an Access Project

Figure 1. The Access Project. Tables are stored in SQL Server.

I now have what looks like an ordinary Access database, but the data and table definitions are stored in SQL. Click on the Queries section to see the difference. You can create functions, views, and stored procedures. These objects are actually stored in the SQL Server database. The forms, reports and VBA code you create are stored in the Access database. The data types used in the table definitions are SQL data types, not Access data types. You can even easily link reports to stored procedures. This is possible in traditional Access but not very easy to do. If the stored procedure requires parameters, assign the parameters to the "Input Parameters" property of the report.

Figure 2. Functions, views, and stored procedures are actually located in the SQL database.

Other Access Project Features

If you need to connect your Access Project to a different server or database, you'll find the Data Link Properties dialog under File--> Connections.

Wouldn't it be nice to be able to perform some administrative functions from within the Project? Look in the database utilities where you'll find several administrative features including a way to back up the SQL database.

Figure 3. Some administration tools.

One thing that is missing is a version of Query Analyzer. The only way to run SQL statements is by saving the statements first in a view or stored procedure. Well, I guess you probably don't want to give QA to your customers anyway, so I suppose that's fine. Overall, I'd say that Access Project is a very nice tool to quickly create a client for your SQL database.

Conclusions

While MS Access can't do the work that SQL Server does, it is quick and easy to set up a user interface and reports with little or no programming. It provides a nice front end to SQL databases, and it is very easy to link to many other data sources as well. In this series, I have shown you how to link from Access to SQL using three different methods. The method you choose will depend on your application and preferences.