Microsoft KB Archive/238399

Knowledge Base

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.

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.

REFERENCES

For additional information on English Query, take a look at English Query Books Online.

For additional information on SQL 7.0 distributed and heterogeneous queries, take a look at SQL 7.0 Books Online.

Keywords: kbhowto kbdatabase KB238399

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.