BUG: E_Fail Error Message Occurs with Access ODBC Driver and LEFT OUTER JOIN Queries

Article translations Article translations
Article ID: 249638 - View products that this article applies to.
This article was previously published under Q249638
Expand all | Collapse all

On This Page

SYMPTOMS

If you submit a LEFT OUTER JOIN query to the Microsoft Access ODBC driver and a Boolean ("Yes/No") column is one of the columns in the fields list, the following error message occurs when you reference the resulting Microsoft ActiveX Data Objects (ADO) recordset:
Run-time error '-2147467259 (80004005)': Data provider or other service returned an E_FAIL status.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was corrected in MDAC version 2.6.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Visual Basic 6.0 and choose a Standard EXE project.
  2. Add the following Project references:
    • Microsoft ActiveX Data Objects 2.5 Library

      -and-

    • Microsoft ADO Ext. 2.5 for DDL and Security


  3. Paste the following code into the Load event of the default form:
        Dim strSQL As String
        Dim cat As New ADOX.Catalog
        Dim tbl As New ADOX.Table
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        
        On Error Resume Next
        Kill "x.mdb"
        On Error GoTo 0
        
        cat.Create "Provider=Microsoft.jet.oledb.4.0;data source=x.mdb;"
        
        tbl.Columns.Append "cChar", adVarWChar, 5
        tbl.Columns.Append "cBool", adBoolean
        tbl.Name = "tOne"
        tbl.Keys.Append "PK1", adKeyPrimary, "cChar"
        cat.Tables.Append tbl
        Set tbl = Nothing
        
        tbl.Columns.Append "cChar2", adVarWChar, 5
        tbl.Columns.Append "cChar3", adVarWChar, 5
        tbl.Name = "tTwo"
        tbl.Keys.Append "PK2", adKeyPrimary, "cChar2"
        cat.Tables.Append tbl
        Set tbl = Nothing
        
        Set cat = Nothing
        
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        cn.CursorLocation = adUseClient
    
        cn.Open "Provider=MSDASQL.1;Driver={Microsoft Access Driver     (*.mdb)};DBQ=x.mdb;" 'Errors
        'cn.Open "provider=Microsoft.jet.oledb.4.0;data source=x.mdb" 'Works
        
        cn.Execute "INSERT INTO tOne VALUES ('aaaaa',0)"
        cn.Execute "INSERT INTO tOne VALUES ('bbbbb',1)"
        cn.Execute "INSERT INTO tOne VALUES ('ccccc',0)"
        cn.Execute "INSERT INTO tTwo VALUES ('aaaaa',1)"
        cn.Execute "INSERT INTO tTwo VALUES ('ddddd',1)"
        cn.Execute "INSERT INTO tTwo VALUES ('eeeee',1)"
        
        strSQL = "SELECT cChar2, cChar3, cChar, cBool " & _
                 "FROM tOne LEFT OUTER JOIN tTwo ON tOne.cChar = tTwo.cChar2"
    
        rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
        MsgBox rs.RecordCount 'Errors here!
    					
  4. Press F5 to run the project.RESULT: The error message described in the "Symptoms" section occurs.

Properties

Article ID: 249638 - Last Review: October 17, 2003 - Revision: 2.1
APPLIES TO
  • Microsoft Data Access Components 2.5
  • Microsoft Open Database Connectivity Driver for Access 4.0
Keywords: 
kbbug kbfix kbjet kbmdac260fix kbmdacnosweep KB249638

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com