12 Replies - 5881 Views - Last Post: 09 April 2013 - 02:00 AM Rate Topic: -----

#1 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

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 "

I am using the code below for my update:


 Set rs = New ADODB.Recordset
 rs.Open "Access", Con, adOpenForwardOnly, , adCmdTable

 If Not txtUserNumber.Text = rs.Fields("reference") Then
 rs.AddNew 'adding new record
 Else
 rs.Find txtUserNumber.Text = rs.Fields("reference")
 End If
 rs.Fields("reference") = txtUserNumber.Text 'Saves the customer ID to the database
 rs.Fields("user_name") = txtUserName.Text  'The customer's prefix
 rs.Fields("level") = txtUserLevel.Text  'The email address the customer uses for instant messaging
 rs.Fields("Description") = txtDescription.Text  'Other details associated with the customers instant messaging
 rs.Update 'Handle the data




I used the code below to save the record and it works fine


Private Sub btnAdd_Click()

 Dim strCriteria As String
    Dim lngCurRecord As Long

    'Trap the user's search entry.
    strCriteria = "reference = " & "'" & txtUserNumber.Text & "'"

    'Open the Northwind Customers table.
    'Con.Open "Simply Basic Weighbridge Control System"
    Set rs = New ADODB.Recordset
    rs.Open "Access", Con, adOpenForwardOnly, , adCmdTable

    'Find the first record matching the criteria.
    rs.Find strCriteria, 1, adSearchForward

    'Provide a response to the user.
    If rs.EOF Then
        
              
     Set rs = Nothing
    Else
    MsgBox "User Number already exists"
    Exit Sub
End If


If txtUserName.Text = "" Or txtUserLevel.Text = "" Or txtDescription.Text = "" Then
    MsgBox "Some fields are still empty!", vbExclamation, "Input Error"
Else
 Set rs = New ADODB.Recordset
 rs.Open "Access", Con, adOpenDynamic, adLockOptimistic, adCmdTable
rs.AddNew
rs("reference") = txtUserNumber.Text
rs("user_name") = txtUserName.Text
rs("level") = txtUserLevel.Text
rs("Description") = txtDescription.Text
'rs("LastName") = txtLn.Text
rs.Update
MsgBox "Record Added Successfusly!", vbInformation, "Add Record"
Call CLEAR
End If

End Sub





What could be wrong with my update code?

Is This A Good Question/Topic? 0
  • +

Replies To: Updating a record in access with vb6

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,541
  • Joined: 12-December 12

Re: Updating a record in access with vb6

Posted 05 April 2013 - 04:02 AM

Do you need a forward only cursor in the first example? Without specifying a lock-type it defaults to adLockReadOnly.

rs.Open "Access", Con, adOpenDynamic, adLockOptimistic, adCmdTable

(as in your second code example)

BTW Googling your error message returns a lot of useful results..
Was This Post Helpful? 1
  • +
  • -

#3 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: Updating a record in access with vb6

Posted 05 April 2013 - 05:09 AM

Okay thanks to your post at least I see some changes in the database I replaced this
 rs.Open "Access", Con, adOpenForwardOnly, , adCmdTable


with this
rs.Open "Access", Con, adOpenDynamic, adLockOptimistic, adCmdTable


And the code now updates the database, but however it is updating the first record in the database and not the record I want to deal with.

this is the whole update code:
Set rs = New ADODB.Recordset
 'rs.Open "Access", Con, adOpenForwardOnly, , adCmdTable

 rs.Open "Access", Con, adOpenDynamic, adLockOptimistic, adCmdTable

 If Not txtUserNumber.Text = rs.Fields("reference") Then
 'rs.AddNew 'adding new record
 
 rs.Fields("user_name") = txtUserName.Text  'The customer's prefix
 rs.Fields("level") = txtUserLevel.Text  'The email address the customer uses for instant messaging
 rs.Fields("Description") = txtDescription.Text  'Other details associated with the customers instant messaging
 rs.Update 'Handle the data
 Else
 'rs.Find txtUserNumber.Text = rs.Fields("reference")
 'MsgBox ("HELLO")
 End If
 'rs.Fields("reference") = txtUserNumber.Text 'Saves the customer ID to the database


Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,541
  • Joined: 12-December 12

Re: Updating a record in access with vb6

Posted 05 April 2013 - 05:32 AM

If Not txtUserNumber.Text = rs.Fields("reference") Then

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).
Was This Post Helpful? 1
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,541
  • Joined: 12-December 12

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.

http://msdn.microsof...9(v=vs.85).aspx
Was This Post Helpful? 1
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

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.

This post has been edited by BobRodes: 06 April 2013 - 09:30 PM

Was This Post Helpful? 1
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,541
  • Joined: 12-December 12

Re: Updating a record in access with vb6

Posted 07 April 2013 - 03:43 AM

View PostBobRodes, on 07 April 2013 - 04:25 AM, said:

The default cursortype is adOpenForwardOnly. This is a so-called "firehose cursor" which supports only MoveNext and MoveFirst. No updating, no MovePrevious, etc.

Thank you for the clarification @Bob. Although, that should be MoveFirst MoveLast :)
Was This Post Helpful? 1
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Updating a record in access with vb6

Posted 07 April 2013 - 08:37 AM

View Postandrewsw, on 07 April 2013 - 05:43 AM, said:

View PostBobRodes, on 07 April 2013 - 04:25 AM, said:

The default cursortype is adOpenForwardOnly. This is a so-called "firehose cursor" which supports only MoveNext and MoveFirst. No updating, no MovePrevious, etc.

Thank you for the clarification @Bob. Although, that should be MoveFirst MoveLast :)/>/>

Well, I'm sorry, but no it shouldn't, as you would know if you had tested your answer before you made it:
Private Sub Command1_Click()
Dim cn As Connection
Dim rs As Recordset
Set cn = New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\nwind.mdb"
Set rs = New Recordset
rs.Open "select * from customers", cn
rs.MoveNext
rs.MoveFirst
rs.MoveLast
End Sub

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?

This post has been edited by BobRodes: 07 April 2013 - 08:46 AM

Was This Post Helpful? 1
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,541
  • Joined: 12-December 12

Re: Updating a record in access with vb6

Posted 07 April 2013 - 10:38 AM

View PostBobRodes, on 07 April 2013 - 03:37 PM, said:

View Postandrewsw, on 07 April 2013 - 05:43 AM, said:

View PostBobRodes, on 07 April 2013 - 04:25 AM, said:

The default cursortype is adOpenForwardOnly. This is a so-called "firehose cursor" which supports only MoveNext and MoveFirst. No updating, no MovePrevious, etc.

Thank you for the clarification @Bob. Although, that should be MoveFirst MoveLast :)

Well, I'm sorry, but no it shouldn't, as you would know if you had tested your answer before you made it:
Private Sub Command1_Click()
Dim cn As Connection
Dim rs As Recordset
Set cn = New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\nwind.mdb"
Set rs = New Recordset
rs.Open "select * from customers", cn
rs.MoveNext
rs.MoveFirst
rs.MoveLast
End Sub

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.
Was This Post Helpful? 1
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

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.

This post has been edited by BobRodes: 07 April 2013 - 07:03 PM

Was This Post Helpful? 1
  • +
  • -

#11 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,541
  • Joined: 12-December 12

Re: Updating a record in access with vb6

Posted 08 April 2013 - 04:46 AM

@Bob. Okay. I appreciate your good intentions and I am aware that I don't always check my answers as often, or as thoroughly, as I should.

Andy.

This post has been edited by andrewsw: 08 April 2013 - 05:07 AM

Was This Post Helpful? 1
  • +
  • -

#12 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

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.
Was This Post Helpful? 2
  • +
  • -

#13 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: Updating a record in access with vb6

Posted 09 April 2013 - 02:00 AM

Thanks so much BobRodes and andrewsw you guys are awesome!! Your posts helped me solve the challenge I was facing.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1