Error on MoveNext on Excel recordset

[aatagri]

I am getting an unknown excel error on the last record of an ADO Excel recordset being called from VB6. The code loops through the records, but on the last record the MoveNext method of the recordset causes this unknown error. It almost seems like the recordset is at the end but rs.EOF is false.
The code used to work fine, but then the excel sheets that are being imported were changed. They were previously being generated in Crystal Reports 8, but the version was changed to CR10. Since then I have been getting this error.

Any insight would be appreciated.
Thanks
Andrew

 

[SkipVought]

Hi,

You could use On Error Resume Next and interrogate the Err.Number for this specific error. When this error occurrs, you are at the EOF.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[aatagri]

The problem with trapping that code is that is an unknown or at least non-specific code. I don't want to get half way through the file, have something happen and then assume I am at the end of the file. I need to ensure all records are processed, or abort the process.

The error code I get back is -2147467259 [Microsoft][ODBC Excel Driver] Unknown

Andrew

 

[bjd4jc]

Have you inspected the ADO errors collection to see if there is a more specific error code than the general ones ADO uses?

See faq222-3704 for more information on what I am talking about.

 

[SkipVought]

What happens if you .MoveLast, .MoveFirst and .Count, to get the row count and then check the current row count with the row count?

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[aatagri]

I also get the error if I try to use the MoveLast method. It must have something to do with the excel file itself, but I am not sure what to check for

Andrew

 

[SkipVought]

Could you copy 'n' paste the last row and the next row in the source data file (only the columns with headings)

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[aatagri]

The application is for end users, so dealing with source data is out of the question. I hav even trying doing a recordcount, but the RecordCount Property returns -1 and if I try to loop through, the same error occurs.

 

[SkipVought]

I am not an end user.

Just wanted to try to see if I could identify a quirk in the data that could be worked around.

But, as one surgeon said to his colleague, "Suture self!"

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[aatagri]

I am sorry. I think I misunderstood your previous post. I thought you were suggesting having someone do a cut and paste as a work around.

The error isn't data related. I am sure of that. I have cut the file in half and it is always the last record, regardless of what data represents that last row.

The error is occuring if I use either the MoveNext on the last row, or the MoveLast method. If I do a watch, after the error is generated rs.EOF is true as expected. I just don't understand why those methods are failing.

 

[SkipVought]

???

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[Swi]

Post your code so we can have a look.

Swi

 

[aatagri]

Dim rs As ADODB.Recordset

sql = "SELECT * from [Sheet1$] Where CountType IN ( 'System', 'Member:', 'Package') "
'creates excel recordset
Set rs = CreateExcelRS(sql, mcConnection, adOpenDynamic)
If rs Is Nothing Then Exit Function

rs.MoveFirst

Dim i As Long
Do Until rs.EOF
Select Case Trim("" & rs!CountType)
Case "Member", "Member:"
miMemberID = Val("" & rs![ Identifier])
Case "System"
piSystemID = Val("" & rs![ Identifier])
End Select

'inserted into Access db
sql = "INSERT INTO DT_ImportCCSATemp (CountType, Identifier, System, Package, Type, Previous)" _
& " VALUES (" & SQLTrimQuotes("" & rs!CountType) & ", " & Val("" & rs![ Identifier]) _
& ", " & SQLTrimQuotes("" & rs!System) & ", " & SQLTrimQuotes("" & rs!Package) _
& ", " & SQLTrimQuotes("" & rs!Type) & ", " & Val("" & rs!Previous) & ")"
ADOExecute sql
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

 

[aatagri]

I have cut off the last few records in the file, so the new last record is one that previously worked before. Therefore I am sure the data is not to blame.
So as the MoveNext is fired after the last row (which should move the pointer to EOF) I get the error. But here is last few records of data

CountType Identifier System Package Type Previous Current
Package 20728 Customer 1 BBC Kids S 1 _________
Package 20732 Customer 1 Travel S 2 _________
Package 20736 Customer 1 Raptors/NBA S 3 _________
Package 22796 Customer 1 Court TV S 30 _________

 

[Swi]

Try something like this to see what is happening. Also, have you stepped through the code to see if everything is working as it should?

Do Until rs.EOF
Select Case Trim("" & rs!CountType)
Case "Member", "Member:"
miMemberID = Val("" & rs![ Identifier])
Case "System"
piSystemID = Val("" & rs![ Identifier])
End Select

'inserted into Access db
sql = "INSERT INTO DT_ImportCCSATemp (CountType, Identifier, System, Package, Type, Previous)" _
& " VALUES (" & SQLTrimQuotes("" & rs!CountType) & ", " & Val("" & rs![ Identifier]) _
& ", " & SQLTrimQuotes("" & rs!System) & ", " & SQLTrimQuotes("" & rs!Package) _
& ", " & SQLTrimQuotes("" & rs!Type) & ", " & Val("" & rs!Previous) & ")"
ADOExecute sql
If rs.EOF Then
Msgbox "End of Recordset reached!"
Exit Do
End If
rs.MoveNext
Loop


Swi

 

[aatagri]

Didn't work. The rs.eof is false until I get the error. If I check the value after the error, rs.eof is true as you would expect.

 

[Swi]

Do you know what entries are in your spreadsheet? If so, add a counter like it looks like you have and increment it to make sure the counter does not exceed the # of records:

Dim i As Long
Do Until rs.EOF
i = i + 1
Select Case Trim("" & rs!CountType)
Case "Member", "Member:"
miMemberID = Val("" & rs![ Identifier])
Case "System"
piSystemID = Val("" & rs![ Identifier])
End Select

'inserted into Access db
sql = "INSERT INTO DT_ImportCCSATemp (CountType, Identifier, System, Package, Type, Previous)" _
& " VALUES (" & SQLTrimQuotes("" & rs!CountType) & ", " & Val("" & rs![ Identifier]) _
& ", " & SQLTrimQuotes("" & rs!System) & ", " & SQLTrimQuotes("" & rs!Package) _
& ", " & SQLTrimQuotes("" & rs!Type) & ", " & Val("" & rs!Previous) & ")"
ADOExecute sql
rs.MoveNext
Loop

Swi

 

[bjd4jc]

I am still curious as to what the underlying ADO errors are being populated as in the Errors Collection of the connection object.

 

[Swi]

Speaking of the connection object, could you please post that code as well. I agree with bjd4jc that you should find out what exactly the underlying error is. The strange thing is that you are only getting the error at the end of the recordset.

Swi

 

[aatagri]

Here is the function that connects to the excel sheet

Public Function ConnectExcel(pcConnection As ADODB.connection, FilePath As String) As Boolean
Dim strConnection As String

On Error GoTo ADOErrors

ConnectExcel = False

strConnection = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & FilePath & ";READONLY=FALSE;"
msExcelConnectString = strConnection
Set pcConnection = New ADODB.connection
pcConnection.ConnectionTimeout = 60
pcConnection.CommandTimeout = 80
pcConnection.Open strConnection

ConnectExcel = (Err.Number = 0)
Exit Function

ADOErrors:
ExcelErrors pcConnection

End Function

And here is the one that creates the recordset


Public Function CreateExcelRS(sSQL As String, pcConnection As ADODB.connection, _
Optional crtype As ADODB.CursorTypeEnum = adOpenForwardOnly, _
Optional lcktype As ADODB.LockTypeEnum = adLockReadOnly, _
Optional timeout As Integer = 60) _
As ADODB.Recordset

On Error GoTo ADOErrors

Set CreateExcelRS = New ADODB.Recordset 'Initialize Recordset to nothing
Dim CurConn As ADODB.connection

Set CurConn = pcConnection

CurConn.CommandTimeout = timeout
CreateExcelRS.Open sSQL, CurConn, crtype, lcktype


If CreateExcelRS.EOF Then
'clear the recordset and just return nothing
Set CreateExcelRS = Nothing
End If


Exit Function

ADOErrors:
ExcelErrors CurConn
Set CreateExcelRS = Nothing

End Function