Compact and Repair database with VBA without Closing the Database? (1 Viewer)

[ions]

Dear MS Access Expert.

I know how to compact a database by using a hidden form and upon closing the database compact it if it is over a certain file size. Please see code below.

What I want is to check the size of the database every 30 minutes; If it reaches a certain size I want to compact the database without closing it.

I have a need to compact so often because I am continously importing / refreshing / deleting tables on a 1 minute timer. This is severly bloating the database very very quickly.


I can use the below subroutine to compact the database by closing it if it becomes bloated and compacting; then opening it up again... but there must be a better way? If there isn't I guess I must resort to closing and re-opening the database?

Public Sub CompactDatabase()
      'Run this code before closing the Database.
      Dim vStatusBar As Variant
      On Error GoTo Err_CompactOnClose
      
          If FileLen(CurrentDb.Name) > 2000000 Then
              Application.SetOption ("Auto Compact"), 1
              Application.SetOption "Show Status Bar", True
              vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
          Else
              Application.SetOption ("Auto Compact"), 0
          End If
      
Exit_CompactOnClose:
          Exit Sub
      
Err_CompactOnClose:
          MsgBox Err.Number & " - " & Err.Description
          Resume Exit_CompactOnClose

End Sub

 

[WayneRyan]

ions,

When Access does a Compact/Repair, it opens a new database --> db1.mdb

One-by-one it copies all tables/forms/modules/queries/reports/macros to the new database.

Then, if successful, it deletes the current database, then renames/runs the db1 to your
current database name.

I don't see how it can do that with a "live" database.

Wayne

 

[Rabbie]

What Wayne says is correct. You can only run compact and repeair if you are the only user of the DB. The DB will be closed and re-opened as part of the process

 

[ions]

Thanks for the great feedback.

What is the standard methodology used to implement what I want?

Every minute, I import an altered XML file into the current database. Prior to the import I delete all the XML tables from the previous Import process (which occured 1 minute ago).

The code for the above process is here.

 Public Sub ImportTankLevelXML()
      
     
      '*********************  Delete Device and Device1 tables. ***********************************
      
      If TableExists("Device") Then
      
            DoCmd.Close acTable, "Device"
            DoCmd.DeleteObject acTable, "Device"
      
      End If
      
      If TableExists("Device1") Then
            
            DoCmd.Close acTable, "Device1"
            DoCmd.DeleteObject acTable, "Device1"
      
      End If
      
      '********************** Import the XML 60 and 61 *******************************
      
      'DataSource:="http://192.168.100.60/index.xml", _

      Application.ImportXML _
      DataSource:="C:\Documents and Settings\Peter Jr\My Documents\Aimco\Development\Tank Level\Tanks_60.xml", _
      ImportOptions:=acStructureAndData

      'Import the second XML
      
      'DataSource:="http://192.168.100.61/index.xml", _

      Application.ImportXML _
      DataSource:="C:\Documents and Settings\Peter Jr\My Documents\Aimco\Development\Tank Level\Tanks_61.xml", _
      ImportOptions:=acStructureAndData

       '*********************  Delete fieldgate and param tables. ***********************************
      
      'Delete fieldgate and param tables.
      DoCmd.DeleteObject acTable, "fieldgate"
      DoCmd.DeleteObject acTable, "fieldgate1"
      DoCmd.DeleteObject acTable, "param"
      DoCmd.DeleteObject acTable, "param1"
      
      
      'Compact and Repair the Database.
      

End Sub

This causes significant bloating. After 1 day of deleting and importing XML at 1 minute intervals, I fear the database will reach an unstable size.

Thanks for your input.

 

[Kempes]

do users log out of your database overnight?

I have a batch script that runs in the night that checks for a .ldb, providing one does not exist, it then compacts the database every single night. It also makes a copy of the db and saves it elsewhere as a backup, then writes a 1kb txt file entitled success(dd-mm-yy) or fail (dd-mm-yy).

I can provide more detailed info if it is of any help to you....

or is it vitally important that the compact occurs more frequently throughout the day????

 

[ions]

Someone mentioned to me that I can directly Link to an XML via MS Access Link feature.

This would be the best solution if it's possible.

 

[boblarson]

Just curious as to why you need to delete the tables and not just the data in the tables. Then re-import into the tables. That would keep the database from ballooning in size.

 

[ions]

Hmm..... I still believe it would keep bloating no? Maybe not as much?

If not I guess I Could delete and append the new records. Direct XML link would be the best option however,

 

[boblarson]

Hmm..... I still believe it would keep bloating no? Maybe not as much?

If not I guess I Could delete and append the new records. Direct XML link would be the best option however,

Deleting and appending records is much more efficient and leads to WAY LESS bloat than deleting objects.

 

[ions]

Bob I have found a potential solution.

I have installed an Add in for Excel that enables Links to an XML source.

I then Link MS Access to the Excel file with the XML link.

Seems to work pretty well.

Now I just have to figure out a way to refresh the Excel file at regular intervals.

This method will eliminate bloating in my opinion but may cause other problems since there is a double link.

Do you have any experience with the above method?

 

[boblarson]

Sorry, I haven't done that one myself. Hopefully someone else can jump in with a solution for that.

 

[ions]

Thanks Bob ... appreciate your help.

 

[Peregrine]

Kempes,

Could you please post the code you described in your previous post about your batch script?

'I have a batch script that runs in the night that checks for a .ldb, providing one does not exist, it then compacts the database every single night. It also makes a copy of the db and saves it elsewhere as a backup, then writes a 1kb txt file entitled success(dd-mm-yy) or fail (dd-mm-yy).'

I have a similar task that this should work really well for.

 

[kdnichols]

Hello All,

I am trying this code in Access 2007 that you listed above:

[
Public Sub CompactDatabase()
'Run this code before closing the Database.
Dim vStatusBar As Variant
On Error GoTo Err_CompactOnClose

If FileLen(CurrentDb.Name) > 2000000 Then
Application.SetOption ("Auto Compact"), 1
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
Else
Application.SetOption ("Auto Compact"), 0
End If

Exit_CompactOnClose:
Exit Sub

Err_CompactOnClose:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_CompactOnClose

End Sub
]

I am getting a 424 error message Object Required.

Any other ideas or fixes.

I really need to get this Compact and Repair to work on close.

Access is notorious for this problem.

Any and all help is greatly appreciated.

Thanks,

Kurt

 

[GComyn]

I have several databases that I use that have 'temporary' tables, ie tables that hold data from import, then get moved to other tables, and the tables are emptied... what I do is I use a backend that is linked. The backend is deleted and remade every time the front end database is run, so the backend never gets too big, and the front end doesn't bloat.

GComyn

 

[kdbailey]

do users log out of your database overnight?

I have a batch script that runs in the night that checks for a .ldb, providing one does not exist, it then compacts the database every single night. It also makes a copy of the db and saves it elsewhere as a backup, then writes a 1kb txt file entitled success(dd-mm-yy) or fail (dd-mm-yy).

I can provide more detailed info if it is of any help to you....

or is it vitally important that the compact occurs more frequently throughout the day????

What version of Access do you use? I am looking for this exact thing, but have been unable to find the solution on 2007. I have written code within the database that automatically closes access if you have been inactive for an hour, so I do not need to check for a .idb.

 

[CJ_London]

Appreciate this is an old post but one of the ways to solve bloat on importing is to import data to a temporary db - and then import to your main db. The process is basically:

Every minute or frequency required (must admit I'm typically a once a day kinda guy!):
Delete tempdb if it exists
Make new tempDB
Import XML files (or whatever) to tables in tempDB
copy data from tempDB to main table(s) in BE
close tembDB

 

[kdbailey]

The update is once early in the morning but it is more complex than being able to just create a temporary DB.

The database that is being updated is tied closely with IT and I cannot change anything within it.

Basically the update is set in stone. I need a way to be able to compact and repair through vba that I can run after the update.

 

[CJ_London]

you should be able to adapt the code in the first post of this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=228573