[RESOLVED] ADO recordset updating on it's own?

[drag0n_45]

Hello all,
I'm using ADO with an Access database using the Jet engine. To the best of my knowledge, when you change a field in the recordset, the field doesn't update in the database until you call the .Update method. The opposite seems to be happening though, when I change a field in the recordset, the field immediately updates in the database instead of waiting until I call the update method.

This is a bit irritating, as it's much easier to change the field in a database and call the update method when the save button is clicked rather than updating all the fields when the save button is clicked. It's also easier to validate: simply check if the recordset is in the edit status and if so, update it.

Does anyone know why that problem is happening? I find that it's only happening on one of my forms, so I must be doing something right somewhere, but I can't seem to find the difference between the forms that wait until Update is called to update the recordset and those that don't.

[sparbag]

Hello Dragon,

Please post the code for the form. I suspect there is a slight discrepancy within this form that others forms do not have. Time for some new eyes.

Kind regards

Steve

[drag0n_45]

Indeed. The code for the forms is huge - it'd be a lot to read through. Although I think I may have discovered the problem, solvable by one question: Does ADO automatically update its recordset when you change fields unless you call the Cancel method first?


If the answer to that is no, then I'll post the code. I'm suspecting that may be that cause. I've already re-written this form's code about 2 times, and I'm trying again just for ha-ha's, this time keeping the same structure as the other form. I was changing the structure slightly in a means to optimize the code.

[GaryMazzone]

The answer is no. How is the recordset declared?

[si_the_geek]

Does ADO automatically update its recordset when you change fields unless you call the Cancel method first?

Are you sure you meant change fields?

If you meant change record, I'm not sure of the answer - but it is definitely possible (presumably depends on the driver/provider you are using), and doing that should be avoided.

[dee-u]

Posting the code would help us look deeper into the problem. Or are you using an adodc?

[brucevde]

ADO automatically calls the Update method if you "move" from the current record being edited or added without calling CancelUpdate.

[dee-u]

ADO automatically calls the Update method if you "move" from the current record being edited or added without calling CancelUpdate.

Yes, that is specially true in bound controls, even while you are updating then just closed the form the changes will still be reflected.

[techgnome]

1) Which is why I hated bound controls in VB6/ADO
2) Wouldn't happen if the recordset was disconnected. I wouldn't be surprised if the connection is open all this time. I also wouldn't be surprised to find an ADODC at the heart of all this.

-tg

[drag0n_45]

No to the data controls - that's actually the purpose of this question: data controls were being used and I am trying to re code everything in raw code.

I wouldn't be surprised if the connection is open all this time.

The connection actually is open all the time. I can't supply the code just yet as i'm still working on re-writing it, but I can give a brief overview of how i'm managing the recordset.

When the form loads, I create the connection to the database and iterate through each record to populate a users list. In each loop, I add the username to a listbox and save the record's bookmark in an array with the array index matching the listbox's listindex.
When the user clicks the listbox, I move the record imdicated in the aforementioned array, and update textboxes with the username and password from the current record.
When the user modifies a textbox, I change the apropriate field im the recordset. When the user clicks the save button, I just call the update method., which makes this sub easier for adding records as well. If the user attempts to change the recordset before Update is called, the program SHOULD display an error and prompt the user to save, but it was just saving the data without the update wethod even being called. There's much more to the form than this, but a lot of it is cosmetic, buttons being enabled or disabled etc.

I'm new to the whole ADO through code idea, but I'm a whole-hearted proponent from the little that I do have experience with. That being said...

Wouldn't happen if the recordset was disconnected

That is true, but how does disconnecting a recordset affect the changes to the recordset? Are they simply updated when you connect? Do you have to call the update method? What abot multiple changes? Is diconnecting the recordset convention? What about leaving the connection open for the entire length of the project? That's what I am doing currently and just using the connnection to open multiple records within the project.

ADO automatically calls the Update method if you "move" from the current record being edited or added without calling CancelUpdate.

Does this happen even in code? I think this might explain why this problem occurs....

The answer is no. How is the recordset declared?

Dim WithEvents vm_Users_rs As ADODB.Recordset

In form_load:

Set vm_Users_rs = New ADODB.Recordset
'Ensure that we can use bookmarks
vm_Users_rs.CursorLocation = adUseClient
'Open the new recordset
vm_Users_rs.Open "Users", vp_DBConn_db, adOpenDynamic, adLockOptimistic

[techgnome]

but how does disconnecting a recordset affect the changes to the recordset?

The changes are applied to the recordset, and they stay there.

Are they simply updated when you connect? Do you have to call the update method? What abot multiple changes?

In order, Yes and no, and yes... and yes you can do that.

Here's what I used to do:
Get a recrodset.
Disconnect it.
Close connection
bind to the grid, allow the user to edit.
Upon save from the user
re-setablish connection
set conenction of recordset to the newly opened connection
call .UpdateBatch to store all the changes to the database
re-get the recordset's data (this is to grab new IDs and such
disconnect, repeat

Is diconnecting the recordset convention? What about leaving the connection open for the entire length of the project?

Yes, disconnected recordsets is the norm and the generally accepted conventional wisom. This is to prevent locking the records so that other people can see the same data. Also, Access has issues when used in a multiple user app.

That's what I am doing currently and just using the connnection to open multiple records within the project.

And if it's a small app and only one person is ever in it at a time, this is probably OK, but it can lead to problems - what if the connection goes down for what ever reason? Next time you go to use it, you would have to open it anyways... if the dropped connection didn't cause an error in the first place.

Does this happen even in code? I think this might explain why this problem occurs....

You betcha... that's why I mentioned it. Most people don't realize that this happens... accidentally change the data, then try to quit the app or move to another record, and then wonder whythe data saved.

Dim WithEvents vm_Users_rs As ADODB.Recordset

In form_load:

Set vm_Users_rs = New ADODB.Recordset
'Ensure that we can use bookmarks
vm_Users_rs.CursorLocation = adUseClient
'Open the new recordset
vm_Users_rs.Open "Users", vp_DBConn_db, adOpenDynamic, adLockOptimistic

Unless there is a VERY Specific REASON to do so, do not declare your recordsets withwevents... jsut declare them. It's not often that you'll need to handle the events of one.

--tg

[drag0n_45]

Get a recrodset.
Disconnect it.
Close connection
bind to the grid, allow the user to edit.
Upon save from the user
re-setablish connection
set conenction of recordset to the newly opened connection
call .UpdateBatch to store all the changes to the database
re-get the recordset's data (this is to grab new IDs and such
disconnect, repeat

So then, would I need to make any special changes to the way I open the recordset in order to use Updat.Batch? Or will the lock types I specified work?
Also.... you can bind grids\controls directly to recordsets?!?! I thought you could only do that with data controls?

So, for clarity's sake, your strategy makes the save event purge all changes to the database rather than simply updating the bound recordset?

Unless there is a VERY Specific REASON to do so, do not declare your recordsets withwevents... jsut declare them. It's not often that you'll need to handle the events of one.

I use the recordset events to, mostly, determine when a record changes in a recordset and prompt the user to save the changes rather than having to call another sub in every method that changes the recordset location.


Also, by convention (and possibly simplicity) should I modify the recordset when I change a control or only when the userclicks the save button? In either case good validation could be used. If I were to update the recordset when a field was changed, I could check the editmode property and confirm the changes with the user (a passive user approach). If I were to update the recordset fields when the user clicks a save button, I could still compare the contents of the recordset with the data on the form before the recordset changes and prompt the user to save. Is this type of thing a conventional standard or a programmer's preference?

[techgnome]

binding - grids: yes... controls: no

Nothing is purged... the update batch tells ADO to take the entire recordset and send it to the database. Update only works on the current row. That's the difference.

locktypes - I don't know, but I think they are right.

events - I wondered about that after I posted. With disconnected recordsets, I'd simply set a dirty flag.... then enable/disable a save button based on that flag, and when the form closes, check the dirty flag, and if it's set, then prompt to save....

But now that you've mentioned it, if the user can only work with one record at a time, it might work better to only bring back one record... the one being edited.... then when they move, save the changes, get the next or previous record... meh... depends on how the app is set up and what you want to do with it exactly.

-tg

[drag0n_45]

OK! Managed to convert all of my code to this method (finally), and I must say I love how easily it is...but, I can't get my disconnected recordset to resync with the database. I have a Apply Changes button that is supposed to initialize a connection with the db and flush all changes, then close the window. Code as follows:

'Open the connection
    OpenDBConnection
    'Reconnect the recodset
    ReconnectRecordset vm_Users_rs
    'Flush all changes
    vm_Users_rs.UpdateBatch adAffectAllChapters
    'Refresh the active user list
    vp_UAC_cls.RefreshDBInfo
    'Disconnect the recordset
    DisconnectRecordset vm_Users_rs
    'Close the database connection
    CloseDBConnection
    'Unload the form
    Unload Me

The subs are below:....

OpenDBRecordset:

Public Sub OpenDBConnection()
If notUS Then
    vp_DBName_str = "hpfilesNL.mdb"
Else
    vp_DBName_str = "hpfiles.mdb"
End If
 
'Set vp_DB_db = DBEngine.Workspaces(0).OpenDatabase(vp_DBName_str)
'^^From the days of DAO...
 
 
Set vp_DBConn_db = New ADODB.Connection
vp_DBConn_db.Open (VP_CONN_STR & vp_DBName_str)
    
End Sub

ReconnectRecordset:

Public Sub ReconnectRecordset(pw_Recordset_rst As ADODB.Recordset)
If vp_DBConn_db Is Nothing Then
    OpenDBConnection
End If
 
Set pw_Recordset_rst.ActiveConnection = vp_DBConn_db
End Sub

DisconnectRecordset:

Public Sub DisconnectRecordset(pw_Recordset_rst As ADODB.Recordset)
pw_Recordset_rst.ActiveConnection = Nothing
End Sub

CloseDBConnection:

Public Sub CloseDBConnection()
If Not vp_DBConn_db Is Nothing Then
    vp_DBConn_db.Close
    Set vp_DBConn_db = Nothing
End If
End Sub

Am I forgetting something? Recordset cursor is set to client.

vp_UAC_cls is a class a made for user access control to various components of my program.

[techgnome]

so what does happen? An error? Or what?

-tg

[drag0n_45]

Nothing. Literally. The form closes and the data isn't saved. Although I did figure the problem out not two minutes after making this post. I was opening the recordset with an Optimistic LockType, not BatchOptimistic.