[AccessD] Autonumber problem

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.