Excel VBA query to Access DB not returning all rows

Hoping someone has some ideas on how to solve this issue:

I have a macro in excel which runs a query against an Access DB (it
actually calls a query inside of Access). The query is not bringing
back all of the records even though I can run the query directly in
Access or Microsoft Query and get all the records returned. For ease of
troubleshooting the query can be a simple select * call from the macro
to Access. Even with this basic call somehow all of the records are
still not being returned when called from the macro within excel.

As an example one query has around 15 missing records out of about 14
thousand total, one of which of course I need returned for processing
in Excel. I don't see anything different about the record I want
returned; only that it doesn't make it back to excel when calling
through the user initiated macro. At 14 thousand records I am well
within the 65 thousand row excel limit as well. Any ideas of what to
check for next?

Eric

re: Excel VBA query to Access DB not returning all rows

Are you using any kind of loop to read the recordset, or using any
values to define the beginning and end points in the array?

re: Excel VBA query to Access DB not returning all rows

etwebbox@hotmail.com wrote:[color=blue]
> Hoping someone has some ideas on how to solve this issue:
>
> I have a macro in excel which runs a query against an Access DB (it
> actually calls a query inside of Access). The query is not bringing
> back all of the records even though I can run the query directly in
> Access or Microsoft Query and get all the records returned. For ease of
> troubleshooting the query can be a simple select * call from the macro
> to Access. Even with this basic call somehow all of the records are
> still not being returned when called from the macro within excel.
>
> As an example one query has around 15 missing records out of about 14
> thousand total, one of which of course I need returned for processing
> in Excel. I don't see anything different about the record I want
> returned; only that it doesn't make it back to excel when calling
> through the user initiated macro. At 14 thousand records I am well
> within the 65 thousand row excel limit as well. Any ideas of what to
> check for next?[/color]

You've only got 15 missing records. Can you easily identify which ones
they are? Is there some commonality? That's where I'd start, doing a
desk check against a selection of records that are displayed.

Edward

re: Excel VBA query to Access DB not returning all rows

There is no loop, in the simple example I gave above it just brings
back the complete select * query to the worksheet and pastes it there.

I will try comparing the result set from the access query and the macro
query and maybe do a distinct filter to find the 15 records when I get
time.
Thanks for your feedback.
Eric