Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Corrupt/ Bad Recordset
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Corrupt/ Bad Recordset
Could you maybe post your code and has it always been this way or has it
worked before " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Corrupt/ Bad Recordset
Could you post your code And has this CopyFromRecordset worked correct before
" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Corrupt/ Bad Recordset
On Apr 12, 9:42 pm, Mike wrote:
Could you post your code And has this CopyFromRecordset worked correct before " wrote: 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.- Hide quoted text - - 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 OLEDB:Database 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Corrupt/ Bad Recordset
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 " wrote: On Apr 12, 9:42 pm, Mike wrote: Could you post your code And has this CopyFromRecordset worked correct before " wrote: 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.- Hide quoted text - - 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 OLEDB:Database 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Corrupt/ Bad Recordset
On Apr 13, 8:51 am, wrote:
On Apr 12, 9:42 pm, Mike wrote: Could you post your code And has this CopyFromRecordset worked correct before " wrote: 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.- Hide quoted text - - 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 OLEDB:Database 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Corrupt/ Bad Recordset
I'm not really sure what to do about the hidden rows and columns sorry
" wrote: On Apr 13, 8:51 am, wrote: On Apr 12, 9:42 pm, Mike wrote: Could you post your code And has this CopyFromRecordset worked correct before " wrote: 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.- Hide quoted text - - 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 OLEDB:Database 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Corrupt | Excel Discussion (Misc queries) | |||
NK2 not corrupt but not being seen | Excel Discussion (Misc queries) | |||
Corrupt Cells? | Excel Discussion (Misc queries) | |||
Corrupt Spreadsheet | Excel Discussion (Misc queries) | |||
Type recordset/recordset? | Excel Programming |