Problem with CopyFromRecrodset
 

I am attempting to get a recordset from MSAccess into a spreadsheet by
executing a query through the use of ADO and then using the CopyFromRecrodset
process to get the data to the spreadsheet.

All works through code as expected to product the spreadsheet with data in
it. However, the problem is that the spreadsheet does not have the same
record count as is returned by running the same query directly from Access.
When run from Access the query returns 6166 recrods. When the spreadsheet is
created there are only 5675 rows created.

Any ideas would be appreciated.

--
HTH

Mr B

Problem with CopyFromRecrodset
 

Thanks for the reply.

There is no SQL query text due to the fact that I am using the following code:
Dim conn As New ADODB.Connection
Dim myrs As ADODB.Recordset
LCMFile = LCMPath & "LCM_file.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
LCMFile & ";"
conn.CursorLocation = adUseClient
Set myrs = conn.Execute("qry_FG12PlusPOReceipt")
oSheet.Range("A1").Select
oSheet.Range("A1").CopyFromRecordset myrs

I have also not been able to determine any specific pattern to the missing
records. The first record returned is the third record from the same query
recrodset in
Access. The missing records do not seem to be at the beginning or at the
end. I have even tried adding the following code in attempt to get all the
records:
myrs.MoveLast
myrs.MoveFirst
Dim RsCnt As Long
RsCnt = myrs.RecordCount

I will look at both recordsets in more detail.

--
HTH

Mr B


"K Dales" wrote:

Can't say why this could happen without seeing the code - especially the SQL
query text. Is there any pattern or distinctive characteristic about the
records that are missing? Are they from the end of the recordset, or
scattered throughout?

--
- K Dales


"Mr B" wrote:

I am attempting to get a recordset from MSAccess into a spreadsheet by
executing a query through the use of ADO and then using the CopyFromRecrodset
process to get the data to the spreadsheet.

All works through code as expected to product the spreadsheet with data in
it. However, the problem is that the spreadsheet does not have the same
record count as is returned by running the same query directly from Access.
When run from Access the query returns 6166 recrods. When the spreadsheet is
created there are only 5675 rows created.

Any ideas would be appreciated.

--
HTH

Mr B

Problem with CopyFromRecrodset
 

"Well, I now have egg on my face.

Upon further examination, I found that there are atcually two queries in
this database. One is named: "qsub_FG12PlusPOReceipt" and the other is
named: "qry_FG12PlusPOReceipt". Seeing both of these query names here like
this they do not seem to be that much alike, but in my haste, I selected the
wrong one in Access.

When I open the same query in Access and by code retrieve the recordset into
Excel I get the same recordset.

My mistake.

Thanks, anyway.
--
HTH

Mr B


"K Dales" wrote:

Can't say why this could happen without seeing the code - especially the SQL
query text. Is there any pattern or distinctive characteristic about the
records that are missing? Are they from the end of the recordset, or
scattered throughout?

--
- K Dales


"Mr B" wrote:

I am attempting to get a recordset from MSAccess into a spreadsheet by
executing a query through the use of ADO and then using the CopyFromRecrodset
process to get the data to the spreadsheet.

All works through code as expected to product the spreadsheet with data in
it. However, the problem is that the spreadsheet does not have the same
record count as is returned by running the same query directly from Access.
When run from Access the query returns 6166 recrods. When the spreadsheet is
created there are only 5675 rows created.

Any ideas would be appreciated.

--
HTH

Mr B