Back end database lock-up

[Database Administrator]

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.

[Database Administrator]

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.

"zmickle@gmail.com" wrote in message
news:d7557be2-e3ee-4707-9795-5abf2307da18@q30g2000prq.googlegroups.com...
> 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.

[Database Administrator]

On Nov 13, 3:42*am, "Allen Browne" 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...@gmail.com" wrote in message
>
> news:d7557be2-e3ee-4707-9795-5abf2307da18@q30g2000prq.googlegroups.com...
>
> > 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

[Database Administrator]

This is just a point of view about using the right tool for the right
job. For reliable multi-user operations in a 32 bit system - you should
use a multi-user system designed for 32 bit systems - sql server (which
I realize is not an option for you). Access multi-user was designed for
16 bit systems (win 3.11).

Several years ago I was contracting for a large company that had
migrated a project from main frames to Access (multi-user - win 2000)
and were having several problems - some similar to yours. I rewrote the
application(s) which solved some of the problems and also recommended
upgrading to sql server. They declined and went back to mainframes. I
left. Last I heard (from a former coworker at that place) they finally
stepped up to sql server.

Not to knock Access - it is a great mini RDBMS, but for 32 bit systems
in an enterprise environment - this is why MS developed a server system.
A file based system like Access just isn't as consistent in a 32 bit
environment as a server sytem for multi-user ops. If you are restricted
to Access you may be better off using individual apps (front and back
end combined) for each user. Then replicate their individual data to a
master mdb.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Database Administrator]

zmickle@gmail.com wrote:

> 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.

What version of Access are you using?

Is the backend on a network server?

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?

Are you up to date on program service packs on all computers?

[Database Administrator]

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.

"zmickle@gmail.com" wrote in message
news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x16g2000prn.googlegroups.com...
On Nov 13, 3:42 am, "Allen Browne" 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...@gmail.com" wrote in message
>
> news:d7557be2-e3ee-4707-9795-5abf2307da18@q30g2000prq.googlegroups.com...
>
> > 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

[Database Administrator]

On Nov 13, 4:48 pm, "Allen Browne" 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...@gmail.com" wrote in message
>
> news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x16g2000prn.googlegroups.com...
> On Nov 13, 3:42 am, "Allen Browne" 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...@gmail.com" wrote in message
>
> >news:d7557be2-e3ee-4707-9795-5abf2307da18@q30g2000prq.googlegroups.com...
>
> > > 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

[Database Administrator]

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...@gmail.com" wrote:
> On Nov 13, 4:48 pm, "Allen Browne" wrote:
>
>
>
>
>
> > Is this a bound form? Or are you programmaticlly executing some updatesin
> > 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...@gmail.com" wrote in message
>
> >news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x16g2000prn.googlegroups.com....
> > On Nov 13, 3:42 am, "Allen Browne" 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...@gmail.com" wrote in message
>
> > >news:d7557be2-e3ee-4707-9795-5abf2307da18@q30g2000prq.googlegroups.com....
>
> > > > 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 -

[Database Administrator]

On Nov 14, 12:26*pm, bobh wrote:
> 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...@gmail.com" wrote:
>
> > On Nov 13, 4:48 pm, "Allen Browne" 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 awhole
> > > 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...@gmail.com" wrote in message
>
> > >news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x16g2000prn.googlegroups.com....
> > > On Nov 13, 3:42 am, "Allen Browne" 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 setsthe
> > > > 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...@gmail.com" wrote in message
>
> > > >news:d7557be2-e3ee-4707-9795-5abf2307da18@q30g2000prq.googlegroups.com...
>
> > > > > 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 splitthe
> > > > > 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 tono-
> > > > > 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 -

bobh,

I tried it, but to no avail. It still locks up. In fact, if I open
just the query on one computer, it will be locked for any new instance
of the datasheet. I cannot figure this on out.

[Database Administrator]

1. for the join in the query you are using as the form's recordsource
a one-to-one join and a one-to-many join??

2. have you tried updating each table singularly? might help with
determining if a table is locking up?

bobh.

On Nov 14, 3:20*pm, Sam Lambson wrote:
> On Nov 14, 12:26*pm, bobh wrote:
>
>
>
>
>
> > 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...@gmail.com" wrote:
>
> > > On Nov 13, 4:48 pm, "Allen Browne" 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 locka 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...@gmail.com" wrote in message
>
> > > >news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x16g2000prn.googlegroups.com...
> > > > On Nov 13, 3:42 am, "Allen Browne" 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 filethat'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...@gmail.com" wrote in message
>
> > > > >news:d7557be2-e3ee-4707-9795-5abf2307da18@q30g2000prq.googlegroups..com...
>
> > > > > > 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, theback
> > > > > > 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 -
>
> bobh,
>
> I tried it, but to no avail. *It still locks up. *In fact, if I open
> just the query on one computer, it will be locked for any new instance
> of the datasheet. *I cannot figure this on out.- Hide quoted text -
>
> - Show quoted text -