Defining Databases and Tables with Access Projects

Developing SQL Server databases with Access 2000 offers you the optimal combination of power, fast results, and minimal costs—a solution that will satisfy the end users, CEO, and CIO in your organization. With Access 2000, Microsoft added SQL Server data-definition capabilities to its popular desktop database manager.

Access 2000 lets developers build SQL Server solutions from a familiar database container interface. And non-SQL Server developers can tap the power of SQL Server with the ease of Access. Also, Access applications built for SQL Server inherit the hardware scalability of Microsoft's enterprise database solution. Access 2000 ships with the Jet database engine and the new Microsoft Database Engine (MSDE). Because MSDE employs the same core database technology as SQL Server 7.0, Access developers can easily migrate solutions from small workgroups to departments and enterprises.

To illustrate the differences between the new Access project and a traditional Access database file, I'll use detailed examples to demonstrate how to create SQL Server databases and tables from Access 2000. Also, I'll demonstrate how to construct stored procedures with familiar Transact SQL (T-SQL) statements inside Access projects.

What's an Access Project?

In Access 2000, Microsoft introduced a new file type (.adp) to manage databases. An Access project works with three types of database servers, SQL Server 6.5 with Service Pack 5 (SP5) installed, SQL Server 7.0, and MSDE running on Windows 9x or Windows NT 4.0. The .adp file extension denotes an Access project. This file works natively with SQL Server, as an Access database file (.mdb) works with the Jet database engine. Traditional Microsoft Access applications can consist of only one .mdb file because .mdb files can store database objects, such as tables and queries, and application objects, such as forms and reports. Applications that rely on Access projects always segment the database objects from application objects. Access stores the application's forms and modules separately from the database tables they use. SQL Server manages the database objects, but developers manage application objects within the .adp file.

Screen 1 and Screen 2 compare the database containers' GUIs for Access project and Access database files. Screen 1 shows the traditional container user interface, and Screen 2 shows the new design of the container object for Access projects. Note that Microsoft removed two items from the container object for Access projects, Create table by using wizard and Create table by entering data. The remaining wizard, Create table in Design view, presents a slightly different grid layout control in Access projects from that in Access database files. SQL Server developers will find the Access project user interface familiar because it matches the Enterprise Manager's user interface. You can use Access project features, such as the ability to set and reset identity seed and increment values. This ability requires programming for Jet database files and it wasn't available in earlier versions of Access. The Access project database container shows four database objects—Tables, Views, Database Diagrams, and Stored Procedures—and five application objects. Developers can expose triggers for any table by right-clicking the Tables icon in the database container objects group and choosing Triggers from the context-sensitive menu that opens.

The Access project database container graphically underscores another Access project innovation. The container integrates database objects with application objects. Notice that the database container gives you access to collections of forms, reports, pages, macros, and modules. Users can invoke familiar wizards for creating forms and reports. However, users can now work with custom databases and user data- definition capabilities for SQL Server databases instead of databases created by others, which developers had to use before Access 2000. It's easy to create a simple bound form, highlight a table name in the Tables collection, and click the AutoForm tool on the Database toolbar. When you make changes to data displayed in a form, it revises the original source data table, which wasn't easy in prior versions of Access. You can easily modify this behavior by altering the Recordset Type property in the Data tab on the Property dialog box (available when you open a form in the Design view). Updateable Snapshot is the default setting. Changing the property to Snapshot converts the form to a read-only format.

Creating an Access Project

You can create a new Access project file two ways. First, you can create a new project with a new database, add new database objects to it, and import objects into the new database. Second, you can create a new Access project for an existing database. This approach lets you create or import a custom set of application objects that will work with the objects in an existing database.

To build a new database when you create a new Access project from the File menu, click the New icon, and choose the Project (New Database) icon in the New dialog box. The File New Database dialog box will open and present a default name that you can change. Screen 3 shows the SQL Server Database Wizard dialog box, which lets you enter a server name, login ID and password, and database name. Select any connected database server name in the combo box. For database servers residing on NT computers, you don't need to enter a login ID and password. If you don't enter this information, the wizard will default to trusting the NT login account of the current user and NT integrated security. For database servers running on Windows 9x computers, you must specify a login ID with an appropriate password. Clicking Next brings you to another dialog box, which lets you continue creating a database after you click Finish.

Instead of making a new database for a new .adp file, you can connect a new .adp file to an existing database. The Data Link Properties dialog box specifies a connection for the .adp file to connect with a specific database. The connection is exclusively for the use of the .adp file. To make this connection, launch the process by clicking the Project (Existing Database) icon in the New dialog box from the File Menu. Next, specify a custom file name for the Access project or accept the default. Then when you click Create, Access presents the Data Link Properties dialog box where you specify the database server name, type of security (NT integrated or SQL Server), and a database name. Click OK to connect the Access project to the existing database that you designated in the Data Link Properties dialog box.

Creating Tables

After creating a new database or linking to an existing database, you can use one of three approaches with Access projects to add tables to a database. First, you can add a new table through the Tables collection of the database container. To add a table through the Tables collection, highlight the Tables icon in the Objects group and click the New icon, as Screen 2 shows. This action opens a dialog box, which lets you choose a name for your table. After specifying a table name, you can enter column names. The Tables collection is convenient for specifying column names and data field types for a single table, as Screen 4 shows. Second, you can add a new table by using the Database Diagrams collection in the Objects group. This approach adds value to situations where you're creating more than one table or linking a new table to an existing table. You can view a table's column specification and its relationship to other tables in the database. Third, you can use T-SQL and the Stored Procedures collection in the Objects group to programmatically create a table.

Screen 4 shows the specifications for a table named T2, which has two columns. In the column tt0, I assigned an int data type to use the first column as an Identity column. This column assigns an automatically incremented value to each row in a table. After selecting the Identity column, you can designate its seed and increment values by typing values in the Identity Seed and Identity Increment columns that appear in Screen 4. The tt0 values start with 0 and increment by 2 with each new row. You can also reset these values anytime. Your changed settings will affect the next new row entry for a table.

Screen 4 also shows the Primary Key tool that you can use to make a column the primary key for a table. Click anywhere in a row that specifies a column, and choose the Primary Key tool. If you want to specify multiple columns as a primary key, highlight all columns before choosing the tool. If you need to change a table's primary key, select the columns you want to change before clicking the Primary Key tool.

You can also create new tables from a database diagram. When you create a table from a diagram, you either create a new diagram or choose an existing one to act as a container for your data definitions. To make a new diagram, select Database Diagrams from the Objects group, then click New. To open an existing diagram, double-click it in the database container. You can add an existing table to a diagram by right-clicking in any blank area of the diagram and choosing Show Table, which opens a dialog box that lets you drag tables to the diagram. The right-click menu also includes a New Table command.

You can create tables in database diagrams and specify constraints, such as foreign keys, between them. Screen 5 shows a diagram with two tables. T2, from the previous example, appears in a Column Names format. I added this table to the diagram from the Show Table dialog box. T3 appears in the Column Properties format. This layout appears from a blank diagram area after you choose New Table from the right-click menu. The table's basic design is complete. Now you need to create a foreign-key constraint for T3. In Screen 5, use the mouse to drag the tt0 field from T2 to the tt0fk field in T3, then release the mouse to open the Create Relationship dialog box, which Screen 6 shows. This dialog box defines the foreign-key relationship, but you can manually adjust it if necessary before committing the setting. After you click OK in the Create Relationship dialog box, Access updates the diagram with a line and key connecting T2 and T3 to show the relationship between the two tables.

Programmatically Creating Tables

As easy as it is to create tables manually in Access 2000, performing the same tasks programmatically offers several distinct advantages. First, a programmatic approach leaves a trace, a SQL script that continues to exist, after Access performs the operations to generate a new table. Second, if you can create the table programmatically, you can easily revise the table's design. Third, you can conditionally create tables only when circumstances demand them.

Screen 7 shows an Access project with two T-SQL stored procedure code listings—MakeTableT2 and MakeTableT3—for programmatically creating a pair of related tables. These listings duplicate the table design I explained in the previous example. The two stored procedures appear as listings when you open them with the Design view icon in the database container. T-SQL developers will feel comfortable with the syntax in these procedures, and traditional Access developers can learn a few new tricks from this way of defining tables.

The MakeTableT2 procedure starts by conditionally dropping any prior version of T2 that is in the database. Because T3 references T2 through a foreign key, the procedure must drop the foreign-key constraint in T3 before it can remove T2 from the database. Attempting to drop T2 without first dropping the constraint violates referential integrity. After restoring the definition of T2, the procedure closes by restoring T3's foreign-key constraint. The Identity settings result in successive tt0 values of 0, 2, 4, and 6. The INSERT statements match those values for tt1 with 1, 3, 7, and 15.

The MakeTableT3 procedure also begins by dropping any prior versions of T3 that are in the database. The identity seed and increment values for the primary key in this table are both 1. These are the default values, so you don't have to set them. The line defining the ttt0 field reads

ttt0 INT IDENTITY (1,1) PRIMARY KEY,

The table definition closes by specifying the foreign-key constraint. Because of this constraint, it's essential that the INSERT statement specify for the tt0fk field an even value in the 0 through 6 range (assuming you run MakeTableT3 immediately after running MakeTableT2). A value outside of the range violates the foreign-key constraint and produces an error.

Empowering Capabilities

Access 2000's new data-definition capabilities for SQL Server databases can empower your organizations to build SQL Server applications faster and more easily than ever before. This same technology can also help ease problems in migrating small workgroup solutions to multidepartment and enterprisewide solutions.