Back end database lock-up

[zmickle@gmail.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 Browne]

Re: Back end database lock-up

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" <samlambson@gma il.comwrote in message
news:d7557be2-e3ee-4707-9795-5abf2307da18@q3 0g2000prq.googl egroups.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.

[zmickle@gmail.com]

Re: Back end database lock-up

On Nov 13, 3:42 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

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" <samlamb...@gma il.comwrote in message
>
news:d7557be2-e3ee-4707-9795-5abf2307da18@q3 0g2000prq.googl egroups.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

[Rich P]

Re: Back end database lock-up

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

[Salad]

Re: Back end database lock-up

zmickle@gmail.c om 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?

[Allen Browne]

Re: Back end database lock-up

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" <samlambson@gma il.comwrote in message
news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x1 6g2000prn.googl egroups.com...
On Nov 13, 3:42 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

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" <samlamb...@gma il.comwrote in message
>
news:d7557be2-e3ee-4707-9795-5abf2307da18@q3 0g2000prq.googl egroups.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

[zmickle@gmail.com]

Re: Back end database lock-up

On Nov 13, 4:48 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

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" <samlamb...@gma il.comwrote in message
>
news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x1 6g2000prn.googl egroups.com...
On Nov 13, 3:42 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
>
>
>

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" <samlamb...@gma il.comwrote in message

>

news:d7557be2-e3ee-4707-9795-5abf2307da18@q3 0g2000prq.googl egroups.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

[bobh]

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...@gmail. com" <samlamb...@gma il.comwrote:

On Nov 13, 4:48 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
>
>
>
>
>

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" <samlamb...@gma il.comwrote in message

>

news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x1 6g2000prn.googl egroups.com....
On Nov 13, 3:42 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

>

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" <samlamb...@gma il.comwrote in message

>

>news:d7557be 2-e3ee-4707-9795-5abf2307da18@q3 0g2000prq.googl egroups.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 -

[Sam Lambson]

Re: Back end database lock-up

On Nov 14, 12:26 pm, bobh <vulca...@yahoo .comwrote:

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" <samlamb...@gma il.comwrote:
>

On Nov 13, 4:48 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

>

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" <samlamb...@gma il.comwrote in message

>

>news:884e75d 7-adb2-4bcb-a8c0-fc9109f75192@x1 6g2000prn.googl egroups.com....
On Nov 13, 3:42 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

>

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" <samlamb...@gma il.comwrote in message

>

news:d7557be2-e3ee-4707-9795-5abf2307da18@q3 0g2000prq.googl egroups.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)i n
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.

[bobh]

Re: Back end database lock-up

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 <samlamb...@gma il.comwrote:

On Nov 14, 12:26 pm, bobh <vulca...@yahoo .comwrote:
>
>
>
>
>

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" <samlamb...@gma il.comwrote:

>

On Nov 13, 4:48 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

>

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" <samlamb...@gma il.comwrote in message

>

news:884e75d7-adb2-4bcb-a8c0-fc9109f75192@x1 6g2000prn.googl egroups.com...
On Nov 13, 3:42 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

>

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" <samlamb...@gma il.comwrote in message

>

>news:d7557be 2-e3ee-4707-9795-5abf2307da18@q3 0g2000prq.googl egroups..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 -

[Sam Lambson]

Re: Back end database lock-up

Yes. When I open any database table it is not updatable, not just
those being queried. I The form has a one to one relationship with the
query, the query is pulling from two linked tables.

[bobh]

Re: Back end database lock-up

I'm a bit confused but, I work in XP and I don't have 2007

1. open both the front-end and the back-end seperately, click File-
Database properties, click on the 'general' tab and check the
'attributes to be sure 'read-only' is not checked off.

2. how are the two tables joined in the query itself ?? joined as a
line or a line with an arrow at one end?

3. have you tried doing a refresh links or even delete linked tables
and linking them back in again? In 2007 can you apply anything
criteria/parameters when linking to tables?

bobh.

On Nov 14, 3:56 pm, Sam Lambson <samlamb...@gma il.comwrote:

Yes.  When I open any database table it is not updatable, not just
those being queried. I The form has a one to one relationship with the
query, the query is pulling from two linked tables.

[Salad]

Re: Back end database lock-up

Sam Lambson wrote:

Yes. When I open any database table it is not updatable, not just
those being queried. I The form has a one to one relationship with the
query, the query is pulling from two linked tables.
>

I'd probably do this. Create a new backend database called JunkBE. I'd
create a table called Junk with a couple of fields. Save and exit

I'd then create a new fe database called JunkFE. I'd then link to table
Junk in JunkBE. I'd then use the form builder wizard and build a form
called Junk bound to table Junk or to a query based on table Junk.

I'd then give the FE to a couple of people and see if they can
add/delete/modify records in form Junk simultaneously.

Setting up Junk should take a few minutes to do, it'll probably take
more time getting it distributed. At least you'd see if Access is
multi-user in your environment.

It sounds like your existing database is quite minimal...only 1 form.
If you can get JunkFE/BE to work, maybe rewrite your existing app. At
least build the form, copy/paste the code module for the form, and see
if that works.

If your version is not multi-user you could ask for a new version of
Access from your vendor and specify you want the network, multi-user
version and not the single stand-alone version you purchased.



Just kidding :)

[Sam Lambson]

Re: Back end database lock-up

On Nov 14, 2:22 pm, bobh <vulca...@yahoo .comwrote:

I'm a bit confused but, I work in XP and I don't have 2007
>
1. open both the front-end and the back-end seperately, click File-
Database properties, click on the 'general' tab and check the
'attributes to be sure 'read-only' is not checked off.
>
2. how are the two tables joined in the query itself ?? joined as a
line or a line with an arrow at one end?
>
3. have you tried doing a refresh links or even delete linked tables
and linking them back in again? In 2007 can you apply anything
criteria/parameters when linking to tables?
>
bobh.
>
On Nov 14, 3:56 pm, Sam Lambson <samlamb...@gma il.comwrote:
>

Yes.  When I open any database table it is not updatable, not just
those being queried. I The form has a one to one relationship with the
query, the query is pulling from two linked tables.

1. The files are not read only when I initially open them. If I try
to open the back end database after someone has updated a field and
before they close the form, the database is read-only.

2. The two tables in design view are connected by a line, not an
arrow. They are linked on a common ID.

3. I have tried refreshing the linked tables, but it doesn't change
the behavior. There appear to be no options other than changing the
location of the source database.

Thanks for your help.

[BruceB]

Re: Back end database lock-up

On Nov 14, 3:42 pm, Sam Lambson <samlamb...@gma il.comwrote:

On Nov 14, 2:22 pm, bobh <vulca...@yahoo .comwrote:
>
>
>

I'm a bit confused but, I work in XP and I don't have 2007

>

1. open both the front-end and the back-end seperately, click File-
Database properties, click on the 'general' tab and check the
'attributes to be sure 'read-only' is not checked off.

>

2. how are the two tables joined in the query itself ?? joined as a
line or a line with an arrow at one end?

>

3. have you tried doing a refresh links or even delete linked tables
and linking them back in again? In 2007 can you apply anything
criteria/parameters when linking to tables?

>

bobh.

>

On Nov 14, 3:56 pm, Sam Lambson <samlamb...@gma il.comwrote:

>

Yes.  When I open any database table it is not updatable, not just
those being queried. I The form has a one to one relationship with the
query, the query is pulling from two linked tables.

>
1. The files are not read only when I initially open them.  If I try
to open the back end database after someone has updated a field and
before they close the form, the database is read-only.
>
2. The two tables in design view are connected by a line, not an
arrow.  They are linked on a common ID.
>
3. I have tried refreshing the linked tables, but it doesn't change
the behavior.  There appear to be no options other than changing the
location of the source database.
>
Thanks for your help.

I can't help but think this is a permissions issue. You stated
earlier that your users "only have read/write, no
delete, modify, or create" and I am assuming this pertains to the
folder and not the database itself. Your users absolutely must have
read, write, create, and delete rights to the _folder_ where the back
end database is located. If they need to change data then they must
have read and write access to the database file itself. I'd make sure
your server admins haven't done something strange. See if they'll
grant full control to to your users on the folder where your database
is on a trial basis to see if that fixes the problem.

Also, along the lines of what Salad suggested with the 'junk'
database...try splitting the sample Northwind database into front and
back end and put the back end on the server, preferably in the same
folder as the back end of your other database. See if your users can
get Northwind to manifest (or not manifest) the problem you described
by using the front end you created.

Also...are you using the new 'multivalue' fields in any of your
tables?

Bruce