Microsoft Access Question

How to copy more than 65,000 records to the clipboard

[Compworld]

Hello,

I need to copy approx 125,000 records from Microsoft Access XP to Microsoft Excel XP, but I receive a message that the clipboard can only hold 65,000 records at a time. Is there any way to extend the amount of records that can be copied to the clipboard?

Thanks for your help in advance.

Regards,

CompWorld

[Paul in South Africa]

Why not Export the table concerned to an Excel spreadsheet file.

[Mardi_Gras]

You can't put all of those records into Excel - it can only handle 65,536 rows. :(

[lorax]

You could dump them out to a comma delimited file of some sort and then import them into something like MultiEdit if the goal here is to get them into something more portable.

[pendanticist]

Hey CompWorld,

I just spoke with my old MIS professor and he insists you can go upwards of a million entries in Access.

Is there a possibility you don't have all the updates, or that another problem may exist? Maybe something in the clipboard itself?

While I didn't specify XP to him, that shouldn't matter.

Hope this helps.

Pendanticist.

[paladin]

in the access database..did you try:
TOOLS=>OFFICE LINKS=>ANALYZE WITH MS EXCELL

?

[Mardi_Gras]

I just spoke with my old MIS professor and he insists you can go upwards of a million entries in Access.

There is no doubt he is right - unfortunately, compworld is trying to get those records into Excel - and Excel can't handle more than 65,000 rows.

[pendanticist]

.cixelsyd eb tsum I

<ylhsipeehs rehtar dais eh>

.tsicitnadneP

[Mardi_Gras]

:) ynnuf yrev

[Compworld]

Hello all,

Thanks for the above replies. Is there a way to increase the amount of lines that the clipboard can hold? right now its also at 65,533, and I need to somehow get it higher.

Any suggestions?

Thanks,

CompWorld

[Mardi_Gras]

compworld - what exactly are you trying to do? I don't think it is possible to put more rows in the clipboard but there may be other ways to accomplish what you want.

I know your original plan was to copy those rows into Excel...since that is not possible, I presume you are looking to move them somewhere...with a little more background maybe someone can come up with a solution.

[Compworld]

I was told that I may be able to copy the records in to open office or Corel Office since the limitations aren't the same as in Microsoft Office. So, I had thought that there might be a way to increase the amount of records that the clipboard holds. Isn't there like a program that could be installed over the clipboard manager in order to do something like this?

Thanks,

CompWorld

[Robino]

The question is: Why do you need it in Excel?

[Compworld]

hello,

I need to pull specific fields from the server and then prepare it for a data feed. The way that I was told was to copy the fields from access and move them to excel. So, I am not at the point that my fields are over 100,000, and I cannot go over 65533. Any suggestions?

Thanks,

CompWorld

[Mardi_Gras]

I may be able to copy the records in to open office or Corel Office

I don't know about Corel, but OpenOffice is limited to just 32,000 rows - so you won't find your solution there.

I need to pull specific fields from the server and then prepare it for a data feed.

What does preparing the data involve? Is there any way to just pull it from Access directly? Or is there some data manipulation that you are planning to do in a spreadsheet program?

I'm not playing 20 questions, it is just that what you are trying to do is not likely to work the way you are trying to do it (no fault of yours) - and it is hard to propose useful alternate solutions without fully understanding the task :)

[aspdaddy]

>my fields are over 100,000, and I cannot go over 65533. Any suggestions?

Do it in two batches

[Compworld]

Hi all,

The reason for this is that I pull the information from a query that I run within Access. Then once the information is loaded, I need to copy it to a spreadsheet program in order to make the modifications the the information. I had moved it to a text file, but it takes longer, and as the rows grow, it would take several hours to do, instead of just a couple.

Its confusing, but I have to do this in order to submit my stuff through different shopping engines and such.

Any suggestions?

Thanks,

CompWorld

[Robino]

xml?

[Compworld]

Nah, I do not know XML. It is only simplet tiext and links that need to be pullled. Just have so many records, and I just do not know what the best way to export and manage them from Access.

CompWorld

[aspdaddy]

Hi Compworld,

You are confusing me :) coz I dont see the problem you are having.

What are you needing to do in Excel, and what is the final format you want the data in when its done - Database or Spreadsheet?

If its spreadsheet, just deal with it in two batches of 60,000. Or make them even smaller and just have several worksheets open in Excel.

If you need it in a database, you could just export it to .csv format, run a script on it to do whatever transformation, then re-import it into Access.

If you need specific Excel functions, you can automate Excel in the script.

HTH