PRB: Query Designer Creates Wrong SQL Statement on MDB Files

• The Query Designer, included with:
  • Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0
  • Microsoft Visual C++, 32-bit Editions, version 6.0
  • Microsoft Visual InterDev, version 6.0
  • Microsoft Visual Studio, Professional Edition 6.0
  • Microsoft Visual Studio, Enterprise Edition 6.0

SYMPTOMS

A database project uses an .mdb file for a database. When you use the Query Designer to create an SQL statement, the SQL pane displays an SQL sentence with the ODBC OJ syntax.

CAUSE

The Query Designers assumes that the transport mechanism to the backend database is ODBC and creates an ODBC-compliant syntax.

RESOLUTION

To workaround this problem, remove the ODBC syntax from the SQL statement.

STATUS

This behavior is by design.

MORE INFORMATION

This section demonstrates how to reproduce the behavior. To do this, you need to know how to use the Query Designer. In this example, you will create a connection to the NorthWind Access database (NWIND.MDB).

Steps to Reproduce Behavior

1. Drag the Order Details and Products table into the Query Designer Window. The tables appear in the Diagram Pane with join lines.



2. Click ProductName in the Products table and Quantity in the Order Details table.



3. Right-click the join line and click Select all Rows from Order Details. The following SQL statement appears in the SQL pane:

   
         SELECT
         FROM { oj `Order Details` LEFT OUTER JOIN
         Products ON `Order Details`.ProductID = Products.ProductID } 

      SELECT
      FROM `Order Details` LEFT OUTER JOIN
      Products ON `Order Details`.ProductID = Products.ProductID 

Additional query words:

Keywords : kbDatabase kbVBp600 kbVC600 kbVisID600 kbVS600 kbDataEnv
Version :
Platform :
Issue type : kbprb