Recordset Corruption

[SSweez]

I have been experiencing an issue that I just can not figure out.
Please let me know if there might be a more appropriate group to post
this question in. I am using Microsoft Jet OLEDB to retrieve a
recordset from an Access Database over our company network. I use a
"CopyFromRecordset" to write the data to a worksheet in Excel. I will
often see the data that is imported corrupted. A good bit of the data
appears dublicated (multiple records that are the same that really
should be uniqe) and sometimes in the wrong columns. Even stranger is
when data appears in the worng column it only does so for certian
records and not others. If I capture the SQL through a debug.prinnt
and run it directly in Access I get a clean recordset every time.
Does anyone know what the problem might be and how to fix it? Any
help would be greatly appreaciated. Thanks.

 

[Roger Whitehead]

I don't know what's going on, but years ago I had strange character sets
appear when doing DAO CopyFromRecordSet imports from otherwise simple data
tables (though in my case, an Informix DB). The solution may have been
available elsewhere, but I never found it.

I now use either:
ADO (which does not have a CopyFromRecordset method), so I have to loop
through the fields collection within a Recordset.MoveNext loop.
This may be slower (I'm not fully convinced by ADO over DAO), but I get
the results I need.
Or
A simple QueryTable import, where I've written the SQL in VBA.

I realise the above doesn't fully answer your question, but I hope it
helps...