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
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