Append to new table vs Update one table

Jake F · External Since: Oct 13, 2009 Posts: 4

I'm having record locking issues and am not sure if putting the mde file on
everyone's computers will be the solution if they're all looking to one back
end file, or if I redo my database so that instead of the users changing one
table it appends information to a new table. I believe that would mean the
records will not be locked? Any advice?

Jake F · External Since: Oct 13, 2009 Posts: 4

Both the back end and front end files have a ldb file when being used, but is
it the front end ldb that is causing the record locks in which case having an
mde file on everyone's machine will fix the problem?

"Jake F" wrote:

> I'm having record locking issues and am not sure if putting the mde file on
> everyone's computers will be the solution if they're all looking to one back
> end file, or if I redo my database so that instead of the users changing one
> table it appends information to a new table. I believe that would mean the
> records will not be locked? Any advice?

S.Clark · External Since: Jan 26, 2009 Posts: 10

What kind of record locking issues?
How are the users locking the data? (i.e. from a form, directly in table, etc)
Distributing the front end is always the right answer.

"Jake F" wrote:

> I'm having record locking issues and am not sure if putting the mde file on
> everyone's computers will be the solution if they're all looking to one back
> end file, or if I redo my database so that instead of the users changing one
> table it appends information to a new table. I believe that would mean the
> records will not be locked? Any advice?

Jake F · External Since: Oct 13, 2009 Posts: 4

The users are editing a single field in a table through a form. Would it
also help if I used a query to populate the form instead of going directly to
the table or that wouldn't matter? Still, each user having a copy of the mde
file on their machine is the way to go?

"S.Clark" wrote:

> What kind of record locking issues?
> How are the users locking the data? (i.e. from a form, directly in table, etc)
> Distributing the front end is always the right answer.
>
> "Jake F" wrote:
>
> > I'm having record locking issues and am not sure if putting the mde file on
> > everyone's computers will be the solution if they're all looking to one back
> > end file, or if I redo my database so that instead of the users changing one
> > table it appends information to a new table. I believe that would mean the
> > records will not be locked? Any advice?

KenSheridan via AccessMon · External Since: May 16, 2009 Posts: 68

Splitting the database into separate front and back ends is definitely the
way to go. You can use a .mde file for the front end if you wish, but do not
convert the back end to a .mde file. Make sure you keep a copy of the
original .mdb front end file as you'll need this to make any design changes
to the interface or if you upgrade to a later version of Access.

Basing the form on a query won't affect any conflicts, but its often a better
option than basing the form directly on a table. For one thing you can sort
the query to control the order in which records are shown in the form.

The other thing to consider is the record locking strategy. Setting the
form's RecordLocks property to 'Edited Record' (pessimistic locking) means
only one user can edit the same record simultaneously; setting it to 'No
Locks' (optimistic locking) means two or more users can be editing the same
record simultaneously, but all but the first to save the record will receive
an error message when they attempt to save the record. They'll be able to
copy their own edits while they examine the other user's changes, and then
either abort their own edits or override the other user's by pasting from the
clipboard. The other option of 'All Records' should not normally be
considered.

Back in the days before Doris Day was a virgin pessimistic locking was not a
practical proposition in Access as it only supported page locking, not true
record locking. That has not been the case for many years now.

Ken Sheridan
Stafford, England

Jake F wrote:
>The users are editing a single field in a table through a form. Would it
>also help if I used a query to populate the form instead of going directly to
>the table or that wouldn't matter? Still, each user having a copy of the mde
>file on their machine is the way to go?
>
>> What kind of record locking issues?
>> How are the users locking the data? (i.e. from a form, directly in table, etc)
>[quoted text clipped - 5 lines]
>> > table it appends information to a new table. I believe that would mean the
>> > records will not be locked? Any advice?

--
Message posted via http://www.accessmonster.com