Create a Access2007 table

Once you have finished designing the Access2007 table, you can begin creating the Access2007 table, which is to construct the fields in each table, define the data types for each field, and set the properties of the fields.

Access2007 Creating a table is divided into creating a new database and creating a table in an existing database. When you create a new database, a new table is created automatically. Creating a table in an existing database can be done in the following five ways:

(1) Create an empty table directly.

(2) Create a table using a template.

(3) Create a table using Design view.

(4) Import or link to a table from another data source, such as a Excel2007 workbook, a Word2007 document, a text file, or another database.

(5) Create a table from a SharePoint list.

First, create a new table in the new database

Example 1, create a "teaching management" database, and establish a "tie" table, which is structured as shown in table 1. Action steps are as follows:

(1) On the File menu, click the New command.

(2) In the right pane, in the File name text box, type the filename "Teaching management" and choose the saved path as "E/example".

(3) Click the Create button.

The new database opens, creating a new table named "Table 1" and opening it in Datasheet view.

(4) In Datasheet view, double-click the Add New Field column heading and enter the new field name "Name" in the table, as shown in Figure 1. A Add new field column is added to the right.

(5) Select the ID field column. On the Datasheet tab, in the Fields and Columns group, click the Rename button, and then enter the column heading, "ID."

Figure 1 Adding the "Fasten name" column heading

(6) In the Quick Access Toolbar, click the Save button.

(7) in the Open Save As dialog box, enter the name of the table "fasten", and then click the OK button, as shown in Figure 2.

Figure 2 Input Table name

Table 1 Department

When you have finished modifying the field headings, you can enter the field values directly. After you enter a field value, Access2007 automatically sets the data type and properties for the field. This approach is simple and quick, but there are drawbacks. Sometimes the field data type is not appropriate and the field size may be redundant. To do this you need to modify the field's data type and field properties in Design view

(8) in the lower-right corner of the Access2007 working window, click

button to open the Design view of the table. Note that this automatically switches to the Design tab, as shown in Figure 3

(9) In Design view, select the "Line Name" field and set the field size to "10", as shown in Figure 4.

Figure 3 "Design" tab

Figure 4 Setting the size of the "Line name" field

Once the settings are complete, save the "fasten" table again.

Creating tables using templates

When you create a contacts, tasks, issues, events, or assets table, you can use the Access2007 built-in table templates for these topics.

Example 2 creates a "communications record" database in which a contact table is created. The specific steps are as follows:

(1) On the File menu, click the New command.

(2) In the right pane, in the File name text box, type the file name "Communication record" and select the saved path as "E:/example".

(3) Click the Create button.

(4) On the Create tab, in the Tables group, click the table Template command.

(5) In the Open Table template Drop-down list shown in Figure 5, click the Contacts template, the table created based on the Contacts table template is inserted into the current database, as shown in Figure 6.

Figure 5 "Table Templates" list

Figure 6 New table created after inserting the contacts template

(6) Click the "Save" button, in the Open Save dialog box, after the table is named to complete the creation of the table

If a table created using a template does not fully meet your needs, you can modify the table. Simple Delete or add fields can be manipulated in a data view, and complex settings need to be made in Design view

Create a table using Design view

Using a template to create a table is convenient and quick, but it has some limitations. Creating a table using Design view is a flexible but complex approach that takes a lot of time. For more complex tables, it is usually created in Design view.

Example 3 in the Teaching Management database, create the results table. The structure of the score sheet, as shown in table 2.

Table 2 structure of the score table

The steps to build the results table using Design view are as follows:

(1) Open the "Teaching management" database. On the Create tab, in the Tables group, click the table Design button.

(2) Open the Design view of the table, enter the field name in the field name according to table 2, select the appropriate data type in the data type column, and set the field size in the general Properties pane as shown in Figure 7.

(3) Put the cursor in the field selected position, press and hold the left hand does not open. Select the secondary and course number two fields and release the left button, when two fields are selected and the background is black. Right-click, on the shortcut menu, click the Primary Key button, or on the Design tab, click the Primary Key button. When Setup is complete, a key graphic appears on the field selector for the school number and course number, indicating that the fields are primary keys, as shown in Figure 8

(4) Click the "Save" button to save the table with the name "score".

Figure 7 Design view of the score sheet

Figure 8 The result of designing a primary key

Create a table by importing

You can create a table by importing information that is stored from another location. For example, you can import information stored from Excel worksheets, SharePoint lists, XML files, other Access databases, Outlook2007 folders, and other data sources.

Example 4, to import "schedule. xls" into the teaching management database, follow these steps:

(1) Open the "Teaching management" database, in the Ribbon, select: External Data tab, in the Import group, click the Excel command button

(2) as shown in Figure 9, in the Open Get External Data dialog box, click the Browse button.

Figure 9 "Get External Data" dialog box

(3) In the Open Open dialog box, locate the lookup range in the folder where the external file resides, select the imported data source file schedule. xls, and click the Open button.

(4) Return to the Get External Data dialog box and click OK.

(5) In the open Select Appropriate worksheet or Range dialog box, click the Next button directly.

(6) In the open "make sure that the first row contains columns" dialog box, select the first row contains column headings check box, and then click the Next button.

(7) In the open specify the information for each field you are importing dialog box, specify the data type of the course Number field as text, and the index entry is "Yes (No Duplicates)." Then set the other field information in turn, set the data type of "hours" as "integer", the data type of "credits" is "byte", and leave the default settings for others. Click the Next button

8 in the Open Define Primary Key dialog box, select the My own primary key radio button, ACCESS2007 automatically select the Course Number field, and then click Next.

(9) In the Name dialog box for the specified table that opens, in the Import List text box, enter course, and then click Finish.

Here is the process of creating a table using the import method.

(10) In the Save Import Step dialog box that opens, deselect the Save Import Steps check box, and click the Close button.

Note: The Save import Step is Access2007 new features, for users who often perform the same import operations, you can save the import step, the next time you can quickly complete the same import.

V. Create a table from a link

You can create a table by linking information that is stored from another location. For example, you can link information stored in Excel worksheets, SharePoint lists, XML files, other Access databases, Outlook2007 folders, and other data sources.

When importing information, a copy of the information is created in a new table in the current database. Conversely, when you link information, you create a linked table in the current database that represents an active link to the existing information stored in another location. Therefore, when you change data in a linked table, you also change the data in the original data source.

The operation of the linked table is essentially the same as the import table, and there is no repetition here.

In order to ensure the security of the database, or when using the ACCESS2007 database in the network environment, it is necessary to split the Access2007 database into two separate databases. Save the table in the back-end database, and the front-end database saves all objects except the table, such as queries, forms, and so on. The front-end database is linked to the tables in the back-end database.

VI. Define PRIMARY KEY

In Access2007, each table usually has a primary key. By using a primary key, you can not only identify each record in the table, but also speed up the index of the table.

In Access2007, there are three types of primary keys: AutoNumber, Word field, and multiple fields. Assigning an AutoNumber field as a table primary key is the simplest way to define a primary key. The main feature of an AutoNumber primary key is that when a new record is added to the table, the primary key field value is automatically added to 1. However, when you delete a record, the primary key values for the AutoNumber are not contiguous and are not automatically adjusted. If the value of a field in a table can uniquely identify a record, such as course number for the schedule, you can designate the field as the primary key. If the table does not have a field value that uniquely identifies a record, you can consider selecting multiple fields to be grouped together as primary keys to uniquely identify records, such as the "Curriculum", where you can combine the "number" and "Course number" two fields as the primary key.