How to: Create Remote Views
You can create remote views by specifying the name of a valid data source or a connection you have previously defined. For more information about defining connections, see How to: Define Connections to Remote Data Sources.
Note
If you create a remote view using only the data source name, Visual FoxPro uses the Open Database Connectivity (ODBC) information about the data source to create and activate a connection to the data source. When you close the view, the connection closes.
You can create remote views in the following ways:
Using the Remote View Wizard.
A wizard is the easiest way to start creating a view. You can select fields from tables and filter and sort the records in your remote view. For more information, see Creating Remote Views Using a Wizard.
Using the View Designer.
The View Designer makes it possible to select fields from tables, specify criteria for retrieving data and join conditions, and filter, sort, and group records to create views or customize existing views. For more information, see Creating Remote Views Using the View Designer.
Creating remote views programmatically.
The CREATE SQL VIEW command makes it possible for you to specify a remote data source or connection name so you can create remote views programmatically.
Regardless of the method you use, the basic process for creating a view is generally the same. For more information, see View Creation.
Note
When you create or use a view from the Project Manager, the Project Manager opens the database automatically. However, if you subsequently use a view outside the project, you must open the database or make sure the database is in scope before you can use the view.
Creating Remote Views Using a Wizard
You can create remote views easily using a wizard. The wizard helps you select connections or data sources and creates a query based on your answers to a series of questions.
To create a remote view with a wizard
Perform one of the following:
- Open the database in the Database Designer.
-OR-
- Open the project for your application in the Project Manager.
On the Tools menu, point to Wizards, and click All Wizards.
In the Wizard Selection dialog box, click Remote View Wizard, and then OK.
Follow the instructions on the wizard screens.
For more information, see Remote View Wizard.
Creating Remote Views Using the View Designer
You can use the View Designer to create views and specify additional options or customize existing views.
Note
For remote views, if you join two or more tables in the View Designer, the designer uses inner joins and places the join condition in the WHERE clause of the SQL statement generated by the view. If you want to use an outer join, the View Designer provides only left outer joins using the syntax supported by ODBC. If you need right or full outer joins or want to use native syntax for a left outer join, create the view programmatically.
To create a remote view
Perform one of the following:
- Open the database in the Database Designer.
-OR-
- Open the project for your application in the Project Manager.
For more information, see Query and View Designers and Select Connection or Data Source Dialog Box.
After selecting tables, you need to select the fields that contain the data you want to retrieve the data for your view. For more information, see How to: Select Fields for Views.
Creating Remote Views Programmatically
You can create remote views programmatically by opening the View Designer or specifying a SQL SELECT statement.
To create a remote view programmatically
Open the database or project to which you want to add the remote view.
Choose one of the following:
- To select a data source or connection name, choose tables, and open the View Designer, use the CREATE SQL VIEW with only the REMOTE clause.
-OR-
To create a remote view without opening the View Designer, use the CREATE SQL VIEW with a CONNECTION clause and the AS clause to specify the SQL SELECT statement you want to use.
Note
To match related records between the tables, you must specify a join condition in the FROM or WHERE clause of the SQL statement used in the CREATE SQL VIEW command. If persistent relationships between the tables exist, they are automatically used as join conditions.
The tables you select appear in the View Designer and Database Designer. You might need to close and reopen the designers to update them.
For more information, see CREATE SQL VIEW Command.
You can view available connections using the DISPLAY CONNECTIONS command. For more information, see DISPLAY CONNECTIONS Command.
For example, the following code opens a database named MyDatabase on a remote server and creates a remote view from a table named MyRemoteTable using a predefined connection named Remote_01:
OPEN DATABASE MyDatabase
CREATE SQL VIEW MyRemoteTable_Remote_View ;
CONNECTION Remote_01 AS SELECT * FROM MyRemoteTable
You can also specify a data source name instead of connection name.
See Also
Concepts
Accessing Remote Data Using Views
View Creation