Loading and Saving record?

[Mike]

What is the best way to load and save a record?

I have a remote SQL Server table with thousands of "Lead" records. I want to
have a form which allows users to view leads and modify the information in
them.

My normal approach is to have a local MS Access table which stores either
zero or one record which is bound to the "Lead" form. When the user opens a
record in their form, the record they are wanting is copied from the remote
SQL Server table to the local access table. They then edit the contents of
the local table via the form, then if/when they decide to save the record an
update query updates the remote table with the contents of the local table.

This actually works very well but can be awkward to implement. It's very
safe as the user only writes to the remote table on save. It also means a
user can easily undo any changes they have made before saving as all work is
done locally.

What alternative techniques are there that allows users to safely load and
save records?

P.S. I have tried binding the remote table directly to the Lead form, but
that seems too prone to writing to the table (e.g. when the form is closed
it writes the changes to the table).

 

[Wayne Morgan]

Create a linked table to the table on the SQL Server or to a stored
procedure on the server. Use that linked table for the form.

You are correct, if there have been changes made to the record and the form
is closed, it will save those changes. It will also save those changes when
you move to another record. If you want the user prompted first, you could
use the form's BeforeUpdate event to prompt them if they want to save the
changes. If they respond No, then Cancel the update and Undo the form to get
rid of the changes.