title: Either EOF or BOF is true (recordset comes empty) - ADO connection Excel VBA / ACCESS
body: <p>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.</p>
  
  <p>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".</p>
  
  <p>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.</p>
  
  <p>Here is the SQL statement of the query:</p>
  
  <pre><code>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]));
  </code></pre>
  
  <p>And this is my code in Excel VBA:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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".</p>
  
  <p>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).</p>
  
  <p>EDIT:</p>
  
  <p>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.</p>
  
  <p>I have tried:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So basically I have an sql statement that works fine in ACCESS, but not in excel VBA. </p>