Multi-user DAO database updating

[Bob Howard]

Using Access 2003, what's a reasonable way, using DAO, to handle multi-user
database updating.

Currently, I have a bound form with several sub-forms ... and I want to
change the whole thing to a single unbound form and use DAO to read and
write the data. All data on the screen comes from a couple of related
tables.

There's a possibility that multiple users could access the same sets of
records and could be trying to update the data at the same time. With the
current bound form/subforms, the system seems to take care of things (at
least it gives a message when this situation it about to occur).

I would be happy if one user "held" the records and locked out other users
until they clicked a "Save" button I could put on the screen. Or I could
use any of several possible techniques.

The problem is that I don't have enough experience in this stuff to know
where to turn ... just to know how to design this and what methods to use in
implementation.

Can someone point me to a web site for information on how to handle this??

Thanks...

bob

[Tom van Stiphout]

On Mon, 1 Mar 2010 18:22:51 -0800, "Bob Howard" <in...@churchtrax.com>
wrote:

Why do you want to go unbound? That is rarely a good idea, especially
if you are not a strong developer.

The error handler should detect multiuser collisions, silently go to
sleep and retry a few times, and only if still failing ask the user
what to do:
'In a standard module:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'Your Save button's click event:
private sub SaveData_Click()
on error goto ErrHandler
dim retryCount as integer
'save your data here.
exit_handler:
exit sub
err_handler:
if err.number=123 then 'replace with actual error number
if retryCount < 10 then
Sleep 50 * retryCount
retryCount=retryCount+1
resume 'try the operation again.
else
if msgbox("Try some more?", vbYesNo or vbQuestion)=vbYes then
retryCount=0
resume 'try the operation again
else
resume exit_handler
endif
endif
else
msgbox err.description
resume exit_handler
endif
end sub

-Tom.
Microsoft Access MVP

[David W. Fenton]

"Bob Howard" <in...@churchtrax.com> wrote in
news:uZCrL8au...@TK2MSFTNGP05.phx.gbl:

> Currently, I have a bound form with several sub-forms ... and I
> want to change the whole thing to a single unbound form and use
> DAO to read and write the data.

That is insane.

You are presenting a pre-ordained solution, instead of describing
the problem this solution was chosen to solve. Please explain what
problems you have and I'm sure we can suggest solutions that don't
involve such a drastic rewrite.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/