PRB: Query Designer Creates Wrong SQL Statement on MDB Files (195245)

Microsoft Visual Basic Professional Edition for Windows 6.0

Microsoft Visual Basic Enterprise Edition for Windows 6.0

Microsoft Visual C++, 32-bit Editions 6.0

Microsoft Visual InterDev 6.0

Microsoft Visual Studio, Professional Edition 6.0

Microsoft Visual Studio, Enterprise Edition 6.0

This article was previously published under Q195245

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

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

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 }

RESULT: The SQL statement contains brackets and the ODBC OJ syntax.

Following is the correct SQL statement.

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

Modification Type:

Major

Last Reviewed:

12/3/2003

Keywords:

kbDatabase kbDataEnv kbpending kbprb KB195245