Link to home
Start Free TrialLog in
Avatar of Laurence Martin
Laurence MartinFlag for United Kingdom of Great Britain and Northern Ireland

asked on

What's wrong with CopyFromRecordset

I'm still investigating but could an Expert help me look in the right place to solve this problem:

I'm using VBA code in Access to write data into Excel, and after working fine every day for months it's suddenly not right.

The code creates a recordset based on a query (which hasn't changed and returns all the records it should)
I then use CopyRecordset to put the data into Excel.
Set recOrders = CurrentDb.OpenRecordset("QRY_OrderAnalysisFullMS", dbOpenSnapshot, dbSeeChanges)
    varreturn = SysCmd(acSysCmdUpdateMeter, 200)

ActiveWorkbook.Sheets(2).Range("A2").CopyFromRecordset recOrders
recOrders.Close

Open in new window


But only half the records end-up in Excel.

The procedure does the same thing three other times with different queries, and they all work fine and return ten times as many records as the problem query does - so it's not a volume of data issue.

I'm going to look at the data in the record where it stops to see if there are any clues there, but does anyone else have any ideas what could be wrong?
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Laurence Martin

ASKER

Thanks Everyone
I dug deeper and found the problem was an #Error in a field in the next record that CopyFromRecordset was trying to write.

I fixed the data and all worked fine - so that's why the code looked OK and it couldn't be reproduced.

Thanks again.
Nice to hear you found the underlying issue. Curious though, as you were doing the export did you get any sort of error message that something had gone wrong? Or was is silently ignored?