Microsoft KB Archive/238399

= How To Allow an English Query to Interact with Non-SQL7 Data =

Article ID: 238399

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft English Query 7.0

-



This article was previously published under Q238399



SUMMARY
This article describes how to use a Microsoft SQL Server 7.0 heterogeneous query to allow an English Query (EQ) to generate queries for other data sources besides Microsoft SQL 6.5 and Microsoft SQL Server 7.0. By following these steps, you can use EQ with Oracle, Microsoft Access, and any other database with an appropriate level of OLE DB driver available.



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.

  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.

 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. 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. 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."

 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. 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</ol>

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

<div class="references_section">