Back end database lock-up

[zmickle]

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]

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.

"zm*****@gmail.com" <sa********@gmail.comwrote in message
news:d7**********************************@q30g2000 prq.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.

[zmickle]

On Nov 13, 3:42*am, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

news:d7**********************************@q30g2000 prq.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

[Rich P]

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]

zm*****@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?

[Allen Browne]

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.

"zm*****@gmail.com" <sa********@gmail.comwrote in message
news:88**********************************@x16g2000 prn.googlegroups.com...
On Nov 13, 3:42 am, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

news:d7**********************************@q30g2000 prq.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

[zmickle]

On Nov 13, 4:48 pm, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

news:88**********************************@x16g2000 prn.googlegroups.com...
On Nov 13, 3:42 am, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

news:d7**********************************@q30g2000 prq.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

[bobh]

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, "[e-mail]" <[e-mail]:

On Nov 13, 4:48 pm, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

news:88**********************************@x16g2000 prn.googlegroups.com....
On Nov 13, 3:42 am, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

>news:d7**********************************@q30g200 0prq.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 -

[Sam Lambson]

On Nov 14, 12:26*pm, bobh <[e-mail]:

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, "[e-mail]" <[e-mail]:

On Nov 13, 4:48 pm, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

>news:88**********************************@x16g200 0prn.googlegroups.com....
On Nov 13, 3:42 am, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

news:d7**********************************@q30g2000 prq.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.

[bobh]

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 <[e-mail]:

On Nov 14, 12:26*pm, bobh <[e-mail]:

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, "[e-mail]" <[e-mail]:

On Nov 13, 4:48 pm, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

news:88**********************************@x16g2000 prn.googlegroups.com...
On Nov 13, 3:42 am, "Allen Browne" <[e-mail]:

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.

"[e-mail]" <[e-mail] in message

>news:d7**********************************@q30g200 0prq.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 -

[Sam Lambson]

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]

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 <[e-mail]:

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]

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]

On Nov 14, 2:22*pm, bobh <[e-mail]:

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 <[e-mail]:

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]

On Nov 14, 3:42*pm, Sam Lambson <[e-mail]:

On Nov 14, 2:22*pm, bobh <[e-mail]:

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 <[e-mail]:

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

[Sam Lambson]

>
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

Thanks Bruce,

I have checked and double checked. All users accessing the database
have full control on the folder that the database is in. I granted
full control to the file itself as well. I don't think it's a
permissions issue. In fact, after troubleshooting some more I don't
think it is an issue with my form or query, but with the tables
themselves.

I did the following: On one computer I open the front end and open a
linked table. It opened in datasheet view and was editable. When I
open the front end on another computer and try to open ANY linked
table, query, or form, it is not editable. The whole database is
locked down, not just the tables concerned.

Would SQL Server act differently or is my problem in my front end. I
can't seem to figure this one out since everyone's Access Options are
set to share and have no locks, each query and form is set to have no
locks.

There is one thing I haven't tried and that is to have "row level
locking" set in the table properties. I don't know if that would free
things up. I'm stumped.

Sam

[Sam Lambson]

On Nov 14, 4:35*pm, BruceB <[e-mail]:

On Nov 14, 3:42*pm, Sam Lambson <[e-mail]:

On Nov 14, 2:22*pm, bobh <[e-mail]:

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 <[e-mail]:

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

And I'll try to split the northwind database. That is a good idea.

Thanks Bruce et al.

[Tony Toews [MVP]]

Rich P <rp*****@aol.comwrote:

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

I don't see how 16 bits vs 32 bits makes any difference whatsover in the suitability
of Access for use in mutli user projects. After all SQL Server was first shipped in
OS/2 a 16 bit environments.

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

Please explain how you could replicate just the data in a combined FE/BE MDB.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:49***********************@news.qwest.net:

The message I'm answering here was completely malarkey from
beginning to end. Disregard its content completely as there isn't a
single point on which it is correct or reliable in the advice it
gives.

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

This is just complete malarkey.

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.

That's the correct thing to do when your user population, or your
security needs, or your reliability needs exceed what a Jet back end
provides.

Not to knock Access - it is a great mini RDBMS, but for 32 bit
systems

The 32-bit thing is complete bullshit -- it has *nothing* to do with
whether or not a Jet back end will be appropriate or not.

in an enterprise environment - this is why MS developed a server
system.

MS purchased a version of Sybase, which predates MS Jet. So, your
history is completely wrong in all respects.

A file based system like Access just isn't as consistent in a 32
bit

RED HERRING -- completely irrelevant.

environment as a server sytem for multi-user ops.

Aha. I see you really don't have a clue what you are talking about,
as the above clause implies that you think a Jet back end is "a
server system." It's not. And that's fine. Anyone who doesn't
understand the difference between a Jet back end (i.e., stored on a
file server and access via the file system) and a database server is
not really qualified to be offering advice.

That would be *you*, Rich P, who is not qualified to offer comments
here.

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.

Even worse advice. That you may be so incompetent that you can't
build reliable multi-user apps with Jet back ends does not mean that
there aren't plenty of competent people out there who are able to do
so.

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

[David W. Fenton]

Sam Lambson <sa********@gmail.comwrote in
news:d0**********************************@i18g2000 prf.googlegroups.co
m:

I did the following: On one computer I open the front end and open
a linked table. It opened in datasheet view and was editable.
When I open the front end on another computer and try to open ANY
linked table, query, or form, it is not editable. The whole
database is locked down, not just the tables concerned.

Have you gotten everyone out and deleted the LDB file? Have you set
the locking in all your application's forms to be optimistic (i.e.,
No Locks)?

Would SQL Server act differently or is my problem in my front end.
I can't seem to figure this one out since everyone's Access
Options are set to share and have no locks, each query and form is
set to have no locks.

Fix the problem with your Access setups, rather than buying all the
problems that come with upsizing to SQL Server.

There is one thing I haven't tried and that is to have "row level
locking" set in the table properties. I don't know if that would
free things up. I'm stumped.

No, this isn't going to help.

In another post you mention something about splitting the northwind
database. Is your app split? If not, that's likely the source of all
your problems. You should split the app and each user should have an
individual front end. If you don't do that, then nobody here is
going to be much interested in offering any further advice, since
you'll be going against the most fundamental of best practices in
Access application development.

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

[Rich P]

You guys get too wrapped up into legalism stuff about Access operations.
The advice I offer is more for RDBMS operations - not just Access
operations - File based and server based. If an operation would be
better suited with a server based system - I would suggest that. The
idea is to offer the easiest solution. If Access requires too much
tweakage to perform an operation then it is no longer the easiest
solution. The idea is to offer the easiest solution. More times than
not - for multi-user stuff - the server based solution is easier. Maybe
my supporing details may not be 100% accurate - so fire me. Wait - I'm
doing this for free.

Bottom line - I go the easiest route. It is cheaper.

Rich

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

[Sam Lambson]

On Nov 17, 12:09*am, Rich P <[e-mail]:

You guys get too wrapped up into legalism stuff about Access operations.
The advice I offer is more for RDBMS operations - not just Access
operations - File based and server based. *If an operation would be
better suited with a server based system - I would suggest that. *The
idea is to offer the easiest solution. If Access requires too much
tweakage to perform an operation then it is no longer the easiest
solution. *The idea is to offer the easiest solution. *More times than
not - for multi-user stuff - the server based solution is easier. *Maybe
my supporing details may not be 100% accurate - so fire me. *Wait - I'm
doing this for free.

Bottom line - I go the easiest route. *It is cheaper.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Alright. The problem, it turns out is not the form, nor the query,
nor the linked tables. I think it might be my environment. My shared
backend reside on a network drive that is found on a UNIX system. I
checked teh permissions for the folder, and in the main settings
nothing is clicked but "Special" and when I go to Advanced, it has the
groups "Everone" "Me" and "The Group that employees belong to" and
gives "Full control" to me and the group of employees and grants
nothing to everyone.

The reason I think it might be permissions is that the entire backend
locks up anytime a open a linked table, query, or form on the front
end. It locks the entire backend as if it was open exclusively,
granting read-only to anyone trying to access it.

Is it my environment or something else?

[Tony Toews [MVP]]

Sam Lambson <sa********@gmail.comwrote:

>Alright. The problem, it turns out is not the form, nor the query,
nor the linked tables. I think it might be my environment. My shared
backend reside on a network drive that is found on a UNIX system. I
checked teh permissions for the folder, and in the main settings
nothing is clicked but "Special" and when I go to Advanced, it has the
groups "Everone" "Me" and "The Group that employees belong to" and
gives "Full control" to me and the group of employees and grants
nothing to everyone.

The reason I think it might be permissions is that the entire backend
locks up anytime a open a linked table, query, or form on the front
end. It locks the entire backend as if it was open exclusively,
granting read-only to anyone trying to access it.

Is it my environment or something else?

That is quite possible. I know next to nothing about *nix networking. However
Microsoft Access does "interesting" things with locking. Now this may only apply to
Jet locking of records and not to locking the complete file. Also older versions of
Samba, the *nix module which handles Microsoft compatible networking, had problems
years ago with exactly duplicating ill documented MS networking features. But I
thnik those should've been long fixed. Maybe.

The problem is that MS uses "phantom locks" in a non standard method. So it may work
well and may not. The following discussion is in reference to Samba. Note that
Novell does handle Access well. Well, except for some versions and the OpLocks
problem.

Samba, the Unix component which provides Windows networking services, has been
written to the documented specs and yet sometimes doesn't work well. For many people
this has worked quite well but others have had lots of troubles.

ACC2000: Microsoft Access and Untested Networks - 209161
http://support.microsoft.com/?kbid=209161

I think ACC: Microsoft Jet Utilities Available [Q176670]
http://support.microsoft.com/?kbid=176670 has some detailed info on "phantom locks"
in the "Understanding Microsoft Jet Locking" whitepaper,

There is an introduction on SAMBA at http://us1.samba.org/samba/docs/SambaIntro.html.
It info on mapping Windows drives to Linux directories.

Someone stated somewhere, somewhen:
-Use Samba >2.2.7 and copy the backend to this share
-Read/write-permisssion for the users
-Install the frontend on every pc an connect the tables
-Read the doc readme2k.txt in the samba-share of the linux-box

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[BruceB]

On Nov 18, 12:07*pm, Sam Lambson <[e-mail]:

On Nov 17, 12:09*am, Rich P <[e-mail]:

You guys get too wrapped up into legalism stuff about Access operations..
The advice I offer is more for RDBMS operations - not just Access
operations - File based and server based. *If an operation would be
better suited with a server based system - I would suggest that. *The
idea is to offer the easiest solution. If Access requires too much
tweakage to perform an operation then it is no longer the easiest
solution. *The idea is to offer the easiest solution. *More times than
not - for multi-user stuff - the server based solution is easier. *Maybe
my supporing details may not be 100% accurate - so fire me. *Wait - I'm
doing this for free.

Bottom line - I go the easiest route. *It is cheaper.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Alright. *The problem, it turns out is not the form, nor the query,
nor the linked tables. *I think it might be my environment. *My shared
backend reside on a network drive that is found on a UNIX system. *I
checked teh permissions for the folder, and in the main settings
nothing is clicked but "Special" and when I go to Advanced, it has the
groups "Everone" "Me" and "The Group that employees belong to" and
gives "Full control" to me and the group of employees and grants
nothing to everyone.

The reason I think it might be permissions is that the entire backend
locks up anytime a open a linked table, query, or form on the front
end. *It locks the entire backend as if it was open exclusively,
granting read-only to anyone trying to access it.

Is it my environment or something else?

I think it's the UNIX. See my response to your other thread.

Bruce

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:49***********************@news.qwest.net:

You guys get too wrapped up into legalism stuff about Access
operations. The advice I offer is more for RDBMS operations - not
just Access operations - File based and server based. If an
operation would be better suited with a server based system - I
would suggest that. The idea is to offer the easiest solution. If
Access requires too much tweakage to perform an operation then it
is no longer the easiest solution. The idea is to offer the
easiest solution. More times than not - for multi-user stuff -
the server based solution is easier. Maybe my supporing details
may not be 100% accurate - so fire me. Wait - I'm doing this for
free.

If you'd posted only what you've written above, there would have
been no dispute.

But what you actually posted was something completely different,
filled with complete factual inaccuracies that misrepresented
reality. Hence, the post criticizing what you wrote.

Now that your overstatements and misstatements have been pointed
out, you seem quite capable of writing something correct and
appropriate. Why not assume people like me will jump on your
misstatements in the future, and write something like the paragraph
I quote above on the first go-round?

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

[David W. Fenton]

Sam Lambson <sa********@gmail.comwrote in
news:49**********************************@o4g2000p ra.googlegroups.com
:

My shared
backend reside on a network drive that is found on a UNIX system.

I would never support this scenario. Jet is much too closely tied to
the Windows file system, and SAMBA can only fake SMB networking.
Linux can't fake the underlying file I/O because it cannot use
native NTFS file access.

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