[AccessD] Access Lock File problem

Janet,

I'd suggest automatically compacting the BE.  The code below will open your
BE file, and then close it.  If the BE file has Compact on Close checked, it
will be compacted when it closed.

1) In your BE mdb file, check 'Compact on Close'.
2) Create a new Access database called AutoCompactBE.mdb.  Put it in an
appropriate place on your server.
3) Create a standard module in that database.
4) Paste in the code below.  Enter in the path to your BE.
5) From the code window, check the reference for 'Microsoft Scripting
Runtime'.
6) Create an AutoExec macro that runs the CompactBE function.
7) Create a Windows scheduled task to open the AutoCompactBE.mdb file at a
time each night when no one else is logged in.
8) Test!

Code for standard module:

'----------------------------------------------
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function CompactBE()
On Error GoTo EH

    Dim stgPathBEFile As String
    Dim stgPathBELDB As String
    Dim appAccess As Access.Application
    Dim fso As FileSystemObject
    Dim stg As String
    Dim rst As DAO.Recordset

'    Stop
    
'    stg = "SELECT BEFullPath FROM tblBEFullPath"
'    Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot)
'    stgPathBEFile = rst("BEFullPath")
'    rst.Close
'    Set rst = Nothing

    stgPathBEFile = "C:\Folder\Folder\YourBackendFile.mdb"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    '-- If the BE file is in use then the BE can't be compacted
    stgPathBELDB = Replace(stgPathBEFile, "mdb", "ldb")
    If fso.FileExists(stgPathBELDB) Then
        Access.Application.Quit acQuitSaveNone
        Exit Function
    End If

    Set appAccess = New Access.Application
    
    appAccess.OpenCurrentDatabase stgPathBEFile, False
    
    Sleep 5000
    
    '-- When the BE closes it will auto-compact.
    appAccess.CloseCurrentDatabase
    
    Sleep 5000
            
    appAccess.Quit acQuitSaveNone
    Set appAccess = Nothing
    
    DoEvents
    
    Access.Application.Quit acQuitSaveNone

    Exit Function
    
EH:
    Access.Application.Quit acQuitSaveNone
    
End Function
'------------------------------------------------

Good Luck!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Janet Erbach
Sent: Wednesday, May 27, 2009 11:25 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Access Lock File problem

Dear Forum Folks:

We have an access order management application here that has a front-end
installed on 10 computers and a back-end that resides on the server.  We
compact/backup the back-end mdb every day - usually with no problem.  But
the compact/back-end, of course requires that only ONE computer can be
accessing the back-end mdb, and every once in awhile the person running the
compact forgets to close out the app on one of the computers.  When that
happens it seems like the lock file gets 'locked' for good;  even after
making sure EVERYONE is out of the app, access thinks the mdb is locked.

The only solution I've found so far is to re-boot the server and then delete
the lock file.  This seems like an in-elegant and time-wasting solution to
me;  is there some setting I'm missing in Access that's causing this issue?
Is there some other way I can release the ldb file without having to
re-boot?

Thanks!

Janet Erbach
IT Prognosticator
Natural Healthy Concepts
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com