ADO RecordSet Properties Not Being Stored

MartinS

Active Member
Joined
Jun 17, 2003
Messages
489
Office Version
  1. 365
Platform
  1. Windows
OK, so after lots of searching for ADO recordset returns -1, I've tried all the suggestions, but each time I get to the line of code following the opening of the recordset, the cursortype option is not set to the requested value. My example uses an ADO command object to execute the stored procedure, but the logic works with the exception of being able to find the record count of the loaded data.
Here's the relevant code:
VBA Code:
Dim objConn         As ADODB.Connection
Dim objCmd          As ADODB.Command
Dim objRS           As ADODB.Recordset
Dim lngRecordCount  As Long
    'Creates the connection object
    Set objConn = OpenConn
    'Create command object
    Set objCmd = New ADODB.Command
    Set objCmd.ActiveConnection = objConn
    'Create the command to get the data and execute
    objCmd.CommandText = "EXECUTE [dbo].[usp_GetBulkConstruction]"
    objCmd.CommandType = adCmdText
    objCmd.Execute
    'Create and open recordset object
    Set objRS = New ADODB.Recordset
    objRS.Open objCmd, , adOpenKeyset, adLockReadOnly
But when I'm at the line following the Open, the Locals window shows different settings for the recordset object, i.e. cursor type is adOpenForwardOnly, even though it's set to adOpenKeyset:
RS.JPG
I've tried removing the command object and setting the first argument of the open recordset to be the "EXECUTE [dbo].[usp..." but get the same issue.
Anyone able to tell me what I'm missing or doing wrong?
Thanks in advance
Martin
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top