Access 07: My query is making the back end database lock up

[Sam Lambson]

Hi,

This is similar to another post, because I still haven't gotten to the
bottom of it.

I have a back end database on a shared drive. Each (of 4) user has a
front end file on their own C: drive. The main form that users have
is bound to a query which merges 2 linked tables. It is not an action
query. When the query is executed, everyone still has access to the
form/query, until somebody makes an update. Or so it seems.

I have found that if I close and open the datasheet view of the query
on one computer when it is open on another computer, the query is not
updatable the second time I open it.

Once the dataset is "not updatable" the entire database is locked
until the form/query is closed on the computer that made updates.

Does anyone have experience with this behavior? The wierdest thing of
all is that when it was an 03 database, not split into front/back
ends, it worked fine. We've only had problems since we moved to 07.

Thanks,

Sam

[Rich P]

Re: Access 07: My query is making the back end database lock up

Hi Sam,

Think of an Excel file on a server which is accessible to multiple
users. If user1 opens the Excel file - when user2 opens the same excel
file it will be read_Only. Access is a file based RDBMS. This is why
if you have multiple users accessing the same data at the same time - it
will be read_only for everyone else while user1 is using it. And this
is why for a multi-user environment the operations on a server based
system will be much easier to manage because a server based system
serves up data sessions for each user rather than opening up one session
like a file based system for all users.

In win3.11 you didn't have all these locks - which of course, led to a
variety of other problems (with data consistency) and thus emerged the
locks - which in a 32bit system create the problem you are experiencing,
and thus emerged server based systems.

Rich

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

[Sam Lambson]

Re: Access 07: My query is making the back end database lock up

Thanks Rich,

I understand your reasoning, but I don't understand why Access has
settings for record-level locking, optimistic locking, and other
features designed to handle multiple users inputing data over a
network. When it was in the 03 format, the database could be opened
on 3 different systems simulteneusly from a network location. It was
only when we migrated to 07 that this locking issue began. Though I
agree that server technology is best, I am convinced by reading MS
support and other forums that a stable multi-user environment is not
only possible with Access, but is a feature that it advertises.

I can have multiple machines open their own front-end instance of
access, open the form, and once everyone has it open, they can all
update. The database is only locked for users trying to open a new
instance of the form/query.

I am working on getting a SQL Server DB set up, but in the mean time,
I'd like to at least restore the functionality that this application
had under Access 03

Cordially,

Sam

[Rich P]

Re: Access 07: My query is making the back end database lock up

Yes, Access has multi-user capabilities - but these capabitlities are
not consistent. That is the issue - the consistency of this
functionality. In some environments Access is still the champ that it
was last century. But in most corporate environments - technology has
moved on.

Access lives on because it is still the #1 file based RDBMS. But in
corporate environments - file based RDBMS is being phased out in favor
of server based systems. This boils down to using the right tool for
the right job. If Access is not working out as advertised for your
environment - then your company needs to evaluate if you should migrate
to a system that will work in your enviroment - or changed the
environment to one that is more suitable for Access.

Rich

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

[Sam Lambson]

Re: Access 07: My query is making the back end database lock up

On Nov 14, 2:16 pm, Rich P <rpng...@aol.co mwrote:

Yes, Access has multi-user capabilities - but these capabitlities are
not consistent.  That is the issue - the consistency of this
functionality.  In some environments Access is still the champ that it
was last century.  But in most corporate environments - technology has
moved on.  
>
Access lives on because it is still the #1 file based RDBMS.  But in
corporate environments - file based RDBMS is being phased out in favor
of server based systems.  This boils down to using the right tool for
the right job.  If Access is not working out as advertised for your
environment - then your company needs to evaluate if you should migrate
to a system that will work in your enviroment - or changed the
environment to one that is more suitable for Access.
>
Rich
>
*** Sent via Developersdexht tp://www.developersd ex.com***

Rich,

Thanks for your advice. I'm definitely going to see about using a
server based system.

Cordially,

Sam

[Salad]

Re: Access 07: My query is making the back end database lock up

Sam Lambson wrote:

Thanks Rich,
>
I understand your reasoning, but I don't understand why Access has
settings for record-level locking, optimistic locking, and other
features designed to handle multiple users inputing data over a
network. When it was in the 03 format, the database could be opened
on 3 different systems simulteneusly from a network location. It was
only when we migrated to 07 that this locking issue began. Though I
agree that server technology is best, I am convinced by reading MS
support and other forums that a stable multi-user environment is not
only possible with Access, but is a feature that it advertises.
>
I can have multiple machines open their own front-end instance of
access, open the form, and once everyone has it open, they can all
update. The database is only locked for users trying to open a new
instance of the form/query.

Stop right there.

What do you mean new instance of the form?

Are you using new instances to open a form? As in
Dim frm as Form
Set frm = New Form_frmTest

Or do you mean this...Let's say you have everybody out of the
application. You click on the desktop icon, enter the form and start
modifying the record. You minimize that app, click on the desktop icon,
and enter the form. Can you update anything? Can you work between the
two open apps?

>
I am working on getting a SQL Server DB set up, but in the mean time,
I'd like to at least restore the functionality that this application
had under Access 03

I don't see why SQL server will make a non-functioning app work any better.

>
Cordially,
>
Sam

[Sam Lambson]

Re: Access 07: My query is making the back end database lock up

On Nov 14, 2:58 pm, Salad <o...@vinegar.c omwrote:

Sam Lambson wrote:

Thanks Rich,

>

I understand your reasoning, but I don't understand why Access has
settings for record-level locking, optimistic locking, and other
features designed to handle multiple users inputing data over a
network.  When it was in the 03 format, the database could be opened
on 3 different systems simulteneusly from a network location.  It was
only when we migrated to 07 that this locking issue began.  Though I
agree that server technology is best, I am convinced by reading MS
support and other forums that a stable multi-user environment is not
only possible with Access, but is a feature that it advertises.

>

I can have multiple machines open their own front-end instance of
access, open the form, and once everyone has it open, they can all
update.  The database is only locked for users trying to open a new
instance of the form/query.

>
Stop right there.
>
What do you mean new instance of the form?
>
Are you using new instances to open a form?  As in
Dim frm as Form
Set frm = New Form_frmTest
>
Or do you mean this...Let's say you have everybody out of the
application.  You click on the desktop icon, enter the form and start
modifying the record.  You minimize that app, click on the desktop icon,
and enter the form.  Can you update anything?  Can you work between the
two open apps?
>
>
>

I am working on getting a SQL Server DB set up, but in the mean time,
I'd like to at least restore the functionality that this application
had under Access 03

>
I don't see why SQL server will make a non-functioning app work any better.
>
>
>

Cordially,

>

Sam

Salad,

Thanks. I tried what you suggested, and opened the front end twice on
the same computer. I had two instances of the form on the same
desktop and I did not experience any locking problems.

Upon further troubleshooting this afternoon, I dont' even think this
problem has to do with the form or the query. If I open a linked
table on one computer and edit it, then I try to open the same table
on another computer it is locked. The whole database locks up, not
just the record or page. Simply opening a linked table locks down the
entire database. If I could overcome that problem, I think it would
fix my form issue.

[Sam Lambson]

Re: Access 07: My query is making the back end database lock up

So my original theory was that my query or form was locking up my back-
end, but now I think it might be my environment. Whenever I open a
linked-table, query or form and edit an entity, the backend is
completely locked up. Every table. The whole thing is read only.

I split the Northwind database and got the same results. I checked
permissions via windows explorer and instead of "Full Control" in the
main security settings, it has "Special Permissions" Checked and when
I click Advanced, it takes me to a screen with a list of user groups
and next to the group that is concerned it says "Full Control."

Could my problem be with permissions or my UNIX share?

Thanks

[BruceB]

Re: Access 07: My query is making the back end database lock up

On Nov 18, 12:20 pm, Sam Lambson <samlamb...@gma il.comwrote:

So my original theory was that my query or form was locking up my back-
end, but now I think it might be my environment.  Whenever I open a
linked-table, query or form and edit an entity, the backend is
completely locked up.  Every table.  The whole thing is read only.
>
I split the Northwind database and got the same results.  I checked
permissions via windows explorer and instead of "Full Control" in the
main security settings, it has "Special Permissions" Checked and when
I click Advanced, it takes me to a screen with a list of user groups
and next to the group that is concerned it says "Full Control."
>
Could my problem be with permissions or my UNIX share?
>
Thanks

UNIX? It might have been helpful to know that bit of information
earlier. Yes, it may well be a problem with your UNIX share. I don't
know what Microsoft's stance is on using an Access 2007 database back
end on a UNIX share is but I know it's not supported with Access
2000. You may simply have gotten lucky that 2003 worked. You open
yourself up to the possibility of peculiar behaviors just like you
describe. Check out the knowledgebase article on Microsoft's support
site ("Microsoft Access and Untested Networks") at

http://support.microsoft.com/kb/209161/en-us

One of the potential problems described there is "An inability to open
a multiuser file (non-exclusively)."

Bruce

[Sam Lambson]

Re: Access 07: My query is making the back end database lock up

On Nov 18, 1:12 pm, BruceB <deluxeinformat ...@gmail.comwr ote:

On Nov 18, 12:20 pm, Sam Lambson <samlamb...@gma il.comwrote:
>

So my original theory was that my query or form was locking up my back-
end, but now I think it might be my environment.  Whenever I open a
linked-table, query or form and edit an entity, the backend is
completely locked up.  Every table.  The whole thing is read only.

>

I split the Northwind database and got the same results.  I checked
permissions via windows explorer and instead of "Full Control" in the
main security settings, it has "Special Permissions" Checked and when
I click Advanced, it takes me to a screen with a list of user groups
and next to the group that is concerned it says "Full Control."

>

Could my problem be with permissions or my UNIX share?

>

Thanks

>
UNIX?  It might have been helpful to know that bit of information
earlier.  Yes, it may well be a problem with your UNIX share.  I don't
know what Microsoft's stance is on using an Access 2007 database back
end on a UNIX share is but I know it's not supported with Access
2000.  You may simply have gotten lucky that 2003 worked.  You open
yourself up to the possibility of peculiar behaviors just like you
describe.  Check out the knowledgebase article on Microsoft's support
site ("Microsoft Access and Untested Networks") at
>

http://support.microsoft.com/kb/209161/en-us

>
One of the potential problems described there is "An inability to open
a multiuser file (non-exclusively)."
>
Bruce

Very helpful, thanks Bruce. I should have thought of that first.