strange behavior with copyFromRecordset/ADO

[Mary]

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 worksheet. 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 sheet however, column A is formatted
with some unrequested combination of fill and pattern. My
program/application has been working fine for months (using oo4o to
connect to oracle) I need to change to ADO for improved performance
with the specific query used by the application and I am hoping to use
CopyFromRecordset again for improved performance. I believe I have
isolated this problem to this line (when this line is not in the
program the strange behavior does not occur).

Any help would be appreciated.

Mary

 

[Robin Hammond]

Mary,

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.

I don't have a solution for you, but if you find anything, please let me
know direct.

Jamie, KeepItCool, anyone else who does data stuff have any ideas or a
similar experience?

Robin Hammond
www.enhanceddatasystems.com

 

[Jamie Collins]

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 worksheet 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.

--

 

[Mary]

Thanks Robin/Jamie

I think I fixed my problem. The problem I described was intermittent
too - only it occured more frequently than not. I converted the first
column of my data set from a date field to a character field. Now I
can no longer seem to recreate the problem.

Thanks again,

Mary

 

[Robin Hammond]

That might explain it. Most of my recordsets have dates in the first field
too. Glad you have fixed it. If it becomes a problem we now know that we can
try your workaround.

Robin Hammond
www.enhanceddatasystems.com