What are the limits of ADODB in Excel?

I have an Excel worksheet with 107,141 records.
I'm using the basic code from this page.

Public Function RecordSetFromSheet(sheetName As String)

Dim rst As New ADODB.Recordset
Dim cnx As New ADODB.Connection
Dim cmd As New ADODB.Command

    'setup the connection
    '[HDR=Yes] means the Field names are in the first row
    With cnx
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source='" & ThisWorkbook.FullName & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
        .Open
    End With

    str_SQL = "SELECT * From [" & sheetName & "$]" & _
        "WHERE [Profession] = 'Veterinary Medicine' " & _
        "AND [Type] = 'Veterinarian' Or [Type] = 'Veterinarian Cln Ac Ltd' Or [Type] = 'Veterinarian Ed Ltd' Or [Type] = 'Veterinarian Nonaprv Prgm Ltd' " & _
        "AND [County] = 'Livingston' Or [County] = 'Macomb' Or [County] = 'Monroe' Or [County] = 'Oakland' Or [County] = 'Washtenaw' Or [County] = 'Wayne'"

    'setup the command
    Set cmd.ActiveConnection = cnx
    cmd.CommandType = adCmdText
'    cmd.CommandText = "SELECT * FROM [" & sheetName & "$]"
    cmd.CommandText = str_SQL
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic

    'open the connection
    rst.Open cmd

    'disconnect the recordset
    Set rst.ActiveConnection = Nothing

    'cleanup
    If CBool(cmd.State And adStateOpen) = True Then
        Set cmd = Nothing
    End If

    If CBool(cnx.State And adStateOpen) = True Then cnx.Close
    Set cnx = Nothing

    '"return" the recordset object
    Set RecordSetFromSheet = rst

End Function

Public Sub Test()

Dim rstData As ADODB.Recordset
Set rstData = RecordSetFromSheet("Sheet2")

rstData.MoveFirst
rstData.MoveLast
MsgBox rstData.RecordCount

rstData.Filter = "County = 'Macomb'"

rstData.MoveFirst
rstData.MoveLast
MsgBox rstData.RecordCount

rstData.Close
Set rstData = Nothing

End Sub

I have no errors creating the objects and the recordset.

The records I need (at least initially) are all towards the bottom on the data.

When I use the SQL of: str_SQL = "SELECT * From [" & sheetName & "$]"
I only get the upper 41,604 records.

Are there some built-in limitations with ADODB?
Is this a System Resource limitation?
If so, without adding more memory, are there any settings I can apply to get all (or at least more) records?

To perform more testing, I copied the bottom 14,592 records to a different sheet (and also updated the sheet name reference in the code).
Now when I re-run the above SQL I get the proper record count, 14,592. But I need to further narrow down the records so I add more SQL as follows:

str_SQL = "SELECT * From [" & sheetName & "$]" & _
"WHERE [Profession] = 'Veterinary Medicine' " & _
"AND [Type] = 'Veterinarian' Or [Type] = 'Veterinarian Cln Ac Ltd' Or [Type] = 'Veterinarian Ed Ltd' Or [Type] = 'Veterinarian Nonaprv Prgm Ltd'"

Actually, to save space here, technically I first added the initial WHERE clause and when that record count was correct I added the following AND clause.
This query returns the correct number of records, 4,584.

But if I run the SQL below:

str_SQL = "SELECT * From [" & sheetName & "$]" & _
"WHERE [Profession] = 'Veterinary Medicine' " & _
"AND [Type] = 'Veterinarian' Or [Type] = 'Veterinarian Cln Ac Ltd' Or [Type] = 'Veterinarian Ed Ltd' Or [Type] = 'Veterinarian Nonaprv Prgm Ltd' " & _
"AND [County] = 'Livingston' Or [County] = 'Macomb' Or [County] = 'Monroe' Or [County] = 'Oakland' Or [County] = 'Washtenaw' Or [County] = 'Wayne'"

I get 8,741 records.

As a further test, if I use the below line on the resulting recordset: rstData.Filter = "County = 'Macomb'" I get 1,099 records instead of the correct record count of 184.

It is evident the .Filter is filtering the recordset albeit not to the correct record count.
Most likely, the .Filter is resulting in the wrong record count because the initial recordset has way too many records. I know this to be true because if I use the filter rstData.Filter = "County = 'Macomb'" on the *last *SQL incarnation that has the correct number of records, the filter results in 184 records as expected.

But what's going on with that last SQL?
How is the resulting recordset getting *more *records than the SQL that doesn't include the last AND clause?

Solution

When mixing AND and OR in SQL you really need parentheses to make sure the logic is doing what you expect....

...AND A or B or c
   AND C or D or E

should probably be

...AND (A or B or c)
   AND (C or D or E)

This (for example):

AND [County] = 'Livingston' Or [County] = 'Macomb' Or [County] = 'Monroe' Or [County] = 'Oakland' Or [County] = 'Washtenaw' Or [County] = 'Wayne'"

could also be written as:

AND [County] IN ('Livingston','Macomb','Monroe','Oakland','Washtenaw', 'Wayne')

...which can also help resolve the AND/OR issue.