ADO AddNew on an empty recordset with Access 2002 db

Can anyone shed any light on this error for me. I have serached MS, google etc etc but can find no info.

runtime error -2147217836 (80040e54)
Number of rows with pending changes exceeded the limit

It happens on the first addnew and I have fiddled with various formats (not addnew with parms) but everything else. Its a pain and I'd be grateful for any help!

Code see below:

With rsSecuritiesDB
        If Not .EOF Then
            .Find "SecurityID='" & DataValue$(3) & "'"
        End If
         If .EOF Then 'NOT found
         'Add a share
            No_Shares = No_Shares + 1
            If No_Shares >= Sha_Max Then Call Check_Sha_Max
            !PriceDate = Format$(Now, "dd/mm/yy") 'DataValue$(1) 'Date
            !Description = DataValue$(2) 'Description
            !LatestPrice = Trim$(DataValue$(4)) & Space$(15 - Len trim$(DataValue$(4))))
            !SecurityID = DataValue$(3)  'Symbol
            '!Seq = DataValue$(5)   'Old ID
            '!Type = "S"
            !LatestPrice = Val(DataValue$(4))
            !PriceDate = Format$(Now, "dd/mm/yy")
        End If
        End With
Who is Participating?
LunchyConnect With a Mentor Commented:
Closed, 250 points refunded.
Friendly Neighbourhood Community Support Admin
I have found that using recordsets with the lockbatchoptimistic is a little easier.

Make all your changes and then issue the batchupdate method of the recordset.

Also, in ADO the .Update isn't least I think isn't not.

how many records are you trying to update?

henrymarriottAuthor Commented:
Thanks for that , Mike. I will give it a go. I have tried various conbinations and you are quite right - update is not necessary if one is using AddNew - the next AddNew triggers the update etc. The stupid thing about this is that it is happening on the first record that it tries to update which makes me siuspect that the err msg is probably a red herring. Its 10pm now so I will try the batch update tomorrow! Thanks for your help. Will let u know what happens.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

henrymarriottAuthor Commented:
Mike sorry to take so long to resolve this one. In fact I managed to fix the problem by putting in a .requery after the IF EOF (2nd one) and that seemed to solve it. The batch lock was obviously wrong though with the simple update but that did not fix the problem. I can only guess why it worked but if I added a record to the db via Access and then ran the vb code it worked. So I assumed it was something to do with an empty recordset hitting EOF or being closed - I must admit that I am finding ADO a great deal more pernickety than it sounds - good old Microsoft!!
I'm glad you got the problem solved. Are there any remaining issues?

henrymarriottAuthor Commented:
Plenty!! but I am getting thru them bit by bit. They are mainly issues of converting the client to a new way of thinking though! If you feel like working a bit harder for your points there is one small issue I do have with bound controls. If you can't face it just say so!

With a bound datalist control and a number of text boxes on a form   - say datalist1 and text1 to text5 - I should be able to bind the contents of the textboxes to the name selected in datalist1. I have copious examples of where the names in DataList1 come from one table and the values in the text boxes from another by setting boundcolumn etc. I cannot get it to work if ALL DATA comes from the same table!! Do I still need TWO ado data controls? Does my boundcolumn have to be a key field - presumably it does? Does DataList bind in exactly the same was as datacombo? I am trying to use no code at all and I reckon that by clicking on a name in datalist all the text boxes should update - am I wrong?
Settings are
Adodc1 works fine and points to a MEMBERS table
DataList1 displays the list of members names - no problem
DL settings are:
BoundCol = MemberID (Key)
Listfield = name
Datafield = name
Datasource and rowsource = adodc1
Textboxes initially display the details for the first member in the list when form is loaded. This never changes when I select a new name.
Settings are:
Datasource = adodc1
Datafield = name or initials or addr1 etc for each text box

If you can guide me thru that lot you will certainly earn your points!



Are you using VB against and Access Database?

If so, you should probably close out this one and then repost your question.  You will probably get lots of answers.  I'm a little swamped for the next couple of weeks.  I have twin boys on the way and my time is rather limited.

henrymarriottAuthor Commented:
OK, Mike, I will do that or I will have another go at solving it myself now that I have had a few days off - very best of luck with the twins - they won't give you much time off!!!

Best wishes
Sounds like a plan.  Don't forget to close out this thread.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.