?
Solved

ADO AddNew on an empty recordset with Access 2002 db

Posted on 2004-04-06
9
Medium Priority
?
934 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 18

Expert Comment

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

Mike
0
 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

864 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