Solved

ADO AddNew on an empty recordset with Access 2002 db

Posted on 2004-04-06
9
918 Views
Last Modified: 2007-12-19
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
            .MoveFirst
            .Find "SecurityID='" & DataValue$(3) & "'"
        End If
         If .EOF Then 'NOT found
         'Add a share
            .AddNew
            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"
            .Update
        Else
            !LatestPrice = Val(DataValue$(4))
            !PriceDate = Format$(Now, "dd/mm/yy")
            .Update
        End If
        End With
0
Comment
Question by:henrymarriott
  • 4
  • 4
9 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 10768755
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 required...at least I think isn't not.

how many records are you trying to update?

Mike
0
 

Author Comment

by:henrymarriott
ID: 10769572
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.
0
 

Author Comment

by:henrymarriott
ID: 10813734
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!!
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10816610
I'm glad you got the problem solved. Are there any remaining issues?

Mike
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:henrymarriott
ID: 10821863
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!

Regards
henry

 

 
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10835208
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.

Thanks,
Mike
0
 

Author Comment

by:henrymarriott
ID: 10860993
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
Henry
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10861066
Sounds like a plan.  Don't forget to close out this thread.

Mike
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 10863732
Closed, 250 points refunded.
Lunchy
Friendly Neighbourhood Community Support Admin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now