RecordCount has non-zero value immediately after Open!

ForumExcel

New Member
Joined
Jan 17, 2017
Messages
21
Excel 2010 on Windows 7 talking to Oracle database:

I am trying to upload data from Excel to Oracle tables. I'm working with 10 rows in Excel in this example. I open a recordset and populate it from an array (not shown here). Sometimes this code works fine when number of records, immediately after "open" is 0. But other times, this value is 10, probably from a previous run, when it ends up in error because my code adds 10 more of same records resulting in duplicates and causing primary-key violations.

I'm surprised that the starting count, immediately after "Open" can be non-zero. I am hoping for help to understand why this can be so and how to resolve this issue. I am closing and setting rst to Nothing after my routine is done.

Thank you

Code:
    Dim rst As Object
    Set rst = CreateObject("ADODB.Recordset")
  
    With rst
      Set .ActiveConnection = con
      .Source = "SELECT * FROM " & table
      .CursorLocation = 3         ' adUseClient
      .LockType = 4               ' adLockBatchOptimistic
      .CursorType = 0             ' adOpenForwardOnly
      .Open
  
      Debug.Print "recordCount immediately after opening"
      Debug.Print "number of records in recordset = " & rst.RecordCount
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,711
If you want a record count, you need to be using a different cursor type, something other than forwardonly.

Why would you be surprised if you have records? You're selecting everything that's in the table
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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
Top