Home

Using a Table

 

Selecting a Table

Before performing most operations on a table, you may need to select it. This is a routine operation that is usually done transparently but in some cases, it is a prerequisite. If you select a table, some operations you perform may affect it, depending on how such operations are carried out. A table indicates that it is selected when it is highlighted:

In this example, a table named CD@Home is selected.

To select a table, in the Tables section of the Database window, you can simply click it, once. If another table or another item is already selected in the Tables section of the Database window, you can press the up or the down arrow key continuously until the table is selected.

To programmatically select a table, you can use the DoCmd object that is equipped with the SelectObject() method. The syntax to use would be:

DoCmd.SelectObject acTable, [objectname][, indatabasewindow]

The first argument must be acTable in this case because you want to select a table. The second argument is the name of the table you want to select. If you want to select the table and only highlight it in the Database window, then pass the third argument as True.

Here is an example:

If the table is already opened (in the next section we will see how to open a table) and it is displaying, it is most likely in the background. If you omit the third argument or pass it as False, the table would be displayed in the foreground. If the table is not opened and you omit the third argument or pass it as False, you would receive an error. You can use a conditional statement and error handling to make sure the user doesn't see this nasty dialog box.

Opening a Table

By default, if you open a database in Microsoft Access, all of its tables are closed. Before using a table, you may need to open it first and this depends on what you want to do with the table. A table can be opened in one of three different views:

  • The Datasheet View, also called normal view, of a table displays as a spreadsheet: it is divided in (vertical) categories and horizontal rows:
     

     
    To open a table in normal view, from the Tables section of the Database window, you can either double-click the table or you can right-click it and click Open
  • The Design View of a table is used for table design
     

     
    To open a table in Design View, from the Tables section of the Database window, right-click the desired table and click Design View. Alternatively, you can first select it. Then, on the toolbar of the Database window, click Design
  • The Preview view of a table resembles a report and allows you to see what it would look like if you wanted to print it
     

     
    To open a table in Preview mode, from the Tables section of the Database window, right-click the table and click Print Preview

To programmatically open a table, you can use the DoCmd object that provides the OpenTable() method. Its syntax is:

DoCmd.OpenTable tablename[, view][, datamode]

The first argument of this method is the name of the table that you want to open. The second argument is a constant value as follows:

View Name Result
acViewDesign The table will display in Design View
acViewNormal The table will display in Datasheet View
acViewPreview The table will display in Print Preview

This second argument is optional. If you omit it, the acViewNormal option applies.

The third argument, also optional, has to do with data entry, which we haven't reviewed yet. This means that you can omit it.

Here is an example:

When this  code executes, a table named Members would be opened in Design View.

 

Closing a Table

After using a table, you can close it. If there is a structural change that needs to be saved, Microsoft Access would prompt you.

To close a table, you can click its system Close button Close or Close (Windows XP). You can also double-click its System button on the left side of its title bar. You can also press Ctrl + F4.

To programmatically close a table, you can call the Close() method of the DoCmd object. Its syntax is:

DoCmd.Close acTable, [objectname], [save]

The first argument must be specified as acTable because you are trying to close a table. The second argument can be the name of the table you want to close. If you suspect that there might be a need to save the structure of the table, you can pass the third argument with one of the following values:

View Name Result
acSaveNo The table doesn't need to be saved
acSavePrompt Prompt the user to save the changes
acSaveYes Save the table without having to prompt the user

 

 

Practical LearningPractical Learning: Closing a Table

  1. To close the Customers table, click its system Close button Close or Close (Windows XP)
  2. In the same way, close the database
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next