OPEN DATABASE, CLOSE DATABASES

Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.

`OPEN DATABASE`, `CLOSE DATABASES`

These commands open and CLOSE DATABASES, respectively. Opening a database does not open any of the tables in it, but closing a database does CLOSE ALL of its tables.

Usage

OPEN DATABASE [ Name | ? ]
        [ EXCLUSIVE | SHARED ]
        [ NOUPDATE ]
        [ VALIDATE ]

The EXCLUSIVE, SHARED, and NOUPDATE options behave the same way for the database as they do for an individual table opened with USE. However, the settings specified in OPEN DATABASE don’t affect opening of tables in the database. That is, if you open a database shared, but have SET EXCLUSIVE ON, a USE for a table in the database is still exclusive. For NOUPDATE especially, this has important implications. Opening a database NOUPDATE is not sufficient to prevent changes to the data in the database; it prevents changes only to the contents of the database container (which tables are included, persistent relations and stored procedures). Fortunately, it also prevents changes to the structure of the individual tables in the database. Unfortunately, working interactively, you find this out only when you go to save your changes or if you have the Database Designer open and notice the “Read Only” in its title bar.

Prior to VFP 7, the prevention of changes when the database is open NOUPDATE wasn't complete. If you attempted to add an index to a table, even though the INDEX command gave you an error message, enough information was stored that the database was considered invalid. This bug is fixed in VFP 7.

Also, if you open a database EXCLUSIVE or NOUPDATE, then reopen it without closing it first, it doesn't matter whether you specify the same keyword(s) again. The database retains the same characteristics. To change them, you have to close the database, and then open it with different keywords.

The VALIDATE keyword lets you check a database for internal consistency. There are various ways a database can get messed up. (Deleting one of its tables or indexes with Explorer comes to mind as an obvious choice.) Unfortunately, OPEN DATABASE VALIDATE doesn’t do anything about it if the database is invalid; it just reports an error and doesn’t open the database. Consider it as “Open the database only if it is valid.” You have to use VALIDATE DATABASE RECOVER to fix the problems. If you omit the VALIDATE keyword, you can open the database anyway and work with it for a while until the problem comes up to bite you. In fact, you have to open the database in order to apply VALIDATE DATABASE RECOVER.

In VFP 7, if the database has Database Events turned on, the OpenData and Activate events fire (in that order). If another database was the current one (see SET DATABASE for an explanation of “current database”), its Deactivate event fires before the newly opened database’s Activate event does.

Example

OPEN DATABASE TasTrade

You don’t actually have to use OPEN DATABASE, because opening a table in a database opens the database as well. (Watch out, though—although opening a table opens the database, it doesn’t make it the selected database.) The best reasons for explicitly opening the database are to add one of the optional keywords, to work on the structure of a database, or so that you don’t have to remember the short filenames for the tables and can use only the long names you’ve assigned.

Usage

CLOSE DATABASES [ ALL ]

By itself, CLOSE DATABASES closes the current database and any of its tables or views that may be open. If Database Events are turned on, the CloseData and Deactivate events fire, in that order. If no database is current, the command closes all open free tables (sort of like what CLOSE DATABASES does in older versions of FoxPro).

One warning: If something else (like the Project Manager) is keeping a database open, CLOSE DATABASES doesn’t close it, even if you include the ALL clause. It does, however, cause the database to no longer be the current one. Interestingly, with Database Events turned on, CloseData and Deactivate fire. We’ve suggested that Microsoft add a PRETTY PLEASE clause to this command that would always close the database, but so far, that request has been received in the spirit in which it was offered.

Adding the optional ALL keyword closes all open databases, their tables and views, plus all free tables. The venerable CLOSE ALL includes CLOSE DATABASES ALL among its functionality. As each database is closed, its CloseData database event fires. The current database is the last one closed, and after its CloseData event fires, its Deactivate event fires.