Re: VBA Copyrecordset to Excel



"Ray C" <RayC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
<EC8CD64E-D5AF-4358-84FD-BA579D23C899@xxxxxxxxxxxxx>:
I have a query in my Access database that I need to export in an
existing pre-formatted Excel worksheet. I programmatically place the
results of this query into a recordset, then I use CopyRecordSet to
copy the data into a specific location in the Excel sheet.

Problem:
One field is a Memo field and it looks like the first 255 characters
get transferred correctly, the rest either gets cut off or appears
as gibberish in Excel. Is there a way to send all the data in the
Memo field using CopyRecordSet ?

Thanks in advance
Ray

On some tests I did a while back, I found that with the
copyfromrecordset method, I could transfer up to 1823 with Access 2000,
but it didn't fail until the memo contained more than 65 509
characters.
In the 2003 version, it failed with more than 911 characters.

I don't know whether these are version specific limitations, or
limitations based on other particulars of my setups - so you'd
probably need to perform other tests yourself.

I think I would try to use CopyFromRecordset with the rest of the
recordset, then open a new recordset with only the memo field, loop it
and the cells through automation to place the content of the memo field
correct.

Another alternative, could be to use/try something like

SELECT <column list>, Left(MyMemo, 911)
FROM

but I wouldn't know how that would be performance wise.

--
Roy-Vidar


.



Relevant Pages

  • Re: Access to Excel Automation ADODB Problem
    ... > Automation to export data from Access to Excel. ... > fields in this recordset is a memo field which will often ... You don't even need a recordset ...
    (microsoft.public.excel.programming)
  • Re: VBA - Create a recordset with no records for inserting
    ... getting SSIS involved until we had our data suitable for bulk insert. ... In this case, we are already in Excel and have a database connection, so I'm ... ' the "where 1=2" guarantees that an empty recordset will be returned ... You should probably investigate using SQL Server Integration Services ...
    (microsoft.public.data.ado)
  • Re: Convert from WrapText
    ... this amount of data from an Excel cell (without widening the Access ... So ideally I wish to take the workbooks into Access and display ... here is the code used for importing the Excel data: ... >> Access would treat each of these rows as a recordset when imported, ...
    (microsoft.public.excel.programming)
  • RE: Exporting Access table using a button click in exce
    ... I have a code that is applied in excel vba. ... Specifically I am looking for looping through a recordset and export the data ... Dim cnn As Object, strQuery As String, rst As Object ...
    (microsoft.public.access.modulesdaovba)
  • Re: Excel Application wont quit
    ... mostly because I don't know much about using recordsets like that in Excel. ... workbook - and excel - before opening the database and recordset. ... create & set a named range equal to first four columns, ...
    (microsoft.public.word.vba.general)