Compact database is running though the option is not selected

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Significantly 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

Answer 5

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

Answer 6

>>>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

Answer 7

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.

Answer 8

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

Answer 9

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