CREATE SQL VIEW Command
Opens the View Designer so you can create a SQL view.
CREATE [SQL] VIEW [ViewName] [REMOTE]
[CONNECTION ConnectionName [SHARE] | CONNECTION DataSourceName]
[AS SQLSELECTStatement]
Parameters
CREATE [SQL] VIEW
Creates a SQL view.[ViewName]
Specifies the name of the view to create.[REMOTE]
Specifies to create a view using tables from a remote data source, or a remote view. Omitting REMOTE creates a view using local tables.[CONNECTION ConnectionName[SHARE] | CONNECTION DataSourceName]
Specifies the name of a previously defined connection or an existing data source to connect with when opening the view.Note
When you use the CONNECTION clause and specify the name of a connection or data source, Visual FoxPro first searches the current database for a connection with the name you specified. If the specified connection cannot be found, Visual FoxPro searches for an established ODBC data source with the specified name. Therefore, if your current database contains a named connection with the same name as an ODBC data source on your system, Visual FoxPro uses the named connection.
Tip
If you use a CONNECTION clause with the CREATE SQL VIEW command, you do not need to include the REMOTE keyword. Visual FoxPro identifies the view as a remote view from the inclusion of the CONNECTION keyword.
The SHARE keyword specifies that Visual FoxPro use a new statement handle for the shared connection, if available. If a shared connection is not available, Visual FoxPro creates a shared connection when the view opens, which you might be able to share with other views.
[AS SQLSELECTStatement]
Specifies a SQL SELECT statement for the view. SQLSELECTStatement must be a valid SQL SELECT statement and should not be enclosed in quotation marks ("").Tip
For local views, preface the view or table name in the SQL SELECT statement with the name of the database and an exclamation point (!). By qualifying the table or view name with the database name and exclamation point, Visual FoxPro searches for the table in all open databases and in the default search path for the table. However, if you do not qualify a table with a database name in a view definition, the database must be open before you can use the view. For more information, see SELECT - SQL Command.
Remarks
A SQL view makes it possible for you to retrieve data from specific fields in one or more related tables as a table that you can update. For more information, see Working with Views (Visual FoxPro).
Example
The following example closes all open databases and opens the Northwind sample database. CREATE SQL VIEW creates a local SQL view named MyView using a SQL SELECT statement that selects all records from the Customers table where the Country field contains "Mexico". MODIFY VIEW displays the view in the View Designer so you can edit the view. After you close the View Designer, DELETE VIEW deletes the view.
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Northwind\Northwind')
CREATE SQL VIEW MyView AS SELECT * FROM Northwind!Customers;
WHERE Country="Mexico"
MODIFY VIEW MyView
DELETE VIEW MyView
For more information, see CLOSE Commands, OPEN DATABASE Command, MODIFY VIEW Command, and DELETE VIEW Command.