Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Problem with CopyFromRecrodset | Excel Programming | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |