3 4
You don’t have to create a project from scratch to get an idea of what a project looks like. You can simply open the NorthwindCS (CS stands for "Client-Server") project provided with Access 2002 by choosing Help, Sample Databases, Northwind Sample Access Project, as mentioned. If you have some version of SQL Server installed and you chose to install the project the first time you opened it, the NorthwindCS project will open much like the equivalent Northwind database, as shown in Figure 19-4.
Figure 19-4. Here’s the NorthwindCS project window—the project is in Access 2000 format.
As you can see, an Access project and an Access database have similar components except that the project has a new object named Database Diagrams. Tables and queries of a project, however, differ significantly from their Access database counterparts. Tables, queries, and database diagrams of a project are discussed in the following sections.
Tables in an Access project look like local tables in an Access database. Although they’re actually SQL Server tables, they don’t have the arrow icon indicating that they’re linked tables. When you open one of the NorthwindCS tables in Design view, it will look different from an Access table; SQL Server uses different field data types than Access. Table 19-1 lists the Access field data types and their SQL Server equivalents.
Table 19-1. Access and SQL Server field data types
Access data type | SQL Server data type |
Yes/No | bit |
Number (Byte) | tinyint |
Number (Integer) | smallint |
Number (Long Integer) | int |
Number (Single) | real |
(no equivalent) | bigint |
Number (Double) | float |
Currency | money smallmoney |
Number (Decimal) | decimal numeric |
Date/Time | datetime smalldatetime |
AutoNumber (Increment) | int (with the Identity property defined) |
Text (n) | varchar(n) nvarchar(n) |
Memo | text |
OLE Object | image |
Number (Replication ID) (globally unique identifier [GUID]) | uniqueidentifier (SQL Server 7 or later) |
Hyperlink | char, nchar, varchar, nvarchar (with the Hyperlink property set to Yes) |
(no equivalent) | varbinary |
(no equivalent) | timestamp |
(no equivalent) | char nchar |
(no equivalent) | sql_variant |
(no equivalent) | user-defined |
Figure 19-5 shows the database and project versions of the Northwind Employees table side by side. The field data types have been converted to their SQL Server equivalents, and you can see that some of the field properties displayed on the General tab in the database’s field properties sheet are displayed as columns in the project, which makes it easier to review and adjust the properties.
Figure 19-5. The Employees table looks different in the Northwind database (on the left) than it does in the NorthwindCS project (on the right).
The queries you see on the Queries tab for the project are Access project components that act as interfaces to SQL Server views and stored procedures (and, theoretically, user-defined functions, although there aren’t any in the NorthwindCS project). Views are the SQL Server equivalent of Access select queries, and they have the same icon as select queries in an Access database. Stored procedures are roughly equivalent to action queries in an Access database; they have a different icon. (See Figure 19-6.)
Figure 19-6. Views and stored procedures are listed on the Queries tab for a project.
See the sections "Project Views," and "Stored Procedures," for more details on project views.
Database diagrams are graphical representations of part or all of a database schema, including tables, columns, and relationships among them. In Access databases, you can have only one Relationships diagram, since there is only a single Relationships window. Projects, however, can have multiple database diagrams, which means that you can create a separate diagram for each set of relationships in the database, instead of putting them all into a single huge diagram. Figure 19-7 shows the Northwind database Relationships diagram.
Figure 19-7. The Northwind Relationships database diagram shows links between tables.