Dan Waters
dwaters at usinternet.com
Tue Apr 14 12:14:02 CDT 2009
Hi Susan, The most important thing I do with Access backend files is to set up an automatic compact & repair. 1) Set your BE.mdb file to 'Compact on Close' in Options | General. 2) Create an Access database with a function called 'CompactBE' in it which gets initiated from an AutoExec macro. Add a table with one row and one field to store the full path to the BE.mdb file. Name this database AutoCompactBE.mdb or something. Put this code into a 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 stg = "SELECT BEFullPath FROM tblBEFullPath" Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot) stgPathBEFile = rst("BEFullPath") rst.Close Set rst = Nothing 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 Exit Function End If Set appAccess = New Access.Application '-- Open the BE file 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 DoCmd.Quit acQuitSaveNone Exit Function EH: DoCmd.Quit acQuitSaveNone End Function 3) On the server where the BE.mdb file is stored, create a scheduled task to open the AutoCompactBE.mdb file at a day/time when the BE.mdb file is unlikely to be in use. I've had good luck with this - no corrupt data for several years now at 4 separate customers. HTH, Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Tuesday, April 14, 2009 11:33 AM To: AccessD at databaseadvisors.com Subject: [AccessD] Keeping your db in tiptop shape I'm going to write about tasks that keep a db in shape -- the db doesn't have to be Access, but can be specific to Access or any database really. If it works out, I'll give credit where possible -- I like doing that and the readers like it too, but I can't promise anything. Susan H.