Web and Access Data Changes

[jcruzAME]

I'm working with an Access program and a portal site that both make updates in an SQL database. If a user in the Access program is changing records in a table, and someone submits a record on the site, the site update will go through and reset whatever changes the user was making in the Access program. But, if the separate users are in the Access programming modifying records in the save table, their changes don't interfere with one another.

Is there a way to make sure the Access program submits the changes they make AS SOON AS they make them? Is there something I can do or code to prevent them from interfering with one another? Or a way to say this data is in use?

 

[pbaldy]

Like keystroke by keystroke, or field by field? I don't know that it's as simple as that anyway. Typically you'd be saving a record in its entirety. If we are 2 users start working on the same record, I change a couple of fields and they get saved and then you save your record, my changes are lost.

I use a method I think I got from the Microsoft Access Developer's Guide to SQL Server (excellent book by the way, though somewhat dated). It involves having a field in the table used as a control. When you update the record you increment that field. If when you try to save your changes that number is different, you aren't allowed to save, as it means someone updated the record between the time you retrieved it and the time you tried to save it.

I suppose you could just update individual fields, but I don't know your situation.

 

[jcruzAME]

That's awesome, that might help me troubleshoot to see if that is what's going on. So how does the checking of the number work? If the current record + 1 equals the users record + 1?

 

[pbaldy]

I should have added I use that method with unbound forms. Let's say when the record is created, it's given a default value of 1. You and I both grab the record, so we have that value. I update the record first, and as part of that process increment that field to 2. When you try to update, it fails because the value you have (1) doesn't match the value on the server (I use an update query with a WHERE clause that includes that field). I check whether the record was updated and pass a message to the user (I do it in SQL Server, so @@ROWCOUNT tells me how many records the query updated).