none
Compact database is running though the option is not selected RRS feed

  • Question

  • Hi

    Access 2013 32 bit

    When I close a database Compress in running, though the option is not selected?

    Why?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Monday, March 21, 2016 5:22 AM

Answers

  • Hi David

    Sorry for my bad English. By compress I mean Compact.

    The Compact is running fine. But I dont want the database to run the Compact operation every time I close/shut down the database. I now have solved it by creating a new empty database and thereafter imported all objects (Forms, Tables, Queries, Macros, Modules) from the old one.

    The new database runs and behave as I want it do.  In other words, I can control if and when the Compact operation should run


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, March 22, 2016 10:11 AM

All replies

  • Access 2013 32 bit

    When I close a database Compress in running, though the option is not selected?

    Why?

    Hi Peter,

    Did you use the debugger to see in detail what is happening on close?

    Imb.

    Monday, March 21, 2016 6:32 AM
  • Hi Imb

    Three Forms opened. I close them one by one and then shut down the database.
    Still the Compress is running.

    When I shut down the database with all three forms open there is one OnClose.

    The OnClose is like this:

    Private Sub Form_Close()
    OpenAllDatabases False
    End Sub

    Sub OpenAllDatabases(pfInit As Boolean)
      ' Open a handle to all databases and keep it open during the entire time the application runs.
      ' Params  : pfInit   TRUE to initialize (call when application starts)
      '                    FALSE to close (call when application ends)
      ' Source and Thanks to: Luke Chung FMS https://www.fmsinc.com/microsoftaccess/Performance/LinkedDatabase.html
        
      Dim X As Integer
      Dim strName As String
      Dim strMsg As String
     
      ' Maxantal tabelldatabaser på servern som skall länkas
      ' när detta skrivs är det Trading Base.accdb
      Const cintMaxDatabases As Integer = 1
    
      ' List of databases kept in a static array so we can close them later
      Static dbsOpen() As DAO.Database
     
      If pfInit Then
      ' Om kunden har valt att inte jobba med Trading Base i nätverk och låter bli att definiera sökväg
      If (Eval("[Forms]![Alla Val]![EgenPathExtra1] Is Null")) Then Exit Sub
        ReDim dbsOpen(1 To cintMaxDatabases)
        For X = 1 To cintMaxDatabases
          Select Case X
            Case 1:
              ' för att detta skall fungera ... gå till fliken Bas / Mappar mm och skriv in sökvägen till Trading Base
              strName = [Forms]![Alla Val]![EgenPathExtra1] & "Trading Base.accdb"
                  End Select
          strMsg = ""
    
          On Error Resume Next
          Set dbsOpen(X) = OpenDatabase(strName)
          If Err.Number > 0 Then
            strMsg = "Trouble opening database: " & strName & vbCrLf & _
                     "Make sure the drive is available. (du anger sökvägen under Bas / Mappar mm" & vbCrLf & _
                     "Error: " & Err.Description & " (" & Err.Number & ")"
          End If
    
          On Error GoTo 0
          If strMsg <> "" Then
            MsgBox strMsg
            Exit For
          End If
        Next X
      Else
        On Error Resume Next
        For X = 1 To cintMaxDatabases
          dbsOpen(X).Close
        Next X
      End If
    End Sub
    


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, March 21, 2016 9:35 AM
  • Three Forms opened. I close them one by one and then shut down the database.
    Still the Compress is running.

    When I shut down the database with all three forms open there is one OnClose.

    The OnClose is like this:

    Private Sub Form_Close()
    OpenAllDatabases False
    End Sub

    Hi Peter,

    What is the purpose of OpenAllDatabases? It looks quite a lot of overhead just to close a database. I would say  Application.Close  would be sufficient, or am I wrong?

    Imb.

    Monday, March 21, 2016 6:41 PM
  • Hi Imb

    The pupose is to create a persistent connection to the linked database, open a MS Access database variable in VBA using the DAO OpenDatabase method. Keep this variable open as long as your application is running.

    I got it from https://www.fmsinc.com/microsoftaccess/Performance/LinkedDatabase.html


    Microsoft Access Linked TablesSignificantly Improve the Performance of Microsoft Access Databases with Linked Tables

    Provided by: Luke Chung, FMS President


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, March 21, 2016 6:49 PM
  • Hi

    Even when I open the database and press Shift key during whole opening process (none of mine code or Forms running) the Compress runs when shutting down the database.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, March 21, 2016 7:24 PM
  • >>>When I close a database Compress in running, though the option is not selected?

    According to your description, what's mean Compress database? Do you mean compact database? if so, as far as I know that there are limitations of Compacting

    1. For the Compact operation to succeed, you must have enough storage space on your hard disk for both the original and the compacted database.

    2. You cannot compact an open database. In a multiuser environment, the compact operation is not successful if another user has the database open. 

    Note: In Microsoft Access, you can compact a database while it is open, as long as the database has been opened exclusively.

    For more information, click here to refer about Defragment and compact database to improve performance in Microsoft Access

    Tuesday, March 22, 2016 6:17 AM
  • Even when I open the database and press Shift key during whole opening process (none of mine code or Forms running) the Compress runs when shutting down the database.

    Hi Peter,

    Perhaps you have placed a tick at "Comprimeren bij sluiten" (Compress at close)

    Imb.


    Add: in the newly opened database.
    • Edited by Imb-hb Tuesday, March 22, 2016 7:39 AM Add
    Tuesday, March 22, 2016 7:36 AM
  • Hi David

    Sorry for my bad English. By compress I mean Compact.

    The Compact is running fine. But I dont want the database to run the Compact operation every time I close/shut down the database. I now have solved it by creating a new empty database and thereafter imported all objects (Forms, Tables, Queries, Macros, Modules) from the old one.

    The new database runs and behave as I want it do.  In other words, I can control if and when the Compact operation should run


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, March 22, 2016 10:11 AM
  • Hi Imb

    No I have not placed a tick at "Comprimeren bij sluiten"

    In Swedish "Komprimera vid stängning"



    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, March 22, 2016 10:20 AM