Back end database lock-up

[Database Administrator]

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.

[Database Administrator]

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

[Database Administrator]

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

[Database Administrator]

On Nov 14, 2:22*pm, bobh wrote:
> 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 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.

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.

[Database Administrator]

On Nov 14, 3:42*pm, Sam Lambson wrote:
> On Nov 14, 2:22*pm, bobh wrote:
>
>
>
> > 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 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.
>
> 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

[Database Administrator]

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

[Database Administrator]

On Nov 14, 4:35*pm, BruceB wrote:
> On Nov 14, 3:42*pm, Sam Lambson wrote:
>
>
>
> > On Nov 14, 2:22*pm, bobh wrote:
>
> > > 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 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.
>
> > 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.

[Database Administrator]

Rich P wrote:

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