Using 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.

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:

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 or . 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:

Practical Learning: Closing a Table