ADO Add New Not working for Final Record

[Dom]

I am importing an Excel Spreadsheet into a temporary table and from there
moving the data between a number of different tables - this is to help with
managing space.
I'm using ADO and the AddNew method but the problem I'm having is that it is
consistently missing the final records. The size of the recordset could be
anything from 1,500 to 2,500 records.

An example of the code I'm using is below -

With cnCopy
.Mode = adModeShareDenyNone
.ConnectionString = CNSTRING
.CursorLocation = adUseServer
.Open
End With

strSQL built here

With rsCopy
.ActiveConnection = cnCopy
.Source = strSQL
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Open
End With

cnCopy.BeginTrans

Do Until rsCopy.EOF

More sql here
RsRule.Open strSQL, cnCopy etc etc

With RSRule!Rule
..AddNew
!AddItem = rsCopy!Item
End With

rsRule.Close
rsCopy.MoveNext

Loop

cnCopy.CommitTrans

The above works fine with an Update statement but I specifically want to
prevent the additions if the code encounters an error. If it does, the whole
transaction is rolled back.

From the code above, is there something I am fundamentally missing?

Thanks in advance
Dom

 

[Dirk Goldgar]

I am importing an Excel Spreadsheet into a temporary table and from
there moving the data between a number of different tables - this is
to help with managing space.
I'm using ADO and the AddNew method but the problem I'm having is
that it is consistently missing the final records. The size of the
recordset could be anything from 1,500 to 2,500 records.

An example of the code I'm using is below -

With cnCopy
.Mode = adModeShareDenyNone
.ConnectionString = CNSTRING
.CursorLocation = adUseServer
.Open
End With

strSQL built here

With rsCopy
.ActiveConnection = cnCopy
.Source = strSQL
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Open
End With

cnCopy.BeginTrans

Do Until rsCopy.EOF

More sql here
RsRule.Open strSQL, cnCopy etc etc

With RSRule!Rule
.AddNew
!AddItem = rsCopy!Item
End With

rsRule.Close
rsCopy.MoveNext

Loop

cnCopy.CommitTrans

The above works fine with an Update statement but I specifically want
to prevent the additions if the code encounters an error. If it
does, the whole transaction is rolled back.

From the code above, is there something I am fundamentally missing?

Thanks in advance
Dom

In this code:

With RSRule!Rule
.AddNew
!AddItem = rsCopy!Item
End With

you don't call the recordset's .Update method. It should be:

With RSRule!Rule
.AddNew
!AddItem = rsCopy!Item
.Update
End With

The ADO help file notes:
<quote>
If you call AddNew while editing the current record or while adding a
new record, ADO calls the Update method to save any changes and then
creates the new record.
</quote>

So for all records except the last one, the record is added anyway. But
for the last record, you close the recordset without calling any method
that would cause the record to be saved.

 

[Dom]

Dirk,
Thanks for that. Does that mean the the BeginTrans, CommitTrans and
Rollback become irrelevant or will they still work?

 

[Dirk Goldgar]

Dirk,
Thanks for that. Does that mean the the BeginTrans, CommitTrans and
Rollback become irrelevant or will they still work?

No, they're not irrelevant, and they should still work.

 

[Dom]

Dirk,
Thanks for your help - I've managed to get it working now.
Dom