 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2005Rst.Update does not update
Hi there, Acc 97 SR2 I hope there is someone out there able to help me with my weird problem. I try to update a field in a table with continuous numbers. My problem is that the code that I run from a button in a form sometimes works, sometimes it does not. So, I press the button, and the table is update properly, then I press the button again and this time no calculation seems to take place and no figures are added to MyField". The number of attempts can vary until the result is no update. Sometimes I can run the code three times after the other and everything is fine, then the fourth time the table is not updated, sometimes it works only once, then pressing the button again and again brings no result. The code is as follows: Dim db As dao.Database Dim rs As dao.Recordset Dim intCounter As Integer Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT * FROM MyTable") intCounter = 0 rs.MoveLast rs.MoveFirst Do While Not rs.EOF rs.Edit intCounter = intCounter + 1 rs!MyField = intCounter rs.Update rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing Any input is highly appreciated! Thanks, Sabine Hi Sabine, this is a weird one. I cant see anything wrong with your code. One thing to check though. Maybe it is working but you are not realising it. You need to ensure that you close Mytable and reopen it after each attempt, to verify that you are looking at the latest data, otherwise your code my be working but you are not getting to see the result. If this isnt the case, try switching the following lines from > rs.Edit > intCounter = intCounter + 1 to > intCounter = intCounter + 1 > rs.Edit Its just a guess but this might work. You could also try recoding this in ADO. AJC > Hi there, > [quoted text clipped - 44 lines] > > Sabine Another thing, your statement > Set rs = db.OpenRecordset("SELECT * FROM MyTable") doesnt include a sort order. If you dont have a primary key defined this will open the table in random order. AJC > Hi there, > [quoted text clipped - 44 lines] > > Sabine Hi Andrew, Many thanks for your input. > Maybe it is working but you are not realising it. Well, I have checked "MyTable" by closing and reopening it each time I pressed the button, and "MyField" is definitely empty at times. I have also already tried switching the lines to > intCounter = intCounter + 1 > rs.edit but that also has got no effect. I have now taken the code out of the form and included it into a module, but the behaviour is still the same ... Sometimes "MyField" is updated, sometimes it is empty. > try recoding this in ADO I have to admit I wouldn't know how to do it at the moment ... > your statement > Set rs = db.OpenRecordset("SELECT * FROM MyTable") > doesnt include a sort order. If you dont have a primary > key defined this will open the table in random order. This is intended. I want the code to update the table from the first entered record to the last. Well, looking forward to any other ideas which may help to get my update problem solved. Thanks again, Sabine One more hint: I have also tried with adding a 'MsgBox intCounter' to see whether the code correctly counts the records, and according to the MsgBox everything should be fine. It tells me '1', '2', '3', etc., yet "MyField" is not updated, but empty ... Rgds, Sabine The code looks ok. As far as ADO goes, not in Access 97. Have you done a compact and repair of the file lately? What is the data type of MyField? Is there an Index on MyField? You say the field is sometimes left blank. Does it get changed from numbers in the previous run to blanks when you run the code a second time? Have you tried importing this into a new mdb file and trying from there? You indicate that you have SR-2 for Office, but do you also have Jet 3.51 SP3 installed?  SignatureWayne Morgan MS Access MVP > One more hint: > [quoted text clipped - 8 lines] > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! Hi Wayne, > The code looks ok. As far as ADO goes, not in Access 97. That was what I thought ... > Have you done a compact and repair of the file lately? Yes, today. > What is the data type of MyField? Integer. > Is there an Index on MyField? No. > You say the field is sometimes left blank. Does it get changed from numbers in the previous run to blanks when you run the code a second time? Yes, this is exactly what happens. > Have you tried importing this into a new mdb file and trying from there? Well, I more often import the whole mdb (135 MB) into a new one. Also did this at the beginning of this week, however, did not restrict this to the tables, queries, forms connected to this problem. Do you think it's worth a try ? > You indicate that you have SR-2 for Office, but do you also have Jet 3.51 SP3 installed? Mmhhh, don't know. If you tell me how to check it, I will have a look. Thanks! Sabine Find msjet35.dll in the Windows\System directory. This may be WinNT\System32 or Windows\System32 depending on your version of Windows. If your install is in a nonstandard directory, you'll have to adjust for that. Right click the file and choose properties. Click on the version tab. The version should be 3.51.3328.0 or higher. http://support.microsoft.com/default.aspx?scid=kb;en-us;172733 Try adding the line DoEvents after the Update line. It shouldn't matter, but won't hurt anything either. I'm just curious if it makes a difference. SignatureWayne Morgan MS Access MVP > Hi Wayne, > [quoted text clipped - 25 lines] > 3.51 SP3 installed? > Mmhhh, don't know. If you tell me how to check it, I will have a look. I have now updated the msjet35.dll to V 3.51.3328.0. Thanks for the link. I have then also added the line DoEvents after the rs.Update, but no success. B U T ! It looks as if I found a solution : After I pressed the button to run the code, I need to wait for 4 (!) seconds and when I then press the button again, everything works fine. If I am too quick, "MyField" is updated with blanks. So it's a timing issue (with regard to your suggested DoEvent it looks as if you already thought about this) and I have now to look for some code which deactivates the button for a few seconds. You don't have a snip of code for it by chance :-) ? Regards, Sabine > I have now updated the msjet35.dll to V 3.51.3328.0. > Thanks for the link. [quoted text clipped - 15 lines] > > Sabine You don't need the rs.movelast and rs.movefirst statements in the code from your original message. It could be that the code is executing the loop before the recordset is ready to accept the values. Try removing those two lines. SignatureBob Quintal PA is y I've altered my email address. > I have now updated the msjet35.dll to V 3.51.3328.0. > Thanks for the link. [quoted text clipped - 15 lines] > > Sabine Another thought. You show no error handler. if it's resume next, you will never see any message that might help to diagnose the problem. comment it out if presesnt. SignatureBob Quintal PA is y I've altered my email address. Hi Bob, Thanks for your input. As you can see from my latest reply to Wayne, the line of code DBEngine.Idle dbRefreshCache which he suggested to include solved my problem. However, I would be interested to know why I "don't need the rs.movelast and rs.movefirst statements in the code." Because, whereever one looks for examples, there is at least the "rs.movefirst" mentioned. Rgds, Sabine > Hi Bob, > [quoted text clipped - 15 lines] > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! When using a recordset, the .recordcount property is not guaranteed valid until the whole recordset has been read. if you were using a for-next loop to go through the records example: for iCtr = 1 to rs.recordcount !myfield = iCtr mext then before starting, a movelast would need doing to ensure that the loop had the correct end point. The .movefirst is required if you have done something with the recordset since you opened it, like a .movelast, a .findfirst or .findnext. They are not necessary if you use a while loop instead, on a newly opened recordset.In examples, I often put extra code to be on the safe side. the statements in question are probably remnants of example code. Sometimes good to have, but they slow execution down if used where not necessary. SignatureBob Quintal PA is y I've altered my email address. Thanks for the clarification, Bob. I have amended my code accordingly. Regards, Sabine Yes, that is what I was thinking with the DoEvents. Since 4 seconds seems to do it, let's try something else. There is a command to allow Access/Jet to write all pending items to disk. Try this in place of the DoEvents. DBEngine.Idle dbRefreshCache SignatureWayne Morgan MS Access MVP >I have now updated the msjet35.dll to V 3.51.3328.0. > Thanks for the link. [quoted text clipped - 18 lines] > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! B i n g o , Wayne, this did the trick - runs perfectly now ! Thanks a lot for your help - much appreciated! Regards, Sabine |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.