Darryl Collins
Darryl.Collins at coles.com.au
Tue May 20 20:54:19 CDT 2008
Dan, Very nice. Thanks for this. One for the code archives. cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Dan Waters Sent: Wednesday, 21 May 2008 12:13 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Autonumber problem You can set up a method to automatically compact and repair a BE daily. 1) Pick a time of day where it is least likely that users will be in your database. Say 3:00 AM. 2) Create a small database titled BECompact.mdb with the following code in a standard module: '------------------------------------------------------------------------ Option Compare Database Option Explicit 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 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 '-- pause code execution for 5 seconds '-- When the BE closes it will auto-compact. appAccess.CloseCurrentDatabase Sleep 5000 '-- pause code execution for 5 seconds DoEvents appAccess.Quit acQuitSaveNone Set appAccess = Nothing Access.Application.Quit acQuitSaveNone Exit Function EH: Access.Application.Quit acQuitSaveNone End Function '------------------------------------------------------------------------ 2) Set the above code to run automatically using an AutoExec macro or a startup form. 3) Add a table (tblBEFullPath) to BECompact.mdb with one field (BEFullPath) and enter the full path to the BE file. 3) Save BECompact.mdb on the server in the correct location. 4) Open the BE file and go to Options. Check 'Compact on Close'. 5) Create a Scheduled Task on the server which will open BECompact.mdb at the time you have selected. 6) Test the Scheduled Task and BECompact.mdb. This will now automatically compact your BE file automatically every day. HTH! Dan -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material.