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.
End of Article
Prev. page
1
[2]
next page -->
