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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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