Either EOF or BOF is true (recordset comes empty) - ADO connection Excel VBA / ACCESS

Question

So I have an access database with multiple tables where I store data on companies. I have designed several queries in Access to pull some information.

From Excel VBA, I connect to the Access database using ADO connection and pull the stored query. I have done so several times already and it works fine. But one of my query doesn't return any result: the recordset returns "either EOF or BOF is true".

When I open the query in ACCESS, ACCESS prompts me for the parameter "GICS_SUB_INDUSTRY_NAME", and it does returns a table with values to me if I input a parameter. So the query works fine in ACCESS but doesn't from Excel VBA.

Here is the SQL statement of the query:

PARAMETERS GICS_SUB_INDUSTRY_NAME Text ( 255 );
SELECT STOCK_STATIC.NAME, Focus_MAXHistoryDate.MaxOfHistory_Date, Focus_MAXHistoryDate.Isin, STOCK_DYNAMIC.CUR_MKT_CAP, STOCK_HIST_IS_BS_CF.BS_TOT_LIAB2, STOCK_HIST_IS_BS_CF.PREFERRED_EQUITY__MINORITY_INT, STOCK_HIST_IS_BS_CF.BS_CASH_NEAR_CASH_ITEM, STOCK_STATIC.GICS_SUB_INDUSTRY_NAME
FROM ((Focus_MAXHistoryDate INNER JOIN STOCK_HIST_IS_BS_CF ON (Focus_MAXHistoryDate.Isin = STOCK_HIST_IS_BS_CF.Isin) AND (Focus_MAXHistoryDate.MaxOfHistory_Date = STOCK_HIST_IS_BS_CF.History_Date)) INNER JOIN STOCK_STATIC ON Focus_MAXHistoryDate.Isin = STOCK_STATIC.ISIN) INNER JOIN STOCK_DYNAMIC ON Focus_MAXHistoryDate.Isin = STOCK_DYNAMIC.Isin
WHERE (((STOCK_STATIC.GICS_SUB_INDUSTRY_NAME)=[GICS_SUB_INDUSTRY_NAME]));

And this is my code in Excel VBA:

Sub Pull_Stock_Peers_Leverage()

'define general variables
Dim Connection As ADODB.Connection
Dim Command As ADODB.Command
Dim GICS_SUB_INDUSTRY_NAME As ADODB.Parameter
Dim RecordSetL As ADODB.RecordSet
Dim iField As Integer
Dim Workbook As Excel.Workbook
Set Workbook = Excel.ActiveWorkbook
Dim Start As Excel.Worksheet
Set Start = ActiveWorkbook.Sheets("Start")
Dim GICS4_Range As Excel.Range
Set GICS4_Range = Excel.Range("GICS4_LookUp")

'Open connection to the AMSIR database
Set Connection = New ADODB.Connection
With Connection
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open "N:\Switzerland\Others\_AMSIR_Company_Database\AMSIR_Company_Database.accdb"
End With

'Open command object and pass a parameter (criteria) to the query
'The parameter name should match the parameter clause in the SQL statement?
Set Command = New ADODB.Command
With Command
    .ActiveConnection = Connection
    .CommandText = "Pull_Peers_Leverage"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("GICS_SUB_INDUSTRY_NAME", adVarWChar, adParamInput, Len(GICS4_Range))
    .Parameters("GICS_SUB_INDUSTRY_NAME") = GICS4_Range
End With

'Create recordset by executing the command
Set RecordSetL = New ADODB.RecordSet
RecordSetL.CursorLocation = adUseClient
RecordSetL.CursorType = adOpenStatic
RecordSetL.Open Command

'Write fields from record set
Dim TargetRangeL As Excel.Range
Set TargetRangeL = Start.Range("PEERS_LEVERAGE")
Dim FieldCount As Integer
Dim i, j As Integer

FieldCount = RecordSetL.Fields.Count
i = 28
j = 37
For iField = 1 To FieldCount
    Start.Cells(i + iField, j).Value = RecordSetL.Fields(iField - 1).Name
Next

'Transpose recordset values and paste into array
Dim recArray As Variant
Dim recCount As Long

recArray = RecordSetL.GetRows
recCount = UBound(recArray, 2) + 1

TargetRangeP.Resize(FieldCount, recCount).Value = recArray

Connection.Close

End Sub

So I do get the recordset in the code and I can actually loop into the recordset and extract field names but the fields have no value and come as "Either BOF or EOF is true or the current record has been deleted".

I have this code for all my other queries but only for this query the recordset comes empty. And it's the only query of that type I have (that does match a query and tables with inner joints).

EDIT:

I was wondering if the issue was the ADO or the way I pass my parameter so I decided to just pass the SQL statement to the ACCESS without putting any parameter; so I should normally get a recordset with all available values.

I have tried:

Sub Test1()

Dim Connection As New ADODB.Connection
Dim Command As ADODB.Command
Dim RecordSetUF As New ADODB.RecordSet
Dim Workbook As Excel.Workbook
Set Workbook = Excel.ActiveWorkbook
Dim Start As Excel.Worksheet
Set Start = ActiveWorkbook.Sheets("Start")
Dim GICS4_Range As Excel.Range
Set GICS4_Range = Excel.Range("GICS4_LookUp")

'Open connection to the AMSIR database
Set Connection = New ADODB.Connection
With Connection
'    .Provider = "SQLOLEDB"
    .Provider = "Microsoft.ACE.OLEDB.12.0"
'    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source") = "N:\Switzerland\Others\_AMSIR_Company_Database\AMSIR_Company_Database.accdb"
    .Open
End With

'Place sql statement and match it to newly created recordset
Set Command = New ADODB.Command
Command.ActiveConnection = Connection
Set RecordSetUF = New ADODB.RecordSet
Dim sql1 As String
'ACCESS accepts * for empty values but Excel VBA needs % as wild card in an SQL queryIf CompF.Text = "" Then CompF.Text = ""

sql1 = "SELECT STOCK_STATIC.NAME, Focus_MAXHistoryDate.MaxOfHistory_Date, Focus_MAXHistoryDate.Isin, STOCK_DYNAMIC.CUR_MKT_CAP, STOCK_HIST_IS_BS_CF.BS_TOT_LIAB2, STOCK_HIST_IS_BS_CF.PREFERRED_EQUITY__MINORITY_INT, STOCK_HIST_IS_BS_CF.BS_CASH_NEAR_CASH_ITEM, STOCK_STATIC.GICS_SUB_INDUSTRY_NAME FROM ((Focus_MAXHistoryDate INNER JOIN STOCK_HIST_IS_BS_CF ON (Focus_MAXHistoryDate.MaxOfHistory_Date = STOCK_HIST_IS_BS_CF.History_Date) AND (Focus_MAXHistoryDate.Isin = STOCK_HIST_IS_BS_CF.Isin)) INNER JOIN STOCK_STATIC ON Focus_MAXHistoryDate.Isin = STOCK_STATIC.ISIN) INNER JOIN STOCK_DYNAMIC ON Focus_MAXHistoryDate.Isin = STOCK_DYNAMIC.Isin;"

Command.CommandText = sql1
'Debug.Print sql1
'Debug.Print returns an SQL statement that works fine in Access!
Command.Execute

RecordSetUF.Open Command

So basically I have an sql statement that works fine in ACCESS, but not in excel VBA.

I tend to use a commandtype of adCmdTable not adCmdStoredProce for this type of query in Access. You could try and see if that works. — Bob Phillips
Yes I have tried that but it doesn't work. I have now tried to write the SQL statement directly in Excel and I also get an empty recordset while doing so. I have also simplified the SQL statement to include NO parameter (so that the recordset would return everything in the database) but the recordset comes empty (EOF or BOF true). When I paste the SQL statement in the sql view of ACCESS and execute it, it works fine and I get all the information in a table format. — ZeLegend
I have often found problems with NULLs in the data, so I tended to put an IFNULL test in my query and return a blank string "" in that case. — Bob Phillips

Nigel Heffernan Nigel Heffernan · Accepted Answer · 2015-11-30T18:14:37

You were working in MS-Access, in a dialect of SQL called 'Jet-SQL'.

You are now working in Excel, with Jet-SQL, and all of the things that the MS-Access host application does for Jet are missing:

You don't have access to VBA native functions (just the Jet-SQL native functions, which resemble some of the VBA string and type-conversion functions)
You don't have access to VBA functions written by the Access developer and globally-declared inside the MS-Access database;
The subqueries declared in your FROM clause had better be tables, or queries with no VBA, too.
The asterisk wildcard won't be silently translated into '%' for you (which I think you've got)

These things fail silently, or with misleading error messages - "Either BOF or EOF is true or the current record has been deleted" - instead of the 'missing parameter' or 'incorrect column name' errors you would expect...

But the place I'd start looking for an empty recordset and no explanation is the FROM clause: either it's one of the obvious 'No MS-Access' issues, or it's something more subtle involving a failure to match on NULL or Empty String - a failure which may well be another issue that the MS-Access wrapper manages in the background.

A quick note on Null-handling: you don't have IFNULL or NZ, so testing IIF(tbl.[Field] IS NULL is your best bet.

Either EOF or BOF is true (recordset comes empty) - ADO connection Excel VBA / ACCESS

1 Answers