CopyFromRecordset BUG ?

Question

All Variables are declared..Q & R are Strings, RS = DAO.Recordset, Z = Database, objXLSheet is an Excel Worksheet run thru automation.

Snippet of code..Running Access 2003 on XP.

........................................

Q = "SELECT BEnd, Dirt, Paint, Trim, Contam FROM BPRepDR;"

Q = "Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)

R = "TheTopper4": objXLSheet.Range(R).Clear

objXLSheet.Range(R).CopyFromRecordset RS, 53, 5

With RS

 .Close: Set RS = Nothing

End With

.....................................

The ".CopyFromRecordset RS, 53, 5" automates the 1st 4 columns of data correctly, but the 5th Column is brought in as 0, or low numbers (3 or less).  I have read older Topics discussing possible CopyFromRecordset problems.  Any known reason why the 5th Column of data isn't correct ?

TIA - Bob

Answer 1

Jeanette - I'm just seeing this.  Thank you.

I tried your suggestion.  The data is from a "Make Table".  Running another simple Query from the Make Table for the 5 column DOES bring in all the Correct data.

I see my snippet above had an area when I entered it in this Topic, but NOT in the Access module of...

  Q = "SELECT BEnd, Dirt, PaintSeed, Yarn, ASplat FROM BPRepDR;"

  Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)

  R = "TheTopper4": objXLSheet.Range(R).Clear

  objXLSheet.Range(R).CopyFromRecordset RS, 53, 5

  With RS

   .Close: Set RS = Nothing

  End With

For some reason, those first 2 lines of

  Q = "SELECT BEnd, Dirt, PaintSeed, Yarn, ASplat FROM BPRepDR;"

  Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)

..are bringing in a 5th column of data that is useless.

I'll be offline a few days if you see this, but should see it again by Friday 5 Nov 2010 at the latest.

TIA - Bob

Answer 2

I have always been able to export more than 4 columns using CopyFromRecordset. Suggest 1st step is to examine the data in the recordset. Make a query that gets the same data and examine it in datasheet view to see what values are shown in the 5th column.

---

Jeanette Cunningham MVP(Access) Pakenham, Victoria Australia