Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compacting from a form

    Using Access 97 on WinNT
    I have made a database which makes a number of tables and needs to be compacted at the end of the macro. I searched through the help file and found a section about compacting the northwind database to Korean language, so I attempted to rip out the main compacting sections and put them in my database. The code I used is as follows:

    Sub CompactQuestion()

    Dim dbsQuestion As Database

    Set dbsQuestion = OpenDatabase("C:EDQuestionnaire.mdb")

    'Makes sure DB doesnt exist
    If Dir("c:EDQuestionnaireBCK.mdb") <> "" Then _
    Kill "c:EDQuestionnaireBCK.mdb"
    'Compacts DB
    DBEngine.CompactDatabase "c:EDQuestionnaire.mdb", _
    "c:EDQuestionnaireBCK.mdb"

    End Sub

    I noticed that the help file mentioned about it not working on an open database, I thought maybe the DBEngine.CompactDatabase routine would close the database as a normal compact does....I was wrong....I got the message:
    "Run-Time error 3356 You attempted to open a database that is opened exclusively by user 'Admin' on machine "MyMachine". Try again when the database is available"

    Does anyone know of a way to compact a database from a module run within it? Please note this is run from a MDB file as it is not a database that is used for data entry.

    Any help would be much appreciated.

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting from a form

    You might want to take a look at Dev Ashish site <A target="_blank" HREF=http://www.mvps.org/access/general/gen0013.htm>The Access Web</A> he has some suggestions and code samples that should apply. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting from a form

    Thanks very much Brian.

    For this database the Compact On Close option will do the trick - I didn't know that was there! However, i'll look into the other solutions as they might be useful for later databases.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting from a form

    The Compact On Close option available under Tools | Options | General tab as instructed by the access web doesn't appear under the tools option in Access 97 SR2.....or is it just me? Oh well, looks like i'll have to learn to use either the addin or the utility sooner than I thought.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting from a form

    You're right, compact on close is only available in 2000.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting from a form

    I realize the inherent evils of the SendKeys method, but I've found it quite easy to send keys to initiate the compact command found in the Tools Menu...Something like: SendKeys "^tdc"

    Just a thought.... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting from a form

    I thought the Access 2000 CompactOnClose option was good the first time I saw it. However when I was working with a large database I found it regularly took a few minutes for Access to shut down. Now I only compact manually.
    To automate compaction you could use a second database to start a second instance of Access with the /compact option applied to the database you wish to compact.

    The following code snippet could be of use:

    szTrgt = """" & "f:Program FilesOffice2kOfficeMSACCESS.EXE" & """"
    szTrgt = szTrgt & " """ & szPath & """ "
    szTrgt = szTrgt & " /wrkgrp f:winntsystem32zzzzzz.mdw /user xxx /pwd yyy /compact"

    ExecCmd szTrgt



    ///////////////////////////////////////////

    Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
    End Type

    Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessId As Long
    dwThreadID As Long
    End Type

    Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
    hHandle As Long, ByVal dwMilliseconds As Long) As Long

    Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
    lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
    PROCESS_INFORMATION) As Long

    Private Declare Function CloseHandle Lib "kernel32" (ByVal _
    hObject As Long) As Long

    Private Const NORMAL_PRIORITY_CLASS = &H20&
    Private Const INFINITE = -1&


    Private Sub ExecCmd(cmdline As String)
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ReturnValue As Integer

    ' Initialisieren der STARTUPINFO-Struktur:
    start.cb = Len(start)

    ' Starten der Shell-Anwendung:
    ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

    ' Warten auf das Beenden der Shell-Anwendung:
    Do
    ReturnValue = WaitForSingleObject(proc.hProcess, 0)
    DoEvents
    Loop Until ReturnValue <> 258

    ReturnValue = CloseHandle(proc.hProcess)
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •