BUG: Acc2000: "Not a Valid Alias" Jet 3068 error message...

• Microsoft Data Access Components version 2.1 SP2
• Microsoft ODBC Driver for Access, version 4.0
• Microsoft OLE DB Provider for Jet, version 4.0
• Microsoft Access 2000

SYMPTOMS

When executing a SQL query containing more than 22 UNION statements using Microsoft Jet 4.0 the following error appears:

Not a valid alias name.

CAUSE

This problem is due to a bug in the underlying Microsoft Jet 4.0 database engine that is included with the Microsoft Data Access Components (MDAC) versions noted in the earlier. As a result of this bug Microsoft Jet 4.0 is unable to execute queries containing more than 22 UNION statements.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next service pack for Microsoft Data Access Components that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

1. Install MDAC 2.1 SP2 on a test computer.



2. Create an ODBC DSN to the NorthWind.mdb sample database called NorthWind.



3. Run the following VBA code. NOTE: The VBA project requires a reference to the "Microsoft ActiveX Data Objects 2.1 Library.":




Sub TestUNIONError()
Dim conn As New ADODB.Connection
Dim sql As String
Dim i As Long

    conn.Open "NorthWind"
    For i = 1 To 22
        sql = sql & "select * from shippers UNION "
    Next i
    sql = sql & "select * from shippers"
    conn.Execute sql
    conn.Close

End Sub 

4. Run the application. It results in the following error message:

   Run-time error '-2147467259 (80004005)'.
   [Microsoft][ODBC Microsoft Access Driver] Not a valid alias name.

Additional query words:

Keywords : kbDatabase kbJET kbODBC kbOLEDB kbGrpVCDB kbGrpMDAC kbDSupport kbMDAC210SP2bug
Version : WINDOWS:2.1 SP2,2000,4.0
Platform : WINDOWS
Issue type : kbbug