Record Locks (Well... maybe?)

[gray]

Hi All

WinXPPro
Access 2002 SP3
plus Access 2007


Background
---------------
I have split my database into a Front-End (FE) / Back-End (BE) configuration. The BE contains Tables only. Both the BE and FE have the following options set under the Advanced Tab in Tools/Options :-

Default Open Mode = Shared
Default Record_locking = No Locks
Open Databases Using Record Level Locking = Unchecked

I use my own record-locking mechanism to ensure users cannot edit/delete a record whislt it is being used by someone else.

My FEs use Linked Tables and a Macro runs upon opening of the FE to re-set all the linked tables to the BE.

I have created two identical copies of the FE... lets call them FE(Loc) and FE(Rem) Loc = Local to BE, Rem = Renote to BE.
FE(Loc) resides on the same machine as the BE (but in a different folder), FE(Rem) resides on another machine. Both machines (hosts) are running Windows XP. The BE Host uses Access 2002. The FE(Rem) Host uses Access 2007. Both hosts reside on the same 100mbit network. The only firewalls between the hosts are the native XP firewalls of the hosts themselves.

My Main form has several comboboxes and subforms whose control and recordsources I manipulate using VB... so they are bound dynamically rather than 'hard-wired'

The Problem
----------------
I can connect both FE(Loc) and FE(Rem) to the BE and I can happily create, edit, delete records as req'd from both FEs. The problem arises when I try to use them simultaneously and in a particular fashion.

If I open FE(Loc) but do not open my main form, the FE(Rem) will happiliy link its tables to the BE on startup within seconds. HOWEVER, if i open the main form on the FE(Loc) and then try to start the FE(Rem), the Table-Links module on FE(Rem)takes 5/6 mins to run to end as opposed to a few seconds. Obviously, this is not acceptable to an end-user.

CURIOUSLY, if I do this in reverse... that is open FE(Rem), then open my main form on it and then start the FE(Loc), the FE(Loc) happily fires up its links within 2/3 seconds.

Is this a record locking issue I wonder ? Any ideas how I can fix the issue please?

Thanks

 

[RossWindows]

This is a curious problem indeed. Have you tried adding a break in the linking code and stepping through it? This can norrow down the problem to a single line of code in most cases. Also, if you have error trapping in your code, comment it out and see if Access throws any default errors. If all else fails, and nobody else can reproduce the error, you may need to post your code and/or database files.

 

[neileg]

Not sure what is happening here but it's a better approach to only use a local FE, not a shared FE on the server.