Excel/ADO: Sometimes no records found

[drewfator]

I can usually run the following code without issue, but it sometimes fails to find any records in the record set. The file itself is large - over 200k rows. In addition, it has some merged cells, and the column widths are not wide enough to view the data without auto-fitting (I am not sure if either of those could be a contributing factor). Also, the times no records have been found have mostly been on a machine running Excel 2010, whereas the successful instances have been on a machine running Excel 2013.

Here is my code:

<code>Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath
Dim sSQL As String
Dim fd As Office.FileDialog
Dim fr11 As String

</code>

<code>Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "FR11".

sSQL = "select F3,F6,F8,F9,F10,F18,F22,F23,F28 from [Natural Detail $] where F18 = '0000121046' or F25 = 'Natural GL Acct Nbr'"

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

</code>​

<code>.AllowMultiSelect = False
.Title = "Please select the file." box.
.Filters.Clear
.Filters.Add "Excel", "*.xlsx"
.Filters.Add "All Files", "*.*"

If .Show = True Then
</code>​

fr11 = .SelectedItems(1)​

<code> End If
</code>​

<code>
End With

DBPath = fr11

oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBPath & "';" & _
"Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1;MaxScanRows=0';"

oRS.Open sSQL, oConn

If Not (oRS.BOF And oRS.EOF) Then
</code>​

Worksheets("FR11").Range("A1").CopyFromRecordset oRS
​

Else​

MsgBox "No records found"
​

<code> End If

oRS.Close
oConn.Close
Set oConn = Nothing</code>
​

Is there anything you see that could be causing the above to sometimes fail, or that would improve the code in general? Any feedback is much appreciated.

 

[Mr_Olaf]

Maybe problem is in the source workbook if it has multiple empty first rows. Parameter "MaxScanRows" is ignored. It is set in TypeGuessRows Registry key value (8 rows by default): [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows]
See this link: What is Ace OLEDB data provider

If this is the problem, you can fill first row with dump data to prevent random behavior of ACE OLEDB provider.

 

[drewfator]

Maybe problem is in the source workbook if it has multiple empty first rows. Parameter "MaxScanRows" is ignored. It is set in TypeGuessRows Registry key value (8 rows by default): [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows]
See this link: What is Ace OLEDB data provider

If this is the problem, you can fill first row with dump data to prevent random behavior of ACE OLEDB provider.

Thanks for the link. What I ended up doing was referencing the fields by their actual names and specifying the range the data is on - now it works great! I suspect that the error was due to multiple empty rows as you said. Your help is much appreciated!