Access Stored Procedure Automation Error

[duftnich03]

Hi All

I have a stored query procedure that executes fine in MS Access, but when I use the ADO library to execute said stored query procedure, and return the results to Excel, I receive an 'Automation error unspecified error -2147467259 (800004005)'.

It appears to be when it tries to open the recordset (rst. open Source:=...etc), I've included both the VBA that calls the open procedure and the open recordset procedure code itself.

Any help would be greatly appreciated.

Cheers.

Code:

Sub retirve_query(query_name As String)

    Set process_simba_ws = Sheet6
    Set checks_data_ws = Sheet5
    header_rw = 1
    fst_col = 1
    Dim header_rng As Range

    On Error GoTo ERROR_RETRIEVEQUERY
    Application.Interactive = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Clear previous data
    checks_data_ws.Rows.EntireRow.Delete

    create_db_connection
    open_record_set query_name
    
    ' Populate column headers
    For Each fld In rst.Fields
        checks_data_ws.Cells(header_rw, fst_col).Offset(0, i) = fld.Name
        i = i + 1
    Next fld
    
    ' Populate query data
    checks_data_ws.Cells(header_rw + 1, fst_col).CopyFromRecordset rst
    
    ' Format columns
    With checks_data_ws
        .Columns.EntireColumn.AutoFit
        .Rows(1).Font.Bold = True
        .Activate
    End With
    
    ' Update header's formatting
    With checks_data_ws
        lst_col = .Cells(header_rw, .Columns.Count).End(xlToLeft).Column
        Set header_rng = .Range(.Cells(header_rw, fst_col), .Cells(header_rw, lst_col))
        header_rng.Font.Bold = True
        header_rng.Interior.Color = 13553360
        .Cells(header_rw, fst_col).Activate
    End With

    close_rst
    close_cnn

EXIT_RETRIEVEQUERY:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.Interactive = True
On Error GoTo 0
Exit Sub

ERROR_RETRIEVEQUERY:
MsgBox Err.Description
GoTo EXIT_RETRIEVEQUERY
End Sub

Open recordset code:

Function open_record_set(query_name As String)

    ' Create a new recordset
    Set rst = CreateObject("ADODB.Recordset")
    ' Define recordset's attributes
    rst.CursorLocation = 3 'adUseClient
    rst.Open Source:=query_name, _
             ActiveConnection:=cnn, _
             CursorType:=2, _
             LockType:=3, _
             Options:=4 'adCmdStoredProc
             
    Set open_record_set = rst

End Function

[Leith Ross]

Hello duftnich03,

Unlike Access, Excel is not a RDB (Relational Database). Reading records in Excel can only be done sequentially. Your record set CursorType can only be 0 (adOpenForwardOnly) or 3 (adOpenStatic). Records in Excel are read only so the LockType must be 1 (adLockReadOnly). I have never used a stored procedure command in excel before. You may need to assign this to a string variable in your code and set the Options to 1 (adCmdText) instead of 4 (adCmdStoredProc).

Function open_record_set(query_name As String)

    ' Create a new recordset
    Set rst = CreateObject("ADODB.Recordset")
    ' Define recordset's attributes
    rst.CursorLocation = 3 'adUseClient
    rst.Open Source:=query_name, _
             ActiveConnection:=cnn, _
             CursorType:=0, _
             LockType:=1, _
             Options:=4 'adCmdStoredProc
             
    Set open_record_set = rst

End Function

[duftnich03]

Thanks for the info, but I use this approach in all of my Excel documents that refer to Access DBs and I have never had this issue before. As far as I understand it that LockType specifies what the Locktype in the DB is, not excel, as the code is passed to the Access DB engine.
I have other queries that are run in the same way, in the same document, that use the same 'open_record_set' procedure as above with great success. I also have some that write back in other multi user environments and they don't work unless adLockOptimistic (3) is selected.

I tried changing it to 1 at any rate, just confirm or deny my theory, and I does not work sadly.

[duftnich03]

Right, I've solved it!

The issue was in my SQL String:

SELECT A2.*
FROM (SELECT A.[Item IDSizeColourFitWarehouseStock Status], A.[Item ID], A.Size, Switch((A.Size='8' Or A.Size='6') AND Left(A.Size,1)<>'0', 'Missing leading 0', true, null) AS [Leading 0 Issue] FROM SiMBA_Plan_Data AS A)  AS A2
WHERE A2.[Leading 0 Issue] <> null
ORDER BY A2.Size;

The above runs fine in Access, but it doesn't when using the ADO libraries in VBA Excel.
However the below does:

SELECT A2.*
FROM (SELECT A.[Item IDSizeColourFitWarehouseStock Status], A.[Item ID], A.[Size], Switch((A.[Size]='8' Or A.[Size]='6') AND Left(A.[Size],1)<>'0', 'Missing leading 0', true, null) AS [Leading 0 Issue] FROM SiMBA_Plan_Data AS A)  AS A2
WHERE A2.[Leading 0 Issue] <> null
ORDER BY A2.[Size];

As you can see the only difference is I've put [] square brackets around my 'Size' field. So this would suggest that 'Size' is a reserved word in the ADO libraries and must be wrapped in [] square brackets when refering to a field name, yet it is not a reserved word in Access and therefore doesn't require the square brackets. This is why it worked in Access, but not in Excel.

Thanks for the help