March 22, 2010 at 5:42 pm
hello
i have been looking for a while now on the best practices on how to handle updates/deletes on an access form that is using ado disconnected recordsets ideally using sql sp's to do the updating/deleting but i cannot find anything that can outline the best way to handle this.
The form recordset is being set to an ado disconnected recordset correctly, the data resides on sql server 2008 express, currently using sp's to populate and handle any updates/deletes the problem i am having is that most of the examples here are using linked tables which i am not as the user does not want to.
Have a Save, Delete button on the form, the form load handles the populating of the form recordset, just would like to know what is the best way of taking a user change on the form and at what point do we fire off the sp and then repopulate the form recordset to have the change, also allowing the user to navigate to new record if possible, presume we clear the relevant field values here. Do i need to set the IsDirty flag etc to trigger relevant events.
If anyone can point me in the right direction be much appreciated.
thanks
March 24, 2010 at 4:04 pm
Hi Westy,
From a purely frontend design perspective, i'd suggest you can do away with the save button completely. Use the Form_Unload event to determine if the user has made any changes, and if they have, then prompt them to save the record or discard the changes.
Before I roll-out any application, I take a pile of the potential users' data and then sit in front of a PC inputting it, and then I almost always experience how painful the frontend I've written and thought was perfect will actually be to use in reality. You wouldn't believe how many changes I've discovered I needed to make by doing this, and prompting for a "save" on closing a form rather than clicking a save button has often been one of them.
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
March 24, 2010 at 4:22 pm
EDIT: I've just realised you're allowing them to navigate through records, so if this is the case, then the Form_Unload event will probably not be entirely applicable. I'd still say that even if you're allowing navigation through records on the form, you can avoid the Save button and prompt the user to save any changes when the record changes through events.
It may be different with your (or others) application(s), but this is just based on my experience in deciding to try to use the frontends I've wanted to force on people!
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
March 24, 2010 at 4:42 pm
hi Rainbow, appreciate the reply
after looking at this online for the last three days, finally went back to this website http://msdn.microsoft.com/en-us/library/bb188204(SQL.90).aspx -
Essentially the form is currently dao, with New, Save and Delete buttons plus the standard navigation.
I agree with you on the form_unload that we either prompt or automatically save the record.
At the moment, when the form opens i fire off a request to sql to return sp results, this then gets populate into adodb recordset with the fields on screen currently bound, make the recordset disconnected so far so good.
I was just looking at a recommended framework in which other dev's have handled updates of existing records, new records being entered and deletion of existing records using this way as i would have thought this would be quite common but the general feeling i have read is that they use linked tables.
What i envisage doing is a save/update, pass the values into an sp call to sql, sql comes back with success then call sql again to reload the form recordset so user can then see the new record.
Same with delete.
When doing New, clear all current fields on form, user then enters values, again the same process
this just seems very longwinded and i have not taken into account any blind updates that may happen as no locks are enforced, i am not sure whether i need to do this in the form frontend as in the sp's i have use begin tran, commit or rollback etc and looking at the last update takes precedence - whether i need to use a concurrency id as outlined in the website above from ms.
Just seems very difficult going from dao to using ado disconnected recordsets and there is not a lot of literature or help around to guide or offer pointers and this is a simple form with no subforms etc
cheers
March 24, 2010 at 6:31 pm
Hi Westy,
If you're going to use a disconnected recordsets with your forms then any relevant linked tables seem redundant to me as you'll be populating the forms via code. OK, linked tables with bound forms require very little code as MS Access handles a lot of the stuff for you, but disconnected recordsets have something of an appeal - apart from being potentially faster and more secure they always sound as if they're something Albert Camus would use if he was a programmer!
Off the top of my head, if you're dealing with a disconnected recordset that's holding an existing record that your user has made changes to, you'll want to know if the relevant record in SQL Server has been amended since you originally populated your recordset (i.e. your blind updates). If this is the case, you'll want to compare the original record with the current record in SQL Server to check for changes against it before committing an update. A number of MS Access controls have an "OldValue" property which might be applicable to disconnected recordsets as they're bound to the form and could come in useful in comparing the original record before changes and the current values it has in SQL Server. Depending on its size, holding the original recordset in a separate variable that you can use for comparison might also be an option, as instead of comparing the OldValue properties of the controls on the form, you could either loop through the recordset's fields or simply check if the original and current recordset objects are equal (it's late so I don't know if the latter would work but if it does it would make life easier).
As a caveat, I should say I haven't needed to use a disconnected recordset in a good few years, so take my comments with that in mind...
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply