Introduction

In this chapter, you will learn about Access projects, how they are different from Access databases, and how they provide a different perspective on the concept of SQL commands. We will also go into the fundamental elements of an Access project and show some of the pitfalls in their use.

Definitions

Overview

We have been a bit vague in defining exactly what database engine we have been working with for the examples in this book. The reason for this is both simple and complex as it almost always is when dealing with Microsoft products. For the most part, we have simply ignored the database engine since almost all examples will work fine no matter what version of SQL or Jet you decide to run. On the other hand, there are a few instances where the engine is critical. The “fun” is in determining in which cases the engine really does matter.

Unfortunately, Microsoft has not made things easy for us. A bit of history is in order. In the early days of Access (versions prior to Access 95), Microsoft took the approach that Access was a consumer product and the internal Jet engine was all that the user would need. FoxPro and Visual Basic were the tools to access big databases, not Access. However, as Access became more popular, Microsoft did add ODBC drivers to Access to pull data from other sources including Microsoft SQL Server and Oracle. While Jet was still the fastest and easiest way to build a database, the other back-end databases were now an additional option. Still, Access and Jet were considered lightweight consumer products that lacked the security and stability of the heavy-duty commercial products. But as Access grew in popularity and the FoxPro market continued to shrink, Microsoft had to rethink its positioning of Access. With Access 2000, Access finally entered the “big leagues” when Microsoft introduced Personal SQL, a product that tried to combine the strengths of SQL Server with the convenience of Jet. We now had Access functioning as a front end to Jet, Personal SQL, and Microsoft SQL Server.

“Why three different database engines?” you might ask. It is easy to give a simple answer. Jet is easy to use, transparent to implement, and does a good job with most applications. It does, however, bog down with more than a dozen concurrent users, lacks real security, and does not have the robustness of a “real” database engine.

Microsoft SQL Server is at the other end of the spectrum. It is a true multiuser system application optimized to handle the processing of large databases. Generally it is installed on a dedicated Windows XP Server box that is loaded with memory and has very fast hard disk access. Microsoft SQL Server has true security built into it and has true transaction processing where all data gets recorded before it is incorporated into the database. This allows for fallback operation and selective restoration of data, ensuring total data integrity. SQL Server also requires constant support to ensure that all operations are optimized and that data backup and system maintenance take place. One database administration consultant says that most of her business comes from companies that try to use SQL without having an administrator at hand. The databases usually work but inevitably they all seem to fail without maintenance.

The third product, Personal SQL, is a compromise and tries to combine the best aspects of Jet with the best features of SQL Server. It provides the data integrity of SQL Server but does not have the need for dedicated system maintenance. It is optimized for efficient data access but does not have the multiuser capabilities of SQL Server. It also does not have all of the support that its big brother has, so you cannot fine-tune it or perform most of the maintenance functions manually. Most important to the user, Personal SQL is a lot cheaper than fullblown SQL Server.

Programmatically, there are differences between SQL and Jet that differentiate the programs. Jet uses the old Microsoft DAO programming to access data. DAO is quick, simple, fast to implement, and relatively easy to debug. SQL generally uses the newer ADO technology. ADO is generally considered the “preferred” method of database manipulation since it is more generic and can be used for other interfaces as well as reaching SQL back ends. It also has many more features than DAO and is considered by Microsoft to be a newer and better technology. On the other hand, it is definitely more temperamental, far more difficult to program, and far more difficult to debug.

Which database engine the user should use is obviously not a simple choice. And much of the time Microsoft does not seem to have a definitive answer. Initially, Microsoft’s position was that Jet was the only engine that should be used with Access. For heavy-duty database crunching SQL Server was better but one should use FoxPro as the tool to get to the data. With the relative demise of FoxPro and the increasing dependence on Access to get to all types of data, Microsoft began providing simple methods of getting SQL data to Access via ODBC drivers and new ways of referencing the data with pass-through queries. The waters were definitely getting a bit muddied on what to use and where to use it.

In the mid-’90s one of the standard questions at Microsoft seminars was “When should a database be in Jet and when should it be in SQL Server?” The answer was more often than not, “When it is too big for Jet, move it to SQL Server.” A definite hedge since they made a point of not defining what “too big for Jet” really meant. They did admit that it was dependent on the size of the database, the number of users, the complexity of the queries, and the load on the network, but even these definitions were left intentionally vague.

Microsoft even tried to make it easier to migrate from Jet to SQL during this time with upsizing tools to migrate databases from Jet to SQL. While good in theory, most of these early tools were more flair than real substance. While tables would upsize, unless the designer had originally thought in terms of SQL, the upsized tables performed slower rather than faster. The tool merely moved the tables over to SQL, leaving all of the query processing local to the machine where the Access program resided. The net effect was that you had all of the overhead of SQL and all of the overhead of Jet. Most people were extremely disappointed when there was no speed increase when their databases were upsized. In all fairness, however, the tools did get better as subsequent versions were released and Microsoft’s teaching tools began to introduce “better” ways of designing queries and table links.

The migration to SQL improved to the point that with the introduction of Office XP, the official Microsoft line was that Jet was dead and people should start moving to Personal SQL, which was included with Office. Microsoft did stress, however, that Personal SQL was not to be confused with its big brother, Microsoft SQL, although most of the features were identical. Personal SQL was designed for smaller systems and while the features were identical, it had not been optimized and tweaked the way Microsoft SQL had been and was definitely not a database engine for anything but the smallest environments. To further encourage the migration away from Jet and to offset the fact that Personal SQL did not come with tools to build tables or queries for server-side operations, Microsoft included a new type of Access database: the Access project that exclusively used SQL Server-like tables accessed by the SQL engines. A new method of programmatically accessing the data was also developed called ADO. ADO incorporated access to many SQL features that were not available with DAO. Features like constraint checking and altering Unicode compression will produce error messages if attempted in DAO or through the Access query grid. They work fine in ADO.

All was not perfect, however. When Microsoft declared Personal SQL was not a replacement for Microsoft SQL, they meant it. While easy to use, Personal SQL had one critical flaw: It could not be used concurrently by more than five people. In short, if you wanted to use SQL with more than a handful of people, you had to get SQL Server and pay that program’s much higher price. Most people reacted to this limitation with a general decision to stick with Jet.

Then along came Access 2003. It appeared that Microsoft, which had been apparently abandoning Jet, was now embracing it again with a new version. ADO, while highly touted in most of the Microsoft literature for Access XP, was almost nonexistent in Office 2003. People continued using DAO.

This brings us back to the topic of which version of SQL we have used in this book. For the most part, we have stuck with “good old” Jet. But the Access projects feature does deserve additional mention since it provides an easier bridge to true SQL.

At least it did until Access 2007 came out and Microsoft took yet another about-face. After dealing with a lackluster reception to projects and an effort to try to move people off Access entirely and to .NET and SQL Server Reporting Services, Microsoft has abandoned projects (at least for right now). While you can still open a project in Access 2007, you cannot do anything to it in the way of making edits. The author still likes projects, however, and finds them very useful — just not in Access 2007. So, for the rest of this chapter, we will drop back to Access 2003.

Differences between Access Projects and Access Databases

The first thing you will note about Access projects is that they are a totally different entity from Access databases. When you want an Access receptacle you can create a database or you can create a project — you cannot create a hybrid between the two designs.

A project is created when Access is opened and the user selects New and then Project using new data. See Figure 16-1.

Figure 16-1. New project menu selection

The next dialog box that appears allows you to name the file and select the location. This process should be very familiar to the user since it is the same process used to create a traditional Access database. See Figure 16-2.

Figure 16-2. File New Database dialog

You need to specify that the file is of type Microsoft Office Access Project (*.adp), but the process is identical to creating an Access database.

The next window is new for projects. Since projects depend on SQL Server or Personal SQL for the database engine, you will next have to select the location of the SQL database through the SQL Server Database Wizard. It is assumed that you either have installed Personal SQL on your machine or have a network connection to an SQL Server. If you don’t, you will not be able to proceed past this step. Microsoft has made it very difficult to mess up this step!

Figure 16-3. SQL Server Database Wizard screen

The first thing you will need to do is select an SQL Server from the drop-down (Figure 16-3). All SQL Servers available to your computer should appear in this drop-down list. We have noticed that once in a while, SQL gets confused and does not include the local server in this list. Just entering the name of the local server is often enough to get the wizard to go out and find the server. Next, fill in the Login ID and Password boxes. If you have set up the SQL Server or Personal SQL to use system passwords, you might be able to skip entering the ID and password. Finally, enter the name of the new SQL Server database or enter the name of an existing database that you plan on using. Clicking on the Next button completes the connection process and you will return to the project window.

Project Window

You will notice that this window is very similar to the one that you have for an Access database, but there are a few differences. The first big difference is the inclusion of Database Diagrams in the Objects list, as shown in Figure 16-4.

Figure 16-4. Project window

The second difference is a bit more subtle but even more important. Note that the SQL database is included in the title bar. If you have just loaded an Access project, you do not have the link to the SQL back end until you log in to it. This can be noted by the “disconnected” indicator in the title bar as shown in Figure 16-5.

Figure 16-5. Not connected

Tables

The real differences between a project and a database appear when you begin working with objects, beginning with tables. The layout of a project table is slightly different from a database table, as Figure 16-6 shows. Nulls are indicated as a primary attribute, not just a property. Second, there is no special data type for autonumbers. Instead, an autonumbering index is built by selecting the data type as int (integer), then selecting the Identity property of Yes. Also note that the identity seed and identity increment can be set directly in the Table view.

Figure 16-6. Table view

The Table view also gives you the full spectrum of data types, unlike database tables. Some of the possibilities are shown in Figure 16-7.

Figure 16-7. Data types

We have previously shown the various types of data available to Jet and how the Jet data types map to SQL. Here is the same information from Microsoft Office Online presented with an SQL perspective.

Table 16-1. Data types

Database Diagrams

Another difference between a project and a database appears in the database diagram object type. This is the equivalent of the Relationship window that is opened via the Tools | Relationships toolbar item.

The database diagrams are built in the same way that the relationship screen is with one major exception: Unlike the relationship screen, each table can be added to a diagram only once. See Figure 16-8.

Figure 16-8. Database diagram

Once the tables are added to a diagram, joins can be constructed just like in the relationship screen. The display of the table relationships is a bit different from that of the relationship screen (Figure 16-9.). For example, the fields that compose the link are not apparent on the database diagram unless you wait for the pop-up. On the other hand, if you go into the properties of each table within the database diagram, there is far more information available than can be pulled from the relationship screen.

Figure 16-9. Linked tables in a project

One of the biggest improvements of the database diagram over the relationship screen is that Microsoft has provided a significant amount of control over how the tables appear on the screen. Figure 16-10 shows the pop-up menu for this window. With a single click of the mouse, the tables can be rearranged, moved around on the screen, and formatted for printing.

Figure 16-10. Database diagram pop-up menu

The other area of major improvement is the Properties menu. A tremendous amount of information about every table, field, index, and relationship can be derived from the Properties menu. And to go even further, selections made in the combo boxes on the Properties pages are immediately reflected in the database diagram. Select a different table and the highlight shifts to the newly selected table. See Figure 16-11.

Figure 16-11. Database diagram Properties window

Queries

While tables and database diagrams have many cosmetic changes that make them different from the Access database equivalents, the real differences between projects and databases lie in the area of queries. Projects are designed with SQL back ends in mind, and the fundamental concepts of project queries highlights this. The Queries tab shows the three primary types of SQL queries: functions, views, and stored procedures (Figure 16-12). Unlike the Access database, you can design each query based on how it will function in the SQL environment.

Figure 16-12. Project query creation selection

Notice that there is a definite separation in the classification of queries. Views are basically the linked combination of tables and other views. They can best be compared with select queries in Access databases. Stored procedures are the equivalent of make-table queries, update queries, delete queries, and append queries. Functions are similar to views but allow parameters to be passed and get preprocessed in SQL rather than Access.

Views

Views are the Access project equivalent of select queries with the same basic functionality and limitations. Like select queries, views do not occupy space as an independent collection but reference the underlying dataset of the tables. The net effect is that anything you do to the data in the view gets reflected in the underlying table. Views are great for predefined slicing and dicing of a database but are rather inflexible since they do not permit run-time parameters.

Figure 16-13. Project view

Note that the view shown in Figure 16-13 presents the join between the tables in a slightly more informative manner than does the Access database.

Figure 16-14. Database select query

While databases allow you to enter alias names, displaying these aliases is a bit cumbersome. In an Access database the way to construct an alias is to place the alias name on the field line separated from the field source by a colon. See the custname field in Figure 16-14 for an example of this. This is definitely harder to read than the format for views, which have a distinct column for the alias names.

The second big difference in the layout of the view is that you can tell immediately if a field is present in the view by noting whether the check box in front of the field name is checked or not checked. You can also add fields to the query grid by checking the boxes next to the field names. This is a minor feature but one that is extremely valuable when the query consists of many fields.

The next big difference between views and select queries is the ordering of fields: Views have fields in a vertical format; select queries are in a horizontal arrangement. This is another minor difference but one that makes the query a lot more readable.

The joins have been enhanced in projects with a few new features that can be entered from the query grid. These can be observed by noting that the connector between the two tables now has a symbol that graphically describes the join. In Access projects, when you have a join between records in one table that match records in a second table, the line is a solid line. When the join includes all the records from one table and only the matching records from the second table, the line is replaced with an arrow. The tail of the arrow represents the table from which all records are taken and the head of the arrow represents the table from which only matching records are present. The Access project represents this by the diamond shape in the middle of the join, as shown in Figure 16-15.

Figure 16-15. Join representation

To show that all records are to be taken from the orders table, the center diamond is modified as shown in Figure 16-16.

Figure 16-16. Right outer join

In a similar manner, all records from the members table are represented by the symbol pointing the opposite direction as shown in Figure 16-17.

Figure 16-17. Left outer join

The new addition is when you wish to take all records from both tables regardless of whether they have a corresponding record in the other table. See Figure 16-18.

Figure 16-18. Full outer join

The join is built in the same manner as a join is built in an Access database. Right-clicking on the join brings up the pop-up window from which properties can be selected. In the Properties window are two check boxes for which records to include. See Figure 16-19.

Figure 16-19. Properties window

The Properties window also allows the user to set up the join condition (Figure 16-20). In Access databases, it is assumed that the join is going to be an equal join where the join field in the first table is equal to the join field in the second table. An Access project allows you to set the condition in the Properties window to other comparisons including not equal, greater than, less than, less than or equal, and greater than or equal.

Figure 16-20. Properties window options

Selecting not equal (<>) for the join condition changes the SQL statement as expected, as shown in Figure 16-21.

Figure 16-21. Join property SQL view

Functionally, views and select queries are identical with the primary difference being that views are stored on the server and queries are calculated locally. In the case of a remote SQL Server, the view calculations take place on the server and produce far less transfer of data.

Stored Procedures

Stored procedures are the action queries of the Access project realm and are the only query type that can represent an update, append, make-table, or delete query.

Figure 16-22. Database project stored procedures

As Figure 16-22 shows, the stored procedure allows the user to set up the initial query, then determine what type of action is going to take place. Here are several interesting observations on stored procedures. First, while a standard select query format is possible, the recordset that is derived from the stored procedure is not editable. Second, when a stored procedure is used to construct an update query, only one item can be in the upper portion of the query grid. This is easily handled, however, by making a view composed of several tables as the source. The view can have as its source whatever you wish, but from the standpoint of Access, it is a single entity.

Functions

Visually, functions appear identical to views as Figure 16-23 shows.

Figure 16-23. Database project functions

The data grid view is very deceptive, however, since there is a great deal that is not revealed. To get under the covers, so to speak, we have to look at the SQL view for both types of queries. If you display the SQL equivalent of the view, you get a standard select query as shown in Figure 16-24.

Figure 16-24. View with SQL

The SQL produced by a function is a little bit different. Notice that it actually returns a value, in this case a table. In short, a query function is just like all other functions in programming. It can take a variety of parameters and generate a single result as the output. In the case of the function query, the output result isatable.

Figure 16-25. Function as SQL

What makes the function far more powerful than the view is what appears in the second line of the function shown in Figure 16-25. Notice the open and close parentheses. Just like any other function, you can add parameter values for the function to process. Generally these parameters are such things as filter values, but one can be extremely creative in defining a function query through parameters. Another important aspect of these function queries is that the compilation of the query occurs when the query is stored, not when it is run. This can lead to far faster processing since the elements of the query do not have to be evaluated every time the query is run.

Now we get into the fun stuff that we have been hinting at. Instead of building a new query from the three options given in the query display, build a new query by selecting New from the menu bar. Notice that there are a few different options, as shown in Figure 16-26.

Figure 16-26. New Query window

Selecting Create Text Scalar Function produces the following function template.

Figure 16-27. Function query template

Notice that what we end up with is a skeleton layout for producing a query. It is here that we can define our parameters and build our SQL statements. When complete and stored away, Access takes the input and creates a function from our input. In short, we have come full circle. We began our discussion of Access projects by showing how they were like Access databases and provided complete graphical tools for building database elements. Our final example uses the graphical elements to once again go back to simple text for entering SQL statements.

Summary

The Access project is a powerful tool for building SQL interfaces. It makes the transition from the Access Jet database model to the more powerful SQL Server engine by providing a familiar interface. While there are some subtle (and not so subtle!) differences, the overall effect is to provide the user with a comfortable way to enter the world of true SQL.

Personally, we are sorry to see that Microsoft has discontinued projects. It did provide a very simple and convenient way of accessing MS/SQL databases and it was very easy to use. But who is to say that they are gone permanently. Like everything at Microsoft, there are no definites.