With this wizard, you can create views using remote data (ODBC). You must have an existing database to store your view in, as well as an existing data source or named connection stored in a database. If you do not have a database open, you are prompted to select an existing database or create one.
Before you run the Remote View wizard, you can set options for remote view and connection defaults on the Remote Data tab in the Options dialog box. You can create connections in the Connection designer.
From Tools menu, choose Wizards, and then click Query.
In the Wizard Selection dialog box, choose Remote View Wizard.
Step 1 - Choose Data Source
In this step, you can specify, from a list of available ODBC data sources or a list of named connections, which one contains the target data. If the box is empty, you must define your data sources and connections in Microsoft Windows before you run the wizard. You can define your data sources using the Data Sources control panel of the Administrative Tools control panel. If your remote data source requires a password, you will be prompted for it when you click Next.
You also can include System tables for ODBC drivers that support them.
After you select an ODBC data source, you might see the Configure Connection dialog box in which you specify whether to use a database or free table, and the location of the target table.
Step 2 вЂ“ Select Fields
In this step, you specify which fields you want to use in your remote view.
Use the Tables controls to locate and select the table you want to use.
In the Available fields box, select one or more fields you want to use from the selected table, and use the arrow buttons to move them to the Selected fields box.
You can use fields from other tables by repeating this process. You can select as many fields as you need and move them into the Selected fields box.
Step 3 вЂ“ Relate Tables
This step displays only if you select fields from more than one table. You can specify which fields in each table contain the same information and can govern the relationship between the tables, thereby determining the included records.
Select the desired fields from the two drop-down list boxes, and then choose Add. If you use multiple tables in your view, then you must relate the tables by indicating which fields contain matching data in each table.
If a relationship does not exist currently and the wizard could not suggest fields for a join, you must choose the fields. Typically, tables include fields for this purpose and often have the same name.
Step 4 - Include Records
This step displays only if you select fields from more than one table. You can specify which records from the selected tables will be made available to the Remote View wizard.
- Only matching rows
- Returns only records from both tables that match the comparison condition set between the two fields in the join condition. This is called an inner join.
- All rows from this table
- Returns all the rows from the selected table. This makes it possible for you to create a left or right outer join. For more information, see How to: Create a Multitable View.
Step 5 вЂ“ Sort Records
This step makes it possible for you to specify one or more fields by which to sort the records in your remote view.
Select up to three fields or indexes to determine the order in which your view results will be sorted. If your table already has one or more indexes, you can select the index tag, which is listed below the fields, separated by a line, in the Available fields window. Select Ascending to sort the view in ascending order, or Descending to sort the view in descending order.
Step 6 - Filter Records
In this step, you can specify a filter condition, so only certain of the records you have chosen to make available are used in the remote view.
Select a field from the Field drop-down list.
Specify an operator, such as equals or contains, in the Operator drop-down box.
Specify a value in the Value textbox.
For example, if you wanted to filter on a specific city, such as Helena, you might choose city from the Field drop-down list, choose equals in the Operator drop-down list, and enter Helena in the Value textbox.
You can reduce the number of records by creating expressions that filter records from the selected tables or views. You can create two expressions and connect them with And, which returns only records meeting both specified criteria, or Or, which returns records meeting either criteria.
You can see the result by clicking the Preview button.
Step 7 -Finish
In this step, you can choose how to handle the newly defined remote view.
- Save remote view
- Saves the newly defined remote view and exits the wizard.
- Save remote view and browse
- Saves the new remote view and then opens it in a Browse window, so you can review the displayed data.
- Save remote view and modify it in the View Designer
- Saves the new remote view and then modify it when it opens in the View designer.