| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Access recordset to import to Excel using ADO
I will be grateful if someone will give me the code to determine if when
importing data using ADO from say Access toExcel if a recordset is greater than 65,536 rows. i appreciate this is not an issue with Excel 2007 -- with kind regards Spike |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Access recordset to import to Excel using ADO
Why not just use the 'TOP' predicate regardless??
"Select top 65000 [x] From [y]..." Note that TOP requires an ORDER BY clause or you'll just get abitary records. It's not an issue if less than the specified number of records are returned, but you will not get any more than you want Simplistic answer - there are other issues like keeping track of where you are if you need to import the remainder into another sheet. Other than that, the recordset will have a RECORDCOUNT property, but not sure if this accurately reflects the record count just after opening. Remember DAO where you had to navigate to the lst record before you could get an an accurate count? I'm sure a quick google should clear that question up ... J Spike: I will be grateful if someone will give me the code to determine if when importing data using ADO from say Access toExcel if a recordset is greater than 65,536 rows. i appreciate this is not an issue with Excel 2007 -- with kind regards Spike |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Access recordset to import to Excel using ADO
thank you for that, much appreciated. i think i need to play around with it
a bit more. I thought there would be a simple answer so if you found you had say 250K records so you could import the first say 65K to on e sheet and so on till they were all imported but it is obviously not as simple as that! -- with kind regards Spike "J" wrote: Why not just use the 'TOP' predicate regardless?? "Select top 65000 [x] From [y]..." Note that TOP requires an ORDER BY clause or you'll just get abitary records. It's not an issue if less than the specified number of records are returned, but you will not get any more than you want Simplistic answer - there are other issues like keeping track of where you are if you need to import the remainder into another sheet. Other than that, the recordset will have a RECORDCOUNT property, but not sure if this accurately reflects the record count just after opening. Remember DAO where you had to navigate to the lst record before you could get an an accurate count? I'm sure a quick google should clear that question up ... J Spike: I will be grateful if someone will give me the code to determine if when importing data using ADO from say Access toExcel if a recordset is greater than 65,536 rows. i appreciate this is not an issue with Excel 2007 -- with kind regards Spike |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Access recordset to import to Excel using ADO
Sorry for the infrequent replies, I'm not around here much.
If you still have issues with this, have a look at the COPYFROMRECORDSET method of the RANGE object in Excel. Basically: Range("A2").CopyFromRecordset rs This also has an option to limit the number of rows to copy (and columns if you're interested) Move to the next sheet. Set the record pointer of the recordset to the first record to copy and repeat the COPYFROMRECORDSET Repeat as necessary. I think that should solve your problem... J Spike: thank you for that, much appreciated. i think i need to play around with it a bit more. I thought there would be a simple answer so if you found you had say 250K records so you could import the first say 65K to on e sheet and so on till they were all imported but it is obviously not as simple as that! -- with kind regards Spike "J" wrote: Why not just use the 'TOP' predicate regardless?? "Select top 65000 [x] From [y]..." Note that TOP requires an ORDER BY clause or you'll just get abitary records. It's not an issue if less than the specified number of records are returned, but you will not get any more than you want Simplistic answer - there are other issues like keeping track of where you are if you need to import the remainder into another sheet. Other than that, the recordset will have a RECORDCOUNT property, but not sure if this accurately reflects the record count just after opening. Remember DAO where you had to navigate to the lst record before you could get an an accurate count? I'm sure a quick google should clear that question up ... J Spike: I will be grateful if someone will give me the code to determine if when importing data using ADO from say Access toExcel if a recordset is greater than 65,536 rows. i appreciate this is not an issue with Excel 2007 -- with kind regards Spike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Access recordset to import to Excel using ADO
think i have answered my own question, if you set the cursor to either
adOpenKeyset of adOpenStatic then you do get a return using "recordcount" -- with kind regards Spike "J" wrote: Why not just use the 'TOP' predicate regardless?? "Select top 65000 [x] From [y]..." Note that TOP requires an ORDER BY clause or you'll just get abitary records. It's not an issue if less than the specified number of records are returned, but you will not get any more than you want Simplistic answer - there are other issues like keeping track of where you are if you need to import the remainder into another sheet. Other than that, the recordset will have a RECORDCOUNT property, but not sure if this accurately reflects the record count just after opening. Remember DAO where you had to navigate to the lst record before you could get an an accurate count? I'm sure a quick google should clear that question up ... J Spike: I will be grateful if someone will give me the code to determine if when importing data using ADO from say Access toExcel if a recordset is greater than 65,536 rows. i appreciate this is not an issue with Excel 2007 -- with kind regards Spike |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Access recordset to import to Excel using ADO
Spike,
If this is still an issue, posted more comments on Feb 8th @ 21:00hrs... somehow or another is ended up in a seperate thead... J Spike: think i have answered my own question, if you set the cursor to either adOpenKeyset of adOpenStatic then you do get a return using "recordcount" -- with kind regards Spike "J" wrote: Why not just use the 'TOP' predicate regardless?? "Select top 65000 [x] From [y]..." Note that TOP requires an ORDER BY clause or you'll just get abitary records. It's not an issue if less than the specified number of records are returned, but you will not get any more than you want Simplistic answer - there are other issues like keeping track of where you are if you need to import the remainder into another sheet. Other than that, the recordset will have a RECORDCOUNT property, but not sure if this accurately reflects the record count just after opening. Remember DAO where you had to navigate to the lst record before you could get an an accurate count? I'm sure a quick google should clear that question up ... J Spike: I will be grateful if someone will give me the code to determine if when importing data using ADO from say Access toExcel if a recordset is greater than 65,536 rows. i appreciate this is not an issue with Excel 2007 -- with kind regards Spike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code for getting Access recordset into Excel | Excel Programming | |||
| import excel to recordset in ASP | Excel Programming | |||
| Access Recordset Rows to Excel | Excel Programming | |||
| How to populate Excel Range from Access RecordSet? | Excel Programming | |||
| Acquiring a single recordset from Access into Excel | Excel Programming | |||
Hybrid Mode
