Re: strange behavior with copyFromRecordset/ADO

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jamie Collins (jamiecollins_at_xsmail.com)
Date: 10/27/04 

Date: 27 Oct 2004 01:12:34 -0700

"Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote ...

> >I have a vba program that, among other things, gets data from an
> > oracle db using ADO. I am trying to use copyFromRecorset to paste the
> > data into a work***. Everything appears to work fine (ie the data
> > is pasted correctly and no errors are visible). When the program is
> > finished and I try to add another *** however, column A is formatted
> > with some unrequested combination of fill and pattern.
> >
> You are not alone. I've seen this one too when dealing with very large
> recordsets, although it seemed to me that it occured when adding a workbook
> while a large recordset is held in memory. You might be right about the
> copyfromrecordset call being at fault though since I haven't tested it
> extensively. Luckily for me, it is intermittent. In my case the first column
> seems to be totally unavailable for use if I try and do anything in the
> corrupted book. I typically run XP, using ADO 2.8. I can say that I don't
> think it is anything to do with your use of Oracle, since I am seeing it off
> a SQL db.
>
> Jamie, KeepItCool, anyone else who does data stuff have any ideas or a
> similar experience?

I have not experienced this problem. I could not reproduce it either.
I tried fetching a one million row table into recordset (using
client-side cursors and server side cursors), added a work*** to
ThisWorkbook, applied a Filter to reduce the recordset's RecordCount
to around 62K rows, used CopyFromRecordset to populate the new
worksheet, added other sheets, saved, opened, etc and had no problems
with formatting or accessing columns.

Jamie. 

--
Quantcast