Objects Locked by Users in Access 2000 1

[jaycast]

We have an application in Access 2000 that locks ALL objects within it if at least one other user has it open. That makes it really difficult to develop new objects or make changes to existing objects that aren't currently being used.

I've done searches for this type of problem, but all I get back are posts on full database locking and record locking.

Anyone have any idea on this?

Thanks

 

[LittleSmudge]

Yes - thats usual.

What you need to do is :-

Split the Database FrontEnd and BackEnd
Allow users to work on one copy of FrontEnd
You do development work on another copy of FrontEnd
Then when you want to publish your development you just replace their FE with yours



'ope-that-'elps.


( If anything in the above isn't clear - just ask )




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[jaycast]

*winces* Yeesh, I was hoping I could avoid that. The logic makes sense and is sound, however since the database app has already been created and has been in production for years, splitting it doesn't seem to be easily accomplished.

Is there any other way? Say it ain't so!

 

[LittleSmudge]

Oh splitting a database to FE and BE is easy-peasy-lemon-squeeze.

Its a 5 minute job for even the biggest db.

1 Kick off all the users
2 Create a backup copy
3 Create a copy called XxxxFE.mdb
4 Create a copy called XxxxBE.mdb
5 Open the XxxxBE.mdb and delete all Queries, Forms, Reports and Modules
6 Compact XxxxBE.mdb
7 Open Xxxx.FE.mdb and delete all Tables
8 Compact XxxxFE.mdb
9 Whilst still working in the FE use File Menu, Get External Data, Link Table to link all of the tables in the BE to the FE
10 Job Done


Set 5 is usually the most laborious and is actually optional
If you skip it you just end up with a bloated BE that contains Forms and Reports etc that are never used.



'ope-that-'elps.


(It should take you less time to do than it's taken me to type ! )



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[jaycast]

Lol...ok, that all makes sense. What about Make Table action queries? If the query objects are resident in the front-end and they are called, wouldn't they proceed to make a new table object in the front-end rather than the back end?

I think that's my biggest concern.

 

[LittleSmudge]

Yes they do.

But think about Why you are using make Table action queries in the first place.

( You MIGHT be different but .. )
It is usually to make temporary tables that are of interest to one user and for the current session only ( Otherwise you'd have stable table designs and use Append action queries.)

So if these MT queries make tables in the user's local front end and then presumably delete them again later - then that's fine. All continues to work as before.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[claytonjgordon]

Couldn't you also just link the MT to the BE if you wanted to share the data?


Dominus Nihil
(Master of Nothing)

 

[LittleSmudge]

Interesting point Clayton.

Yes you can modify each of the MT queries so that they write to the central BE database if you NEED to.

Use the format :-
SELECT F1, F2 FROM tblSource INTO MadeTable IN '\\Server\Path\TargetBE.mdb';



It all depends on how many MTs you have and whether you NEED to change them.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.