Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO Recordset Cursor Types (VBA/Excel/2003)

    Hi,

    I have an Excel 2003 application that reads data from either a .mdb or from SQL Server, depending on the user's choice. If the user selects MS Access, then all data is read from or written to the .mdb. If they choose SQL Server, then all data is exchanged with the specified SQL Server.

    I am struggling with the ADO Recordset.RecordCount property.

    I know if I open a recordset with the wrong cursor type I will either get a -1 or I will need to do a .MoveLast before I get the true record count.

    My problem is I originally wrote the Excel app to only link to Access. Now that I need to cater for SQL server as well, I am struggling to get all the various "rst.RecordCount" instances to return the correct values.

    I have created a class module that creates the ADO connection to the relevant DB. I then have an OpenRecordSet method that I use to open whichever recordset I want. I simply pass a "SELECT * FROM tblTable" type string to the method and I get the recordset returned.

    In the class method, I have :

    <font color=448800>'To create the connection for an .mdb</font color=448800>

    <font color=red> Set cnn = New ADODB.Connection
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & strDB
    cnn.Open strCon</font color=red>

    <font color=448800>'To create the connection for an SQL Server</font color=448800>

    <font color=red> Set cnn = New ADODB.Connection
    strCon = "Provider=SQLOLEDB;Persist Security Info=False;User ID=" & strUID & ";pwd=" & strPWord & _
    ";Initial Catalog=" & strDBName & ";Data Source=" & strServer & ";"
    cnn.CursorLocation = adUseServer
    cnn.CommandTimeout = 120
    cnn.ConnectionTimeout = 120
    cnn.Open strCon</font color=red>

    When I open a recordset I use :

    <font color=red>Public Function OpenRecordset(strSQL As String) As ADODB.Recordset

    Dim rst As ADODB.Recordset

    On Error GoTo ErrorHandler

    If cnn.State = 0 Then GoTo ExitHandler

    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    Set OpenRecordset = rst

    ExitHandler:

    On Error Resume Next

    Exit Function

    ErrorHandler:

    . . . .

    End Function</font color=red>

    I am struggling to determine under what conditions the cursor type of the rst is adOpenForwardOnly, adOpenDynamic or adOpenKeyset. The latter is what I "asked for" and it is what I *want* to be able to use the .RecordCount property. I thought I had noticed different resultant cursor types in the context of the OpenRecordset method depending on whether my SQL string contained an ORDER BY clause or not. I have also noticed occasions where there is one cursor type in the context of the OpenRecordset method and then a different cursor type when the recordset is returned to the calling procedure.

    I have resorted to a clumsy do loop until .eof to count the records as a "robust" way of counting the records, but this seems messy.

    Any help to unravel the mysteries of resulting cursor types despite having asked for adOpenKeyset will be appreciated.

    Regards,

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Recordset Cursor Types (VBA/Excel/2003)

    replying to my own post - seemed easier than editing the original

    I may have stumbled on a solution, but I am still none-the-wiser . . .

    I tried a different provider for the SQL server connection :

    <font color=448800> 'ODBC Driver for SQL Server - Standard Security - Works</font color=448800>
    <font color=red> strCon = "Driver={SQL Server};Server=" & strServer & ";Database=" & strDBName & ";Uid=" & strUID & ";Pwd=" & strPWord</font color=red>

    I don't know if this provider is newer, older, better, etc., but I am getting recordsets opened with a cursor type of adOpenKeyset on a regular basis.

    Are this provider and the OLEDB one of my original post both installed as a standard option with MS Office ? I don't want to give my colleague this new app and find he doesn't have the relevant provider installed.

    Still looking for someone to help explain these mysteries please, thanks,

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO Recordset Cursor Types (VBA/Excel/2003)

    <P ID="edit" class=small>(Edited by jscher2000 on 31-Jan-07 14:43. )</P>The native OLEDB provider for SQL Server should give better performance than the multi-layer communications of the OLEDB provider for ODBC manipulating the ODBC driver for SQL Server. However, this assumes it does what you want it to do.

    As far as I know, you should be able to specify any cursor you want with the native provider. Perhaps some debug.print statements will help uncover the problem? Try inserting something like this after opening rst:

    <code>Debug.print "CursorLocation: " & Split("?|?|Server|Client","|")(rst.Cursorlocation) & _
    "; CursorType: " & Split("ForwardOnly|Keyset|Dynamic|Static","|")(rst .cursortype) & _
    "; Supports RecordCount: " & iif(rst.supports(adbookmark or adapproxposition), "True", "False")</code>

    That's roughed out, not tested, so apologies for any typos. Corrected that last constant...

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ADO Recordset Cursor Types (VBA/Excel/2003)

    Adrian,
    I believe that cursortypes are dependent not only on the provider but also on the cursorlocation property. You may want to have a look at this article for some information. I also have a table somewhere (I think in the Access Desktop developers Handbook) which shows the requested cursortypes and the actual returned ones for various situations - I will see if I can find it.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •