Question Access 2007 Back End Locks Up

[MightyAC]

I have an Access 07 application with a sepparate front end (FE) that links to a back end (BE) via linked tables. When I connect a second FE to the BE across a windows network the application freezes until the local front end no longer has any connections to the data open.

This situation seems specific to Access 07 and to Vista based PCs. I used the same application in Access 03 without problems on the same Vista to Vista network. The application also works fine in Access 07 but on an XP to XP or XP to Win Server 03 network.

The Access app is being run using the 07 runtime. The application sets the default record locking to optimistic and the application open mode to Shared.

On the Vista PC which acts as the server I've given the Everyone user Full Control to the folder containing the application.

I've shared the folder containing the application, made 'Eveyone' a Co-Owner and 'Everyone' has Full Control.

On the satellite Vista PC I've mapped a network drive to the shared folder. 'Everyone' has full control to the mapped drive.

I'm stumped so any help would be appreciated. Thanks.

 

[MightyAC]

Any ideas would be helpful as I'm stumped.

Thanks
-AC

 

[SOS]

When you say - "when I connect a second FE" does that mean you have just a copy of the first frontend on another machine? Or are you trying to do up a completely different frontend and link?

 

[MightyAC]

Thanks for the reply. Here is the setup:

Computer 1 and Computer 2 each have a completely unique copy of the same front end linked to the same back end database.

Computer 1 Contains:
C:\Folder\FrontEnd.mdb Linked To C:\Folder\BackEnd.mdb

The folder C:\Folder is shared. Everyone is a CoOwner, Everyone has full control

Computer 2 Contains:
S:\ drive mapped to //Computer1/Folder

C:\Folder\FrontEnd.mdb Linked to S:\BackEnd.mdb

I hope this illustrates the setup a little better.

 

[SOS]

Thanks for the reply. Here is the setup:

Computer 1 and Computer 2 each have a completely unique copy of the same front end linked to the same back end database.

Computer 1 Contains:
C:\Folder\FrontEnd.mdb Linked To C:\Folder\BackEnd.mdb

The folder C:\Folder is shared. Everyone is a CoOwner, Everyone has full control

Computer 2 Contains:
S:\ drive mapped to //Computer1/Folder

C:\Folder\FrontEnd.mdb Linked to S:\BackEnd.mdb

I hope this illustrates the setup a little better.

I think it does.

1. Everyone should have a copy of the front end on their computer and it shouild be linked to the same location \\Computer1\Folder\BackEnd.mdb

2. If they all have their own copy then it should not have errors about sharing because even if they don't have SHARED as a default (and it is a per user setting, not database default) then they won't honk everyone else up.

3. And less likelihood of corruption.

 

[MightyAC]

That is how I have it setup though.

Each user has their own front end linked to one back end. However, as soon as one users interacts with the data in anyway user two's application is completely frozen until user one is no longer editting data.

I can put two unique copies of the front end on the same PC and link them both to the data file and there is no locking problem at all, so the problem must relate to the network. However, as I mentioned in the original post the locking problem only occurs when using Access 07 (works fine in Access 03) and seeminly only when on a Vista to Vista network.

My guess is there is an additional level of security, that I'm not aware of, in Access 07 and/ Vista.

This is what I've checked in the way of security and locking settings:
1) NTFS - Everyone user has Full Control to the application folder
2) The files in the folder are inheriting the folder permissions
3) The application folder is shared and Everyone is listed as a co-owner of the share
4) The Access front end manually sets the default record locking to 'optimistic' and the application open mode to 'shared'
5) The app locks up whether the front end on computer two is linked using a mapped network drive or a UNC path.

Any ideas?

 

[Grady]

We are having the same problem! Did you ever resolve this issue? Also we are using a multivalued field are you?

Thanks

 

[MightyAC]

I have not yet resolved the problem. In this particular case I was able to use a different PC as the server to avoid the problem. However, it will come back to bite me in the ass again in the future no doubt.

In my opinion Access 07 has added many more headaches than advantages.

 

[Grady]

Ok thanks- ours I guess is a different issue that we have narrowed down to the multivalued field. Per Microsoft the Multivalued field and the Attachment field use 'pessimistic locking' no matter what you do! So each time two users are in that field (different records) the form and application locks up until one saves and the other has to close and go back in. It's a pain but apparetnly something we have no control over.

Thanks for the reply!

 

[SOS]

It's a pain but apparetnly something we have no control over.

Actually, you do have control over it. Do not use their multivalued fields and use a normal one-to-many record situation with a junction table and a subform. Then you have the ability to use normal record locking and it was the way you would have had to do it in all versions prior to 2007. The multivalue field is a big pain in the @$$ anyway and I think they should not have gone there.