Corrupt/ Bad Recordset

[SSweez]

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.

 

[Mike]

Could you maybe post your code and has it always been this way or has it
worked before

 

[Mike]

Could you post your code And has this CopyFromRecordset worked correct before

 

[SSweez]

Could you post your code And has this CopyFromRecordset worked correct before





- Show quoted text -

The copyfromrecord set has always worked. As I have been using/
testing the worksheet more I have noticed this intermitant issue. Here
is my code:

dbpath = ThisWorkbook.Names("dbpath").RefersToRange
dbpass = ThisWorkbook.Names("dbpass").RefersToRange
dbdir = ThisWorkbook.Names("dbdir").RefersToRange
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbpath &
"; Jet OLEDBatabase Password = " & "'" & dbpass & "'"
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = sConnect
adoConn.Open
rsSpendData.Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly
If rsSpendData.EOF Then
rsSpendData.MoveFirst
Sheets("Spend Detail").Range("J27").CopyFromRecordset rsSpendData
Else
Sheets("Spend Detail").Range("J27").CopyFromRecordset rsSpendData
End If


I would post the SQL but it is really long. If I put the SQL directly
in an Access Query it always works.

 

[Mike]

try this I'm not sure why you have
Sheets("Spend Detail").Range("J27").CopyFromRecordset rsSpendData twice
that could be you dup data


Do While rsSpendData.EOF = False
'If rsSpendData.EOF Then
'rsSpendData.MoveFirst
Sheets("Spend Detail").Range("J27").CopyFromRecordset rsSpendData
'Else
'Sheets("Spend Detail").Range("J27").CopyFromRecordset rsSpendData
'End If
rsSpendData.MoveNext

 

[SSweez]

The copyfromrecord set has always worked. As I have been using/
testing the worksheet more I have noticed this intermitant issue. Here
is my code:

dbpath = ThisWorkbook.Names("dbpath").RefersToRange
dbpass = ThisWorkbook.Names("dbpass").RefersToRange
dbdir = ThisWorkbook.Names("dbdir").RefersToRange
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbpath &
"; Jet OLEDBatabase Password = " & "'" & dbpass & "'"
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = sConnect
adoConn.Open
rsSpendData.Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly
If rsSpendData.EOF Then
rsSpendData.MoveFirst
Sheets("Spend Detail").Range("J27").CopyFromRecordset rsSpendData
Else
Sheets("Spend Detail").Range("J27").CopyFromRecordset rsSpendData
End If

I would post the SQL but it is really long. If I put the SQL directly
in an Access Query it always works.- Hide quoted text -

- Show quoted text -

Some more info. The sheet I am copying into has a number of hidden
rows and columns that the data will go into. The rows are filtered
using Excel. I created a new sheet and used but the following code in
from of the code above that copies in the "Spend Detail" tab:

sheets("sheet1").range("a1").copyfromrecordset rsspenddata

Strange thing is that the data that went into the new sheet was fine
but the data that was copied into the "Spend Detail" sheet was still
"corrupt." When I compared the two sets of data 21 of the 630 records
were different. The records that come in different (actually they are
duplicate records that should not be there) seem to happen at random.

 

[Mike]

I'm not really sure what to do about the hidden rows and columns sorry