How To Allow an English Query to Interact with Non-SQL7 Data (238399)

MORE INFORMATION

Microsoft English Query 7.0 generates SQL commands for SQL Server only. It does not generate SQL statements for other data sources. However, you can create SQL Server 7.0 views to encapsulate heterogeneous queries for other data sources. Those views can be imported into an existing English Query project by File/ Import new Tables menu item.

The following steps give an example of how you can insert a view into a English Query project.

1. Use the following code to create a view in SQL Server 7.0 NorthWind database to create a distributed query to Jet 4.0 data source, run the code from Query Analyzer:

      Use NorthWind
      go
      Create view NWindView as
      Select P1.ProductID as ID , P1.ProductName as Name From          NorthWind..Products P1, 
      OpenRowset    ('Microsoft.Jet.OLEDB.4.0','e:\VS98\VB98\nwind.mdb';'admin';'', 
    'select ProductID, ProductName from products where UnitsInStock > 100') P2
      Where P1.ProductID = P2.ProductID
      go
   					

   NOTE: You will need to change the path to the .mdb file to the appropriate path on your computer.
   
   
2. Create a new EQ project and import the Product table from the Northwinds database. At this point you can only import tables and not views.
3. Once the project is created, use File/ Import new Tables menu item to import the view into the EQ project. The view will be shown in the list of tables.
4. When you click OK to import the view you will receive the following warning messages in the authoring tool:
   "Warning: no foreign keys were specified in your database. You will need to add all necessary joins manually before creating relationships."
   "Warning: The table dbo.NWindView does not have keys specified in the database. You will need to add key information in English Query before you can load your application."
5. Next you must fix the warnings by right-clicking on the view name and select "Edit". In the dialog box, click on the Specify Key button and select the ID field as the key.
6. Then Right-click on the view name again and select "Insert Join". Specify a join between NWindView view and the Products table where NWindView.ID = Products.ProductId

The view can now be used to define new entities and relationships in the authoring tool.