Export more than 65536 records from ADP to excel 2007

[Tore]

The user has moved from office 2003 to office 2007. The access adp solution
is developed in access 2003 and works perfectly in with office 2007 except
for some details.

How can I export more than 65536 records from access adp client to excel 2007?

Here is a part of the current VBA code:

RS is an adodb.recordset containing the records to be exported.
ExcelRange.CopyFromRecordset RS cannot take more than 65536 records?

' Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets.Item(1)

' Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True

' Copy field names to the first row of the worksheet
fldCount = RS.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = RS.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset RS

 

[Paul Shapiro]

You could use a Select Top 50,000 query and use a where clause to move
through the data in batches. Or you could look into using SQL Server's DTS
or SSIS (depending on SQL Server version) to do the data transfer.

 

[Tore]

Since this is on the client (access adp) side DTS is not what I would like to
use. I had hoped to be able to export to excel 2007 from an adodb.recordset
containling 140 000 records.
Tore

 

[Paul Shapiro]

You could use the first suggestion, exporting in batches.

But you can also download from MS a free DTS runtime package that lets you
run a DTS package on the client. DTS always executes on the client anyway.
That would just give you a way to package up the data transfer and then
still have it execute on the client. I can send a code sample if you need
one. I had updated some code like this for a client a while back.

I would try the batch export first though. I didn't like needing DTSRuntime
configured on a client computer.

 

[Sylvain Lafontaine]

Another possibility would be to do the import from Excel instead of doing
the export from ADP.

 

[Tore]

Thank you for your responses, Paul and Sylvain
I hoped I could reuse the code from my initial post in some way.
excelrange.CopyFromRecordset RS where RS is the recordset holding 140 000
records. I guess CopyFromRecordset has some limits and this cannot be done?

I have seen some proposal on the net where you export to a text file on
local C: disk and then open this textfile wih excel 2007. I will also look
into the DTS solution you suggested.

I hope to limit all changes to my "ExportToExcel" vba procedure that takes
the full recordset at input. Hope not to do any change in forms or SQL Server
2000.

Regards

Tore

 

[Sylvain Lafontaine]

But why don't you simply copy the recordset row by row? Probably that it
will take some more time than with using the CopyFromRecordset method but
are you constrained by time when exporting your Excel file?

 

[Mark Han[MSFT]]

Hi Tore,

Regarding your question, I think that Paul and Sylvain have provided very
good suggestions to you.

Following the advices, I would like to add more comments for your reference.
1 CopyFromRecordset has some limits. If you would like to use
CopyFromRecordset to translate more than 65536 , it might not work. The
issue seems to be by design.

2 To use DTS/SSIS package is suggested. If you have some questions on this,
please let me know.

Hope the above helpful.

Thank you!

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Han[MSFT]]

Hi Tore,

This is Mark. I'm writing to follow the issue.

If I can assist you anything related to the issue, please let me know.

I look forward to your update.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Tore]

The user sets up the select statement to retrieve records form the SQL
server. He selects query from a list and is allowed to modify the sql
statement. The user has limited sql experience, he cannot set up batches of
65000. I want a "one click" export from Access adp to Excel if possible, or
possibly by using file open/save to export to a file (a few more clicks). In
this system all records for one year (about 140 000) would be typical target
for export. Import from excel is not an option, and loping through all the
records takes too much time.

Input parameter to my ExportToExcel procedure is an adodb.recordset holding
all the records. My hope would be to manipulate this recordset into chunks of
65000 records, and then use excelrange.copyFromRecordset.

Regards

Tore

 

[Paul Shapiro]

The only option I can see is to replace the user's direct interaction with
Access with a process running via your code. The good part is you should be
able to create a much better user experience for a user with limited sql
skills, as compared to letting the user work directly in the Access query
builder. I usually build a filtering form that lets the user make any
desired adjustments. Building the form can be complicated depending on the
flexibility they need and the underlying data complexity, but then it works
very well for the users.

A simpler alternative would be letting the user customize the query, and
then click a button calling your code which does the export. That wouldn't
take much work and should accomplish the goal.

 

[Mark Han[MSFT]]

Hi Tore,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

I'm following the issue. Paul has provided suggestion to you and his
usggestion makes sense. Thank you, Paul.

If you have any concerns on that or need me further explanation, please let
me know. Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Han[MSFT]]

Hi Tore,

This is Mark. I'm writing to follow the post.

If you have any concerns or questions on the issue, please let me know.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================