ADODB in VB6

[Andrzejek]

For years I have been using this to connect to ORACLE:
(With Reference to Microsoft ActiveX Data Object X.X Library)

Public Cn As ADODB.Connection

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Some connect string here"
Cn.CursorLocation = [blue]adUseNone[/blue]
Cn.Open
...

And then to create some recordsets:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
    .CursorType = [blue]adOpenForwardOnly[/blue]
    .CursorLocation = [blue]adUseClient[/blue]
    .LockType = [blue]adLockReadOnly[/blue]
    .Open strSQL, Cn
    If .RecordCount > 0 Then
        For i = 1 to .RecordCount[green]
             'whatever I need to do[/green]
            .MoveNext
        Next i  
    End If
    .Close
End With
Set rst = Nothing

Where strSQL is my Select statement(s).

For Insert, Delete, or Update I use:

Cn.Execute strSQL

Now, do I use all settings in [blue]BLUE[/blue] correct?
I think some Connection settings set also the recordset settings (is that right?) so maybe I don't need to do some of this (blue) stuff...?

Have fun.

---- Andy

 

[Bluejay07]

Hi Andy,

When opening the recordset, you can set the locktype and the cursor type all on the same line.

.open strSQL, Cn, adOpenForwardOnly, adLockReadOnly

If at first you don't succeed, then sky diving wasn't meant for you!

 

[dilettante]

This:

Cn.CursorLocation = adUseNone

Has been obsolete forever basically. It acts as a no-op and leaves the default of adUseServer. Since Insert, Delete, Update don't return Recordsets it makes no difference to you for those operations anyway.

If you want client cursor services from ADO then you need to set the CursorLocation appropriately. This makes a number of non-trivial differences in how the Recordset operates.

Setting CursorLocation at the Connection level only impacts Recordsets returned by Connection.Execute calls.

This stuff is all documented in the ADO section of the MSDN Library.

 

[Andrzejek]

Thanks dilettante,
I did read about it on MSDN before I posted this question, but it was not very clear (to me)

I changed my Connection's CursorLocation to adUseClient:

Public Cn As ADODB.Connection

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Some connect string here"
Cn.CursorLocation = [red]adUseClient[/red]
Cn.Open
...

So I don't have to specify recordset's CursorLocation, it takes it from Connection's CursorLocation:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst[green]
    '.CursorType = adOpenForwardOnly
    '.CursorLocation = adUseClient
    '.LockType = adLockReadOnly[/green]
    .Open strSQL, Cn, adOpenForwardOnly, adLockReadOnly
    If .RecordCount > 0 Then
        For i = 1 to .RecordCount[green]
             'whatever I need to do[/green]
            .MoveNext
        Next i  
    End If
    .Close
End With
Set rst = Nothing

I hope that's right....

Have fun.

---- Andy

 

[dilettante]

That trick only works for Recordsets returned by Connection.Execute calls.

Are you sure you even want client cursors?

 

[Andrzejek]

I think so.
If I use Server side cursor, my RecordCount is -1 when recordset is open. I would have to .MoveLast to see the record count which would make a lot of code modification, and I don't want that.

So what is the down side of Client cursor (vs Server side)?

Have fun.

---- Andy

 

[dilettante]

There are advantages and disadvantages to both.

You might read through The Significance of Cursor Location for a brief summary.

 

[Andrzejek]

Thank you for the link.

I think I will stick with Client side cursor since the record sets I use are (relatively) very small: a few fields with (the most) a few hundreds of records, a lot of times just one record, sometimes a little bigger set of data that I can filter on. Plus, since all of my users use the application that resides on Citrix server(s), I don’t think there is much difference if I use (Oracle’s) server or (Citrix’s) server - which is my Client’s side - to do my magic.

Have fun.

---- Andy

 

[dpaulson]

I don't think there is much of a downside to a client side cursor when the network is local, but if the database is assessed across the internet, your upload speed is a big downside and can make your app pretty much useless.

David Paulson