If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2003 with over 1 million records on a single file
I don't use the above, but this is from a end-user question.
1. they try to load the records into excel, but fails. Is there any tools or utilities to break the single access file *.mdb into multiple mdb files so that records will be fewer in each new mdb files to allow loading into excel ? p/s : we are using office 2003 |
#2
|
|||
|
|||
Access 2003 with over 1 million records on a single file
On Thu, 23 Oct 2008 09:26:11 +0800, "IT Staff" wrote:
I don't use the above, but this is from a end-user question. 1. they try to load the records into excel, but fails. Is there any tools or utilities to break the single access file *.mdb into multiple mdb files so that records will be fewer in each new mdb files to allow loading into excel ? p/s : we are using office 2003 An Access *DATABASE* does not contain "records". It contains tables (which contain records); so the direct answer to your question is No. What you can do, however, is create Queries based on the table that you're trying to export. These queries can use criteria based on fields in the table to select subsets of the table to export, or can use the Top Values property of the query to select 65536 records (the limit for exporting). If they're exporting into Excel2003, however, I'm pretty sure there is still a 65536 row limit *in the spreadsheet* so a million rows won't fit. I realize that the limit has been lifted in 2007 but I'm not sure how you could do the export in one shot. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Access 2003 with over 1 million records on a single file
On Wed, 22 Oct 2008 19:50:06 -0600, John W. Vinson
wrote: It would also be interesting to know what the user wants to do with 1M (or whatever subset) records in Excel. If she wants to make some further calculations it is good to know Access can do most things Excel can do. -Tom. Microsoft Access MVP On Thu, 23 Oct 2008 09:26:11 +0800, "IT Staff" wrote: I don't use the above, but this is from a end-user question. 1. they try to load the records into excel, but fails. Is there any tools or utilities to break the single access file *.mdb into multiple mdb files so that records will be fewer in each new mdb files to allow loading into excel ? p/s : we are using office 2003 An Access *DATABASE* does not contain "records". It contains tables (which contain records); so the direct answer to your question is No. What you can do, however, is create Queries based on the table that you're trying to export. These queries can use criteria based on fields in the table to select subsets of the table to export, or can use the Top Values property of the query to select 65536 records (the limit for exporting). If they're exporting into Excel2003, however, I'm pretty sure there is still a 65536 row limit *in the spreadsheet* so a million rows won't fit. I realize that the limit has been lifted in 2007 but I'm not sure how you could do the export in one shot. |
#4
|
|||
|
|||
Access 2003 with over 1 million records on a single file
John:
For Excel 2007 / Access 2007 You can export using the Excel object model and use the CopyFromRecordset object to quickly place hundreds of thousands of records (I've done up to 300,000 before without a problem). You might be able to export a million records that way as well. It would take a little bit longer than the normal CopyFromRecordset that most of us are used to but hey isn't that to be expected? -- Bob Larson Access MVP Free Tutorials and Samples at http://www.btabdevelopment.com __________________________________ "John W. Vinson" wrote: On Thu, 23 Oct 2008 09:26:11 +0800, "IT Staff" wrote: I don't use the above, but this is from a end-user question. 1. they try to load the records into excel, but fails. Is there any tools or utilities to break the single access file *.mdb into multiple mdb files so that records will be fewer in each new mdb files to allow loading into excel ? p/s : we are using office 2003 An Access *DATABASE* does not contain "records". It contains tables (which contain records); so the direct answer to your question is No. What you can do, however, is create Queries based on the table that you're trying to export. These queries can use criteria based on fields in the table to select subsets of the table to export, or can use the Top Values property of the query to select 65536 records (the limit for exporting). If they're exporting into Excel2003, however, I'm pretty sure there is still a 65536 row limit *in the spreadsheet* so a million rows won't fit. I realize that the limit has been lifted in 2007 but I'm not sure how you could do the export in one shot. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Access 2003 with over 1 million records on a single file
=?Utf-8?B?Ym9ibGFyc29u?= wrote
in : For Excel 2007 / Access 2007 You can export using the Excel object model and use the CopyFromRecordset object to quickly place hundreds of thousands of records (I've done up to 300,000 before without a problem). You might be able to export a million records that way as well. It would take a little bit longer than the normal CopyFromRecordset that most of us are used to but hey isn't that to be expected? Er, what? I use TransferSpreadsheet when I need to output Access data for Excel. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#6
|
|||
|
|||
Access 2003 with over 1 million records on a single file
David:
Our initial tests with Access 2007 and Excel 2007 is that TransferSpreadsheet doesn't deal with the large, large datasets (over 100,000 records). If you haven't had that problem, can you verify that? -- Bob Larson Access MVP Free Tutorials and Samples at http://www.btabdevelopment.com __________________________________ "David W. Fenton" wrote: =?Utf-8?B?Ym9ibGFyc29u?= wrote in : For Excel 2007 / Access 2007 You can export using the Excel object model and use the CopyFromRecordset object to quickly place hundreds of thousands of records (I've done up to 300,000 before without a problem). You might be able to export a million records that way as well. It would take a little bit longer than the normal CopyFromRecordset that most of us are used to but hey isn't that to be expected? Er, what? I use TransferSpreadsheet when I need to output Access data for Excel. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#7
|
|||
|
|||
Access 2003 with over 1 million records on a single file
=?Utf-8?B?Ym9ibGFyc29u?= wrote
in : Our initial tests with Access 2007 and Excel 2007 is that TransferSpreadsheet doesn't deal with the large, large datasets (over 100,000 records). If you haven't had that problem, can you verify that? Well, I don't have Access or Excel 2007. I would assume that TransferSpreadsheet in Access 2007 would have been extended to allow for exporting more rows than the old 8-bit limit by choosing the appropriate value for your Spreadsheet Type parameter. In A2K3, the highest version is indicated by the built-in constant acSpreadsheetTypeExcel9 (which would be Excel 2000, the last time the file format was changed), so I'd assume there's a new acSpreadsheetTypeExcel12 constant, for Excel 2000 format. Isn't there? -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#8
|
|||
|
|||
Access 2003 with over 1 million records on a single file
acSpreadsheetTypeExcel12
acSpreadsheetTypeExcel12Xml "David W. Fenton" wrote in message 36.94... =?Utf-8?B?Ym9ibGFyc29u?= wrote in : Our initial tests with Access 2007 and Excel 2007 is that TransferSpreadsheet doesn't deal with the large, large datasets (over 100,000 records). If you haven't had that problem, can you verify that? Well, I don't have Access or Excel 2007. I would assume that TransferSpreadsheet in Access 2007 would have been extended to allow for exporting more rows than the old 8-bit limit by choosing the appropriate value for your Spreadsheet Type parameter. In A2K3, the highest version is indicated by the built-in constant acSpreadsheetTypeExcel9 (which would be Excel 2000, the last time the file format was changed), so I'd assume there's a new acSpreadsheetTypeExcel12 constant, for Excel 2000 format. Isn't there? -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|