Copying truncates strings of spaces?

[Me!]

Hi,

Whenever I 'select all records' and copy the data into Excel, one of my
fields is truncated . The fiedls may contain 2 or more spaces within a text
string (an alpha-numeric code) i.e ABC 123 and the number of internal
spaces is truncated i.e. ABC 123 becomes ABC 123

Can anyone advise why this happens and how to prevent it. Unfortunately the
views have >30,000 rows so I can't simply 'Analyse with excel'. (I am
currently having to split my view into 3 separate queries using WHERE
criteria to get each under the 16,000 row limit for 'analysing with excel'
and then re-combining them into one table.)

Any help greatly appreciated.....Thanks.

 

[Tom van Stiphout]

Have you tried exporting to Excel using DoCmd.TransferSpreadsheet?

-Tom.

 

[Me!]

Hi Tom,

I've tried the following but get the error message 'There are too many rows
to output based on a limitation....'.


Public Sub TransferSQLViewToExcel()

DoCmd.OutputTo acOutputStoredProcedure, "SELECT * FROM DEAD_CAR.dbo.CapDer",
acFormatXLS, "U:\Docs\Access Export - Receipt File.xls"

End Sub

I've also tried a simple 'TransferSpreadsheet' macro but it will only work
on tables & not queries or stored procedures? And what I need to output to
Excel is a fairly complex query?

Any advice??

 

[Tom van Stiphout]

There is a way to output data to Excel using Automation, but it is
slow.
There also is a way to attach Excel as a linked table, but if I'm not
mistaken that link is readonly which does not help you.

Let's start at the beginning: why is there a need to export to Excel
in the first place? Access can do many things Excel can.

-Tom.

 

[Me!]

Hi Tom.

I need to export to excel to email the data off-network, so can't think of
how to avoid it?

J.

 

[Tom van Stiphout]

Access has several ways to email data. For example, check out
DoCmd.SendObject. Give it a query name, and out of the door it goes.

-Tom.

 

[Robert Morley]

You might be better off sending it to a text file rather than an Excel
sheet. They're easier to work with from a data point of view. Excel's main
benefit is that it's easier to work with from a human perspective.

If you really need to stick with Excel, you can manage some pretty fast
exports using CopyFromRecordset on the Excel side, but that's getting
moderately advanced. I haven't tried it yet myself, but there was a thread
a few weeks ago that covered exporting very large recordsets to multiple
sheets. See the last message in this thread for more info & code:

http://groups.google.com/group/micr...ead/thread/c5d9e4f42fe7c06b/dfc4842feff44105?


Rob

 

[aaron.kempf]

you could use an enterprise-level ETL tool like SQL Server DTS or
SSIS.

There are many wizards that will do what you are asking of-- copy SQL
Statement X into a spreadsheet.
You _ARE_ using ADP right?

I'd just reccomend buying a developers edition of SQL Server; it is
only $49 and it will allow you to do whatever you need.

I honestly don't think that there would be any licensing issues with
building an SSIS package and then running it via the command line. I
do know that MSDE 2.0 would allow this; it included the Dts
Executable; what was it called again

DtsUi.exe or something?

I mean; if MSDE 2.0 comes with the executable to do it; of course it's
legal right?

Use the developers editon for development.. And then run the DTS
packages on the MSDE machine.

Thanks

-Aaron

 

[Me!]

Thanks for the suggestions everyone. I've definitely got plenty to try.