Re: Back end database lock-up

Hi,
If your query is a 'select' query and it has multiple tables in it and
it's bound to the form you might try changing the Recordset Type of
that query.
open the query to design view, open the properities box, single-click
in a blank spot in the tables area of the query(I forget what that
area is called), scan down the properties and find 'recordset type'
change it from 'dynaset' to 'inconsistent update', save it. load the
form and try updating again.

it may help with your issue.
bobh.


On Nov 13, 10:35 pm, "zmic...@xxxxxxxxx" <samlamb...@xxxxxxxxx> wrote:
On Nov 13, 4:48 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx> wrote:





Is this a bound form? Or are you programmaticlly executing some updates in
an action query or some code?

A bound form should not behave as you describe, but code might lock a whole
table.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"zmic...@xxxxxxxxx" <samlamb...@xxxxxxxxx> wrote in message

news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 13, 3:42 am, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx> wrote:

Suggestions:

1. Is this on a wired (CAT5) network?
No unstable connections (such as notebooks using WiFi)?

2. You say that users each have a different front end MDB on their own
computers, but then you say they open the front end (singular)
simultaneously. Is each one opening a separate front end MDB file that's
on
their own C: drive? (That's best.)

3. Check the open mode and locking strategy of *each* front end.
In A2007, that's Office Button | Access Options | Advanced.
In earlier versions it's Tools | Options | Advanced.
You should see:
- Default Open mode: Shared
- Default Record Locking: No Locks

4. Close all front ends. Make sure the LDB file in the back-end folder is
gone. Then try again. (In practice, it's the first user in who sets the
actual locking mode.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"zmic...@xxxxxxxxx" <samlamb...@xxxxxxxxx> wrote in message

news:d7557be2-e3ee-4707-9795-5abf2307da18@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Excuse my noobness. I am managing an access database that is shared
by 4 users. Management does not want to use any technologies outside
of access for this application (no SQL Server, etc). I have split the
database into front end and back end databases and put the front end
on each of the users' computers. All users can open the front end
simultaneously, but once somebody updates data in the form, the back
end locks up.

What I expect to happen:
- I assume that access should use optimistic locking (?) that would
only lock the records that are being updated, but it locks the entire
database.

what I've tried:
- i checked and double checked that the query and form are set to no-
locks
- I tried taking out all calculated values (string manipulations) in
the query
- I'm not the only one working on this, so there have been quite a few
other things looked at to no avail.

I'm stumped. Thanks for any help.

Allen,

Thanks so much for your response

1. Yes every computer has a  wired.

2. Each user has their own ACCDB front end on their own C: drive.

3. Each front end is configured as you suggested in Access Options (I
will double check today)

4. The locking file is not created until a user updates the data via
the form.  Any computer that has that same form already open can make
changes as well.  After an update is made, any new instance of the
form is "not updatable." When the updating users close the form, the
locking file goes away.

Something about updating the query through the form locks the back end
database, then the back end is locked until the form is completely
closed.

I think the problem is occurring at the point that someone updates.

Thanks,

Sam

Thanks for the responses.  I may request a SQL Server instance.  With
the right argument I may be able to convince my higher-ups to allow
it.  But here are the answers to the questions asked in your
responses:

@Salad:
What version of Access are you using? Access 2007 (enterprise I think)

Is the backend on a network server? Yes it is on a shared drive that
is mapped to each workstation.  The linked tables are referencing the
backend via drive name (i.e. Q:...) rather than the network name.

Keith Wilby mentioned rights.  I think in Novell for rights; read,
write, erase (delete), modify, create.  You said you had read/write.
Are there other rights you can grant?  They only have read/write, no
delete, modify, or create.  I do not have rights to change these, but
might be able to request it if I find out that it is a must-have.

Are you up to date on program service packs on all computers?
yes. I can check, but I believe so.

@Allen

By bound form do you mean the form is bound to a query, then yes.  In
the data source property, there is a query.  The query simply merges
two tables on a foreign key.  values from the query are available in
the form and when you put your cursor in the textbox, you can update
the data.  That is when the backend locks up.

They want this thing working by tomorrow, so if it's still not working
by end of day, I'll request a SQL Server DB.

Thanks for the help.

Sam- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Dynaset, inconsistent updates
    ... confusion is to show us the sql statement ... you switch the query from Design View to SQL View by using the ... "CLEARLY" see a recordset type is a SELECT query. ... Gina Whipp ...
    (microsoft.public.access.queries)
  • Re: Back end database lock-up
    ... If your query is a 'select' query and it has multiple tables in it and ... scan down the properties and find 'recordset type' ... Default Record Locking: No Locks ... database into front end and back end databases and put the front end ...
    (comp.databases.ms-access)
  • Re: Dynaset, inconsistent updates
    ... Gina Whipp ... My code was referencing a select query. ... confusion is to show us the sql statement (or at least the first few ... "CLEARLY" see a recordset type is a SELECT query. ...
    (microsoft.public.access.queries)
  • Re: Query is Updatable - but the form based on it - is not - Why??
    ... changed the recordset type to: ... Dynaset (Inconsistent Updates), so now the query is ok. ...
    (microsoft.public.access.forms)
  • Re: Back end database lock-up
    ... Default Record Locking: No Locks ... - i checked and double checked that the query and form are set to no- ... The locking file is not created until a user updates the data via ...
    (comp.databases.ms-access)