Best way to connect from VB6

I'm currently working on a project that is going to read/write lot of data into a SQL EXPRESS 2005.

What is BEST WAY(faster and reliable) for connecting, reading, writing updating from VB6...

The code below is working except the RecordCount that always returns -1

I've tried to do a Movelast before, doing so produce this error --> rowset does not support fetching backward

Private ObjConn As New ADODB.Connection
Private ObjRS As New ADODB.Recordset

...

in a function....
Dim i As Integer, j As Integer

With ObjConn
.ConnectionTimeout = 30
.CommandTimeout = 30
.Provider = "SQLOLEDB"
.ConnectionString = "Driver={SQL Native Client};Server=MACHINE\SQLEXPRESS;Database=DSD;UID=sa;PWD=MYPass;"
End With
ObjConn.Open
strRequest = "SELECT * FROM dbo.Site"
ObjRS.Open strRequest, ObjConn
j = 0
MsgBox ObjRS.RecordCount ' How to get NUMBER OF RECORDS BEFORE LOOPING ?

Do While Not ObjRS.EOF
task(j).SiteID = ObjRS.Fields(ObjRS.Fields(0).Name).Value
' THIS SECTIONS WORKS
ObjRS.MoveNext

j = j + 1
Loop
ObjConn.Close
test = task
End FunctionThanks for helping ! I'm stuck in the middle...

Merci

For MsgBox ObjRS.RecordCount ' How to get NUMBER OF RECORDS BEFORE LOOPING ?

Try one of the following

1) ObjRS.CursorLocation = adUseClient

2) ObjRS.Open strRequest, ObjConn, adOpenKeyset

Hope this helps

|||

The reason you can not move backwards or get the record count is because of the fact that you are getting the SQL Server Firehose cursor as a result. You can not get the record count until you reach the last record for your query. Similarly Firehose cursors do not support going backwards. The advantage of firehose cursors is that they are the fastest way of fetching SQL Server data, however they do not support updates. If you want to perform updates you have to request different cursor types when calling Open on the Recordset object. For example if you use Static cursor using adOpenStatic parameter to Open you will get the Recordcount correctly. Read the section title : "Understanding Cursors and Locks" in the ADO documentation in MSDN.

Thanks

Waseem Basheer

|||

RecordCount returns -1 when you are using a forward-only cursor or a dynamic cursor (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp). Since the default is a forward only cursor, that would explain the behavior that you're seeing.

As Waseem mentions, this cursor type would also explain the behavior you're seeing with MoveLast.

If you switch to using a static cursor, this property should have the row count value that you were expecting (the code snippet doesn't appear to have any need to update data, so this would presumably be sufficient).