[AccessD] Access Lock File problem

Thank you all for your suggestions!  I'm going to try using the 'compact on
close' option that Dan suggested.  If I still hit lock file problems I will
try the process explorer - I didn't realize that I could use that to track
down a lock file.  If the problem continues I'll apply at Burger King.
Flipping burgers has GOT to be more fun than IT Admin stuff...

Thanks again!

Janet



On Wed, May 27, 2009 at 12:19 PM, Dan Waters <dwaters at usinternet.com> wrote:

> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>