Referencing Multiple Databases

Article
10/23/2006

You can have many Visual FoxPro databases on your system to meet organizational needs in a multi-user environment. Multiple databases offer the following advantages:

Controlling user access to a subset of tables in the overall system.
Organizing the data to efficiently meet the information needs of various groups using the system.
Allowing exclusive use of a subset of tables for creating local and remote views at run time.

For example, you might have a sales database that maintains sales information used primarily by the sales force working with customers and another database that maintains inventory information used primarily by the buyers working with suppliers. At times the information needs of these groups will overlap. These databases can be opened at the same time and accessed at will but they contain completely different types of information.

Multiple databases can add flexibility to your system.

You can use multiple databases either by opening more than one database simultaneously or by referencing tables in a closed database. Once multiple databases are open, you can set the current database and select tables in it.

Opening More Than One Database

When a database is open, the tables and relationships between tables are controlled by the information stored in the open database. You can have more than one database open at a time. For example, you might use multiple open databases when you run multiple applications, each based on a different database. You might also want to open multiple databases to use information, such as custom controls, stored in a database that is separate from your application's database.

To open more than one database

In the Project Manager, select a database and choose Modify or Open.

-or-
Use the OPEN DATABASE command.

Opening a new database does not close any databases you've opened previously. Other open databases remain open, and the newly opened database becomes the current database.

Setting the Current Database

When you open multiple databases, Visual FoxPro sets the most recently opened database as the current database. Any tables or other objects you create or add to the database become a part of the current database by default. Commands and functions that manipulate open databases, such as ADD TABLE and DBC( ), operate on the current database.

You can choose a different database as the current database through the interface or with the SET DATABASE command.

To set the current database

On the standard toolbar, select a database from the Databases box.

-or-
Use the SET DATABASE command.

For example, the following code opens three databases, sets the first database as the current database, then uses the DBC( ) function to display the name of the current database:

OPEN DATABASE testdata
OPEN DATABASE tastrade
OPEN DATABASE sample
SET DATABASE TO testdata
? DBC( )

Tip Visual FoxPro might open one or more databases automatically when you execute a query or a form that requires the databases to be open. To be sure you are operating on the correct database, set the current database explicitly before issuing any commands that operate on the current database.

Selecting Tables in the Current Database

You can choose from a list of tables in the current database with the USE command.

To choose a table from the current database

Issue the USE command with a "?" symbol.

The Use dialog box displays so that you can select a table to open.

For example, the following code opens the sales database and prompts you to select a table from the list of tables in the database.

OPEN DATABASE SALES
USE ?

If you want to select a table that is not associated with the open database, you can choose Other in the Use dialog box.

Closing a Database

You can close an open database from the Project Manager or with the CLOSE DATABASE command.

To close a database

From the Project Manager, select the database and choose Close.

-or-
Use the CLOSE DATABASE command.

For example, the following code closes the testdata database:

SET DATABASE TO testdata
CLOSE DATABASE

Both options close the database automatically. You can also close databases and all other open objects with the ALL clause of the CLOSE command.

Issuing the CLOSE DATABASE command from the Command window does not close a database if the database was opened by:

The Project Manager when you expanded the outline to view the contents of a database.
A form that is running in its own data session.

In these circumstances, the database remains open until the Project Manager closes the database, or until the form using the database is closed.

Scope Resolution

Visual FoxPro uses the current database as the primary scope for named objects, such as tables. When a database is open, Visual FoxPro first searches within the open database for any objects you request, such as tables, views, connections, and so on. If the object is not in the database, Visual FoxPro looks in the default search path.

For example, if the customer table is associated with the sales database, Visual FoxPro would always find the customer table in the database when you issue the following commands:

OPEN DATABASE SALES
ADD TABLE F:\SOURCE\CUSTOMER.DBF
USE CUSTOMER

If you issue the following command, Visual FoxPro will first look in the current database for the products table.

USE PRODUCTS

If products is not in the current database, Visual FoxPro will look outside the database, using the default search path.

Note You can specify the full path for a table if you want to be able to access it inside or outside a database — for example, if you anticipate a change in the location of a table. However, you increase performance when you reference only the table name, because Visual FoxPro accesses database table names more quickly than names specified with a full path.

Share via