Use

This command opens and closes tables and views. It has myriad clauses that let you vary the way it works.

Usage

USE [ [ Database! ] TableName | [ Database! ] ViewName | ? ]
      [ IN nWorkArea | cAlias ]
      [ AGAIN ]
      [ ONLINE ]
      [ ADMIN ]
      [ ALIAS AliasName ]
      [ NOREQUERY [ nDataSession ] ]
      [ NODATA ]
      [ INDEX IndexFileList | ? ]
      [ ORDER [ nIndex | IDXFileName
       | [ TAG ] TagName [ OF CDXFileName ]
         [ ASCENDING | DESCENDING] ]
      [ EXCLUSIVE | SHARED ]
      [ NOUPDATE ]
      [ CONNSTRING cConnectionString ]

One of the cool things in Visual FoxPro is the ability to treat query results just like a table by defining a view. This means that, instead of running a query to get the results, you simply open the view with USE. Looking at the code, you usually can’t even tell whether it’s a table or a view.

USE actually both opens and closes tables and views. If a table or view name or ? is specified, USE opens that table. (We’ll use the word “table” in this discussion to mean “table or view,” unless we tell you.) If all of those are omitted, USE closes the table. In fact, if there’s a table already open in the work area you specify (or the current work area, if you don’t specify), USE closes it before opening the table you name.

When you USE a local view, it also opens (and leaves open) the tables the view is based on.

USE closes any open table in the specified work area before it checks whether it can open the new one. If the open fails, the work area is left with no table open.

If you don’t specify otherwise, USE operates on the current work area. As with other Xbase commands, you can specify a work area by providing its number or the alias of the table currently open there. But USE offers one more option—if you say IN 0, it finds the lowest unused work area and opens the table there. Watch out when you use this option—like the IN clause of other commands, USE doesn’t change the current work area. The table gets opened, but you’re not sitting on it until you SELECT the new work area. The IN 0 option is most useful when you want to open a series of tables.

You can close a table without SELECTing its work area first. Just USE IN cAlias to close the table open as cAlias.

You don't even have to know that a table is open to close it in whatever work area it's open. USE IN SELECT("cAlias") will close the table because SELECT() returns the work area the alias is open in, if it's open, or zero if the table isn't open. USE IN 0 has no effect. MVP Cindy Winegarden showed us this one.

Any table can be opened many times, using only a single file handle. That’s what AGAIN is all about. One warning—if you USE a table twice and SET ORDER to the same thing in both work areas, both work areas have to be either ascending or descending. You can’t have one ascending and the other descending using the same index. This is true even if the two uses of the table are in different data sessions!

NOREQUERY and NODATA are both supposed to speed up opening of views. Neither behaves quite as documented. NODATA tells FoxPro to create a cursor for the view, but don’t populate it yet. The idea is that getting just the structure should be faster than actually moving the data. In fact, that’s true only when dealing with a smart server. Some servers will fulfill this request by retrieving all records, then throwing them out.

NOREQUERY, on the other hand, is utterly useless by itself. It’s supposed to prevent data from being loaded a second time when you open a view AGAIN. Great idea, but as far as we can tell, that’s what happens automatically when you USE a view AGAIN. You don’t need NOREQUERY for that.

With the optional nDataSession parameter, NOREQUERY is pretty cool. If you have the same view open in several data sessions, you can open it again and specify which data session’s version of the view should be “borrowed” to populate the newly opened view. With a parameterized view, each data session might contain a different set of records, so you can decide which set you want. The newly opened view is populated with the last committed data from the specified view—what we’d see with OLDVAL(), not the current data in that view. If the user has changed some data but the changes haven’t been saved to the original data source, the new view doesn’t see those changes (which is as it should be).

There are two bugs related to the NODATA clause. The first is that if a view parameter is listed in the AND clause of the JOIN part of a view's SQL SELECT statement, VFP displays the parameter dialog even when the view is opened NODATA. Here's an example of such a view: CREATE SQL VIEW TestView AS ; SELECT * FROM Orders ; JOIN OrdItems ; ON Orders.Order_ID = OrdItems.order_ID and ; Orders.Cust_ID = ?vp_CustID The second bug is similar: The parameter dialog displays when a view that SELECTs from a parameterized view is opened NODATA. In that case, the parameter dialog is for the underlying parameterized view. Although Microsoft claims this behavior is "by design," in our opinion it's a bug; the NODATA clause should be respected in any views the USE command has to open.

The ONLINE and ADMIN options are for offline views. These views let you detach data from the database and take it on the road without having to have all the data along. You take a view offline with the CREATEOFFLINE() command. Once you’ve done so, the view can be treated like a table, opened with USE, and so forth. These options are for using the offline view in the presence of the original database. ONLINE says to open the view and prepare to reattach it to the actual data. Once you USE a view ONLINE, you need to either commit or revert the changes in the data. ADMIN lets you open the offline view without having to update the actual data—you can, for example, simply examine the changed data to see what you want to do with it.

The INDEX and ORDER clauses are really a shortcut for the SET INDEX and SET ORDER commands. They do the same thing as those commands right in the USE.

EXCLUSIVE and SHARED let you override the SET EXCLUSIVE setting when necessary. NOUPDATE lets you open a table in “look, but don’t touch” mode—a feature we use all the time when peeking at the tables underlying the Form, Class, Project and Menu power tools.

One downside of opening remote data with remote views rather than the SQL pass-through functions is that the connection information is hard-coded, either in a connection stored in the database or in an ODBC data source. This means this information can’t be changed, so you can’t use the same remote view to access data on a different server, for example. Also, user names and passwords stored in the DBC aren’t encrypted, so that’s a potential security risk. To avoid these problems, starting in VFP 7, you can use the CONNSTRING clause when you open the remote view. If this clause is specified, the connection information specified (or selected in the SQL Data Source dialog if an empty string is passed) is used to open the remote view rather than the connection information specified when the view was defined. This gives you both flexibility (you can store the server, user name and password somewhere, such as in a configuration table) and security (the password can be encrypted in the table and decrypted when the connection string is built). One issue to be aware of when using CONNSTRING is that if another remote view is already open with the same connection as the one being opened, the connection isn’t shared, even if both views are configured to share connections.

In the initial release of VFP 7, the ALIAS clause was ignored if you used the CONNSTRING clause. This bug was fixed in Service Pack 1.

In VFP 7, opening a view or a table that belongs to a database fires the BeforeOpenTable and AfterOpenTable events if Database Events are turned on (notice there’s no BeforeOpenView or AfterOpenView). Also, the BeforeOpenTable and AfterOpenTable events for any tables the view is based on fire, even if the tables are already open. This likely shows what VFP is doing internally (opening additional copies of the tables in internal data sessions) when it opens a view.

Example

* Open a table in an unused work area in a specified order.
USE Employee IN 0 ORDER Last_Name

* Open a view.
OPEN DATA TasTrade
* Quotes are needed below because of embedded blank, so we'll
* assign an alias.
USE "Product Listing" ALIAS Prods

* Create a connection that points to the Northwind Access
* database, and create a view on the Orders table.
CREATE DATABASE TestConn
CREATE CONNECTION NorthWindAccess ;
  CONNSTRING 'driver=Microsoft Access Driver (*.mdb);' + ;
  'dbq=c:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb'
CREATE SQL VIEW Orders CONNECTION NorthWindAccess ;
  AS SELECT * FROM ORDERS
USE Orders
BROWSE
USE
* Now open the Orders view but from the SQL Server version of
* the Northwind database.
USE Orders CONNSTRING 'driver=SQL Server;server=(local);' + ;
  'database=northwind;uid=sa;'
BROWSE

See Also

AfterOpenTable, BeforeOpenTable, Candidate(), CreateOffline(), Create SQL View, NoDataOnLoad, OldVal(), Select, Select(), Set Exclusive, Set Index, Set Order, SQLConnect(), Used()