[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