Updating a record in access with vb6

Posted 05 April 2013 - 03:33 AM

i am trying to edit a record that i just saved into the databasebut the code I have been using has been giving me some errors " run time error '3251': current recordset does not support updating. This maybe a limitation of the provider,or of the selected locktype "

Re: Updating a record in access with vb6

This is only comparing the field of the first record in the recordset, or the row that the recordset is currently on (having just opened the recordset).

You need to:
Use rs.MoveFirst so that you start searching from the first row;
Use rs.Find to find the reference you are looking for;
Check whether Find was successful in locating the row;
If it was successful, edit and Update the row;
If it didn't find the row, then add a new row (if this is what you want to do).

Re: Updating a record in access with vb6

Posted 05 April 2013 - 05:42 AM

I shall just mention that if you are searching an Index (perhaps the primary key) then it is possible to use Seek, rather than Find, which would be much faster. But there are a number of conditions to fulfil before this will work properly, so I suggest that you continue with Find for the moment.

Re: Updating a record in access with vb6

Posted 06 April 2013 - 09:25 PM

The default cursortype is adOpenForwardOnly. This is a so-called "firehose cursor" which supports only MoveNext and MoveFirst. No updating, no MovePrevious, etc. It's used to iterate a recordset, for example to fill a listbox with values. A dynamic cursor is not only updatable, it updates itself with all the additions and changes of other users. This is the most expensive cursor performance-wise. Another choice is the keyset cursor, which is also updatable, will show changes of other users, and will not show additions of other users. The last choice is to use disconnected recordsets (which is the default behavior in .Net) and batch updating, which is the cheapest updatable recordset in terms of performance, but also the most complicated to work with in multiuser situations. Merging update conflicts becomes nontrivial when you are updating in batches.

An important point about the Seek method is that it only works with "native JET databases" meaning Access and nothing else. Obviously, this can complicate a back end migration from Access to SQL Server or Oracle, so it becomes a potential scalability risk.

There is a way to dynamically index a recordset to improve the performance of the Find and Sort methods. (Check the "Optimize" property.) To do so, you have to use a Client-side cursor, though, and they are not directly updatable. The way to overcome this is to use SQL statements or stored procedures with ADO command objects to update your database, and simply run another query (close and reopen the recordset) whenever you want to update your local recordset with your changes. This means that your recordset is "dirty" between requeries if you don't do it every time you change a record, and doing that can cause unacceptable performance overhead if you are working with a lot of records. You can overcome this by using an array to keep track of changes not yet posted to your local recordset and only do a requery when the array gets to a certain size.

Also, the Filter method works better for me in this context than the Find method. Filter doesn't actually alter contents of the recordset, it just changes the "logical size" of the recordset by reducing the number of records that can be viewed.

I've found I get great performance for low overhead by using a combination of select queries, filters, and update queries to get what I want.

Three guesses which line errors out with "Rowset does not support fetching backward"? />

I don't mean to embarrass you, Andrew, but you should probably check your answers more carefully before you make them, in particular with ADO since this isn't the first howler you've made in your ADO answers. (Last one had to do with a less-than-complete understanding of how cursortype and cursorlocation interact, as I recall.) You have an impressive range of expertise and people listen to you. If you're really trying to help them, you have a responsibility to see that you're not giving them wrong information. Especially when you contradict someone else who is trying to help too.

Three guesses which line errors out with "Rowset does not support fetching backward"?

I don't mean to embarrass you, Andrew, but you should probably check your answers more carefully before you make them, in particular with ADO since this isn't the first howler you've made in your ADO answers. (Last one had to do with a less-than-complete understanding of how cursortype and cursorlocation interact, as I recall.) You have an impressive range of expertise and people listen to you. If you're really trying to help them, you have a responsibility to see that you're not giving them wrong information. Especially when you contradict someone else who is trying to help too.

Fair enough?

Wow, sounds very much like you've been saving this up. I apologise for the "mistake" but object to the term "howlers".

Are you saying that you check everything that you post and don't make mistakes?

Feel free to correct me if I make any mistakes, but I will continue to post if I feel I have something useful to offer to the OP.

Re: Updating a record in access with vb6

Posted 07 April 2013 - 06:58 PM

As you will. Here's my view: since this is the second time that you have given out incorrect information like this, I felt it was my responsibility as a forum leader to say something. I can tell you that unless I'm quite sure that I know the answer, yes, I do check it. That is not to say that I'm never wrong. Sometimes I'm sure I know the answer and I'm wrong, too. Sometimes I research the answer and I come to a wrong conclusion. But I would say that I'm more careful than you are showing yourself to be. For example, my "howler" that you corrected (which didn't offend me at all, by the way) when I thought an OP was using VB6 and he was using Excel I only posted after spending some time looking for the method in the VB6 documentation and not finding it. Clearly, you didn't do anything similar here. And I always check before correcting someone else's post, yours included.

If you take responsibility to help someone, then to steer them wrong fails to meet that responsibility. I do it too, but it bugs me when I do. It isn't useful to an OP to give him wrong information, after all. Since you could improve the quality of the help you give if you go to a little more trouble to verify your answers, I would think that you would consider it your responsibility to do so, given that you want to be as helpful as you are able.

As for the term howler, I didn't say it to give offense. I would have characterized it a howler (defintion of howler: a glaring mistake) if I had done it myself, so I'm simply applying the golden rule.

All that said, I'm glad that you're here, and I think you're an asset to our forum. I've looked at several of your posts in other forums, too, and I'm impressed with the breadth of your skills. You're a lot better than I am at some things (although ADO isn't one of them ). Please take what I have said as positive criticism (indeed, I intend it that way) and be a little more careful with your answers in future. We'll all benefit.

Re: Updating a record in access with vb6

Posted 08 April 2013 - 06:31 PM

Thanks Andy. If it's at all helpful, you may wish to have a look at Nickel Tour of ADO's different cursor types. It's ten years old, and some changes have been made with later versions of ADO (notably the Keyset CursorType doesn't need you to do a MoveLast before you get an accurate record count anymore), but it lays out the different types pretty well.