USE Command

  • Article

Opens a table and its associated index files or opens a SQL view.

USE [[DatabaseName!] TableName | SQLViewName | ?]
   [IN nWorkArea | cTableAlias] [ONLINE] [ADMIN] [AGAIN]
   [NOREQUERY [nDataSessionNumber]] [NODATA] 
   [INDEX IndexFileList | ? [ORDER [nIndexNumber | IDXFileName 
   | [TAG] TagName [OF CDXFileName] [ASCENDING | DESCENDING]]]]
   [ALIAS cTableAlias] [EXCLUSIVE] [SHARED] [NOUPDATE] 
   [CONNSTRING cConnectionString | nStatementHandle ]

Parameters

  • [DatabaseName!]TableName
    Specifies the name of the table to open. To open a table not in the current database, precede the table name with the database name followed immediately by an exclamation point (!).

    Tip

    On Microsoft Windows 98 and later, spaces are significant in file names; therefore, avoid using extraneous spaces in TableName and DatabaseName. If a table or database name contains spaces, enclose the name in quotation marks (" " or ' ').

    Tip

    To access a table associated with or not associated with a database, specify the full path. However, you can increase performance by using only the table name.

    Note

    If you do not include a database name, Visual FoxPro can open tables only in the current database. Visual FoxPro does not recognize path names correctly if a disk or directory name contains an exclamation point.

  • SQLViewName
    Specifies the name of a SQL view in the current database to open. SQLViewName can also be the name of an offline view created with the CREATEOFFLINE( ) function. For more information, see CREATEOFFLINE( ) Function.

    You can create a SQL view using CREATE SQL VIEW Command.

  • ?
    Displays the Use dialog box so you can choose a table.

  • IN nWorkArea
    Specifies the work area in which to open the table. To close a table in a specific work area, call USE without the table name but include the IN clause and the work area number.

    The IN clause supports 0 as a work area. Including 0 opens a table in the lowest available work area. For example, if tables are open in work areas 1 through 10, the following command opens the customer table in work area 11:

    USE customer IN 0

  • IN cTableAlias
    Specifies the alias of the table to open in the work area of a table that is currently open. The alias of the open table is specified with cTableAlias.

    If you omit nWorkArea and cTableAlias, the table is opened in the currently selected work area.

  • ONLINE
    Opens an offline view created with CREATEOFFLINE( ). You can specify the name of the offline view in SQLViewName. You can use TABLEUPDATE( ) to update data on the server.

    The offline view must be opened exclusively. Before opening the offline view with USE, include the EXCLUSIVE clause in USE or SET EXCLUSIVE to ON.

    You cannot open or close an offline view in ONLINE mode from within a transaction.

  • ADMIN
    Opens an offline view created with CREATEOFFLINE( ) but does not update the data on the server with the changes made to the offline view. Opening an offline view with the ADMIN keyword allows you to make changes to the offline view without updating the data on the server.

    You cannot open or close an offline view in ADMIN mode from within a transaction.

  • AGAIN
    To open a table concurrently in multiple work areas, you can do one of the following:

    • Select another work area and issue USE with the table name and the AGAIN clause.

    • Issue USE with the table name and the AGAIN clause, and specify a different work area with the IN clause.

    When you open a table again in another work area, the table in the new work area takes on the attributes of the table in the original work area. For example, if a table is opened for read-only or exclusive access and is opened again in another work area, the table is opened for read-only or exclusive access in the new work area.

    Index files opened for the original table are available for the table you open again if you don't open indexes when you open the table again. The index order is set to 0 in the work areas where the table is opened again.

    You can open indexes that were not opened with the original table. This sets the index order to 0 for the original table.

    A table opened again is assigned the default alias of the work area. You can include an alias every time you open a table in multiple work areas as long as the aliases are unique.

    In Visual FoxPro for Windows, opening a table again in another work area doesn't consume an additional file handle.

  • NOREQUERY [nDataSessionNumber]
    Specifies that data from a remote SQL view is not downloaded again. NOREQUERY is available only for SQL views and is typically used when you open a SQL view again by including the AGAIN clause. Including the NOREQUERY clause increases performance for large data sets because the data does not need to be downloaded again.

    nDataSessionNumber can be included to specify that data for a remote SQL view in a specific data session isn't downloaded again. If nDataSessionNumber is omitted, data isn't downloaded for the view open in the current data session.

  • NODATA
    Download the structure of a SQL view without data. NODATA provides the fastest method for determining the structure of a SQL view.

    Note

    Opening a view with NODATA always opens a new cursor for the view. When you use NODATA, Visual FoxPro creates a WHERE clause for the view that always returns a value of false. Because no records on the data source meet the WHERE clause condition, no rows are selected for the remote data source cursor. Therefore, Visual FoxPro retrieves the structure of the view quickly because it does not wait for the remote data source to build a potentially large cursor.

    Tip

    Using NODATA is more efficient than setting the MaxRecords property to 0 on your view or cursor using DBSETPROP( ) or CURSORSETPROP( ) respectively. When you use the MaxRecords property, you must wait while the remote data source builds a cursor for the view containing all data rows that meet the view's normal WHERE clause conditions. Rows from the full remote view cursor are then downloaded according to the setting of the MaxRecords property. For more information, see DBSETPROP( ) Function and CURSORSETPROP( ) Function.

  • INDEX IndexFileList
    Specifies a set of indexes to open with the table. If a table has a structural compound index file, the index file is automatically opened with the table.

    IndexFileList can contain any combination of names of single-entry .idx and compound .cdx index files. You don't have to include the file name extensions for index files unless an .idx and a .cdx index file in the index file list have the same name.

    The first index file named in the index file list is the master controlling index file, which controls how records in the table are accessed and displayed. However, if the first index file is a .cdx compound index file, records in the table are displayed and accessed in physical record order.

  • INDEX ?
    Displays the Open dialog with a list of available index files to choose from.

  • ORDER [nIndexNumber]
    Specifies a master controlling single-entry .idx index file or compound .cdx index file tag other than the first index file or index tag specified in IndexFileList.

    .Idx index files are numbered first, in the order in which they appear in the index file list. Tags in the structural compound index file (if one exists) are then numbered in the order in which the tags were created. Finally, tags in any independent compound index files are numbered in the order in which they were created. You can also use SET ORDER to specify the controlling index file or tag. See SET ORDER Command for a further discussion of the numbering of index files and tags.

    If nIndexNumber is 0, records in the table are displayed and accessed in physical record order, and the indexes remain open. Including ORDER 0 enables open index files to be updated while presenting the file in record number order. Including ORDER without nIndexNumber is identical to including ORDER 0.

  • ORDER [IDXFileName]
    Specifies a single-entry .idx index file as the master controlling index file.

  • ORDER [TAG TagName] [OF CDXFileName]
    Specifies a master controlling tag in a compound .cdx index file. The tag name can be from the structural compound index file or any open compound index file. If identical tag names exist in open compound index files, include OF CDXFileName and specify the name of the compound index file containing the desired tag.

  • ASCENDING
    Specifies that the table records are accessed and displayed in ascending order.

  • DESCENDING
    Specifies that the table records are accessed and displayed in descending order.

    Including ASCENDING or DESCENDING doesn't change the index file or tag; it alters only the order in which records are displayed and accessed.

  • ALIAS cTableAlias
    Creates an alias for the table that you can use with other commands and functions. An alias must begin with a letter or an underscore and can consist of up to 254 letters, digits, or underscores.

    Note

    Visual FoxPro automatically creates an alias if the alias you provide contains an unsupported alias character.

    When a table is opened, it is automatically assigned an alias, which is the table name if ALIAS is omitted. However, you can create a different alias for the table by including ALIAS and a new alias. In earlier versions of FoxPro versions, an alias must begin with a letter or an underscore and can contain up to 10 letters, digits or underscores.

    Visual FoxPro assigns a default alias if a conflict between alias occurs or if you use the AGAIN keyword to open a table simultaneously in multiple work areas without specifying an alias when you open the table in each work area.

  • EXCLUSIVE
    Opens a table for exclusive use on a network. For more information on the exclusive use of tables, see SET EXCLUSIVE Command.

  • SHARED
    Opens a table for shared use on a network. SHARED allows you to open a table for shared use even when EXCLUSIVE is set ON.

  • NOUPDATE
    Prevents changes to the table and its structure.

  • CONNSTRING cConnectionString | nStatementHandle
    Specifies a connection string for the ODBC data source. The cConnectionString parameter can represent parameters normally passed using either SQLCONNECT( ) or SQLSTRINGCONNECT( ). The nStatementHandle parameter specifies a memory variable representing an existing statement handle from an existing connection.

    When you use cConnectionString, Visual FoxPro passes the connection string to the remote view that you specify with SQLViewName, which then uses the connection string to connect to the specified data source instead of using any previously defined connection string. Passing an empty string ("") displays the ODBC Data Source dialog box and makes it possible for the user to select a data source.

    Note

    If you specify incomplete data in the connection string, the data source displays a login dialog box for any needed login information. If you omit the optional CONNSTRING cConnectionString clause, the remote view uses the currently defined default connection. For more information about data source connection strings, see your ODBC driver documentation.

    Visual FoxPro supports overriding the default connection for a remote view and requires the nStatementHandle parameter for supporting shared connections.

Remarks

If you issue USE without a table name, and a table file is open in the currently selected work area, Visual FoxPro closes the table. Visual FoxPro also closes a table when another table is opened in the same work area. You cannot have more than one table open in a work area at one time.

When used with a noncurrent database, the USE command opens the database automatically but does not set the database as the current database. When the table closes, the database remains open.

Example

The following example opens three tables in three different work areas. The Data Session window is opened to show where the tables are open and to show the alias for each table.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
ACTIVATE WINDOW View
USE customer IN 0  && Opens Customer table
USE employee IN 0  && Opens Employee table
USE products IN 0  && Opens Products table

See Also

Tasks

How to: Use Tables from Other Databases

Other Resources

Managing Views

Commands (Visual FoxPro)

Language Reference (Visual FoxPro)