Getting the recordcount using ADO with Excel.

[wadey]

Hi,
The following code returns a recordcount of -1 for a spreadsheet that contains 198 rows. the version of XL is 2000. What is wrong? :-

the dlgUpload is a commondialog control.

Public Excel_Connection As ADODB.Connection
Public Excel_RecordSet As ADODB.RecordSet
Public Excel_SQL As String
Private Sub Form_Load()
Dim strFolderPath As String

On Error GoTo ErrHandler

With dlgUpload
.DefaultExt = ".xls"
.FilterIndex = 2
.DialogTitle = "Please select XLS file"
.Filter = "XL Spreadsheet (*.xls)|*.xls"
.ShowOpen
End With

strFolderPath = dlgUpload.FileName

Set Excel_Connection = New ADODB.Connection
Set Excel_RecordSet = New ADODB.RecordSet

With Excel_Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;IMEX=1;HDR=YES"
.Open strFolderPath
End With

With Excel_RecordSet
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With

Excel_SQL = "select * FROM [Sheet 1$]"
Set Excel_RecordSet = Excel_Connection.Execute(Excel_SQL)

MsgBox Excel_RecordSet.RecordCount

Exit Sub

ErrHandler:

MsgBox Err.Description
End Sub

 

[JerryKlmns]

Recordcount of -1 happens on a forward only cursor.
There is an open debate whether to trust or not the RecordCount property. The trustworthy way is this SQL statement

"SELECT Count(*) FROM [Sheet 1$]"

which counts all records.
If you just need a pass of the recordset and the RecordCount I would try the counting sql for the RecordCount and a forward only cursor. It is indeed two questions to the database but you get a valid RecordCount and a fast openning of the recordset (adUseServer + adOpenForwardOnly + adLockReadOnly) for both questions.

 

[Reuel]

Here's a question:

When you run this, do you see any results in your recordset? If so, add the following line before your count:

Excel_RecordSet.MoveLast

Some recordsets do not show anything for the record count until the last record is selected.

 

[BobRodes]

Well, Reuel, that won't be the case with a client-side recordset, since it's static by definition, and static recordsets are fully populated at opening. (ForwardOnly and Keyset recordsets are not.) Default values for a cursor are the ones that JerryKlims has in his response, and these will show a recordcount of -1. When you change the cursorlocation to aduseclient, you get a proper recordcount after opening the recordset. So, wadey's settings (by the way, wadey, once you set the cursorlocation to aduseclient, your other settings are defaults and therefore unnecessary to spell them out) should return a proper record count.

So, my first hypothesis is that the Excel OLE DB provider is forcing the recordset back to a server side one. Wadey, perhaps you can verify whether the recordset's cursorlocation is still client side once you do the connection.execute method. That's where I'd look first.

HTH

Bob

 

[wadey]

Cheers BobRodes,

I replaced the execute part of my code with the folowing and it works fine:-



With Excel_RecordSet
.CursorLocation = adUseClient
End With

Excel_SQL = "select * FROM [Sheet 1$]"
Excel_RecordSet.Open Excel_SQL, Excel_Connection, adOpenStatic

'Excel_RecordSet.MoveLast
MsgBox Excel_RecordSet.RecordCount


Regards.

 

[JerryKlmns]

wadey
In your first post you define the properties of the recodset and then
Set Excel_RecordSet = Excel_Connection.Execute(Excel_SQL)
which results to default settings of --> The returned Recordset object is always a read-only, forward-only cursor

Your last variation uses all previously defined property settings and then opens. Something like...

With Excel_RecordSet
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "select * FROM [Sheet 1$]"
.Open
MsgBox .RecordCount
End With

You don't need the .MoveLast method as Bob mentioned before.

 

[wadey]

yeah no probs. it is commented out that last bit of code.

Regards

 

[Reuel]

Well, spank me and call me Shirley! I learned something today. Thanks!

 

[JerryKlmns]

Oops

forgot the ActiveConnection

With Excel_RecordSet
.ActiveConnection = Excel_Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "select * FROM [Sheet 1$]"
.Open
MsgBox .RecordCount
End With

 

[BobRodes]

Jerry, I would personally eliminate the activeconnection, cursortype and source properties, since they aren't neccessary. Basically, you're maintaining unneccessary state across function calls, which isn't generally accepted as best practice. I would do this:

With Excel_RecordSet
   .CursorLocation = adUseClient
   .CursorType = adOpenStatic
   .LockType = adLockOptimistic
   .Open "select * FROM [Sheet 1$]", Excel_Connection
   MsgBox .RecordCount
End With

Passing the values as arguments to the Open method makes the object less stateful and therefore more efficient.

HTH

Bob

 

[tsdragon]

Don't forget to .MoveFirst before you trying processing that recordset!!!

(voice of experience)

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[BobRodes]

Hi Tracy,

Could you explain in more detail when you feel you should use MoveFirst?

Thanks,

Bob

 

[JerryKlmns]

Bob

Thanks for sharing your knowledge.

But let 's expand on the "less stateful and therefore more efficient."

The ultimate would be

With Excel_RecordSet
   .CursorLocation = adUseClient
   .Open "select * FROM [Sheet 1$]", Excel_Connection
   MsgBox .RecordCount
End With

Am I right?
The gain would be time saving from
- using default values (CursorType & and
- not assigning values to properties of the recordset
(function calls)

And the loss would be the not so visible defined properties?

--------------
I 'm on project ending end of October on a full day basis away from office, so I might not respond soon.

 

[tsdragon]

I you use .MoveLast to get the number of records in a recordset, and then decide you want to process the records in that recordset (other than the last one), then you'd better do a .MoveFirst, otherwise you'll only ever see the last record of the recordset. We were talking about using .MoveLast, weren't we?



Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[strongm]

>We were talking about using .MoveLast, weren't we?

Nope. Moved beyond that.

 

[BobRodes]

Jerry, yes, that's more efficient. I meant to remove the cursortype line, must have missed it. Furthermore, the LockType property is superfluous as it only applies to updatable recordsets. Setting the value to adLockBatchOptimistic will make a disconnected recordset updatable as a batch. (You reopen the connection and call the UpdateBatch method.)

So, yes, yours improves on mine. However, to be most efficient, the recordset should be the result of a stored procedure call. Furthermore, this prevents injection attacks. (chiph has an excellent faq somewhere on injection attacks somewhere.)

Bob

 

[JerryKlmns]

Bob

Roger that!