I'm using Access 2007 with Windows 7 Ultimate. I'm opening an Excel sheet
with 50,000+ record using an ADO recordset. The recordset only has 38,442
records. I've tired it on a few different Excel sheets and it's always the
same (unless there's less than 38,442).

I've tried client side and server side. I've tried every possible
combination of cursor type and lock type and it's always the same. Can
anyone tell me what I'm doing wrong?

Thanks.


--
Matthew Wells
matthew.wells (AT) firstbyte (DOT) net

On Mon, 3 May 2010 08:24:14 -0400, "Matthew Wells"
<matthew.wells (AT) firstbyte (DOT) net> wrote:

Try linking the sheet to your database. Perhaps there really are only
so many records, and the rest are nulls or otherwise of the wrong data
type.

-Tom.
Microsoft Access MVP

I've looked at all the data in all 5 Excel files. It's consistent with no
nulls. All files return 38,442 records.


--
Matthew Wells
matthew.wells (AT) firstbyte (DOT) net

"Tom van Stiphout" <tom7744.no.spam (AT) cox (DOT) net> wrote

Hi Mathew,

with 50,000 records, it is easy to miss things with the human eye. One
thing you could do in Excel VBA is to set a Range object to the sheet's
used range and then debug.print range.rows.count to see how many rows
are in the used range

Dim rng As Range
Set rng = Sheet1.UsedRange
Debug.Print rng.Rows.Count

Note: Sheets("Sheet1") does not appear to contain the UsedRange
Property. You have to use the sheet's codename "Sheet1" (or sheet
whatever). If the used range does contain 50,000 rows then do the
following:

I would write a loop in Excel VBA against your data range against the
unique ID column (or whatever column is supposed to definitely have data
for every row) and see if there are any null rows.

Dim rng As Range, i As Integer
Set rng = Sheet1.UsedRange
For i = 1 To rng.Rows.Count
If rng(i, 1) = "" Then
rng(i, 1).Interior.ColorIndex = 38
Debug.Print "i is: " & i
End If
Next


Rich

*** Sent via Developersdex http://www.developersdex.com ***

On 3 May, 13:24, "Matthew Wells" <matthew.we... (AT) firstbyte (DOT) net> wrote:
Have you tried *importing* the data into access? You may get some
information about rows that fail to import by doing this.

HTH

JB