Compacting the connected BE automatically.

[Guus2005]

This FE database runs a job overnight and i want to present the users with a clean compacted database (BE) in the morning.
But since the FE is connected to the BE how can i compact it?

Thx!

 

[DCrake]

Are you wanting to compact the front end or the back end? Why not simply tick compact on close in the settings. So when your overnight job has finished and the front end closes, this will close the back as well, if you are the only user on the system. Thereby firing the compact on close on the back end. Having said that I am not exectly sure that it will anyway you can code a compact routine on completion of the overnight job.

David

 

[Guus2005]

Are you wanting to compact the front end or the back end? Why not simply tick compact on close in the settings. So when your overnight job has finished and the front end closes, this will close the back as well, if you are the only user on the system. Thereby firing the compact on close on the back end. Having said that I am not exectly sure that it will anyway you can code a compact routine on completion of the overnight job.

David

The compact option is already turned on in the BE. It doesn't compact when you close the FE which was exclusively connected to it. No events where fired when connecting to the BE.

In the BE i have a macro which closes the BE database thus preventing the users to open it. When there are no users connected to it, the database compacts. Otherwise not.

I was thinking of deleting all links to tables in the BE, then try to compact it and reconnect. I was hoping of getting a better/easier solution.

Any ideas?

 

[DCrake]

Unfortunately I am on site today and do not have access to my main laptop but I am sure you can compact a backend mdb from the front end even if the links exist. Will try and dig out the code and test.

David

 

[Guus2005]

I tried disconnecting all connected tables and then using CompactDatabase trying to compact the database. That didn't work. The ldb file still locks the database.

I am afraid that i have to use a batch file which waits for a few seconds/minutes, close the database thus releasing the locks and then compact it using a commandline parameter.

I'd rather keep it all together and not use some external script.

I hope you have an answer

 

[DCrake]

Find attached a zip file that contains a demo of how to compact a database and zip it up afterwards. This can be commented out but the code is on there to compact the BE. There is some documentation with it.

Hope this helps

David

 

[Guus2005]

Unfortunately this works only if the to be compacted database was not connected. The problem remains.

Nice zip functionality though.

So i am still searching.

 

[DCrake]

I just tested this with a connected mdb and it worked for me.

               'Compact the database to a temp name
                DBEngine.CompactDatabase "C:\Databases\SSPSdata.mdb", "C:\Databases\SSPSdataOld.mdb"
                DoEvents
                
                'Check to see if the file has been created
                If Dir("C:\Databases\SSPSdataOld.mdb") <> "" Then
                    
                    'Delete the original mdb
                    Kill "C:\Databases\SSPSdata.mdb"
                    'Run the compact again back to its original name
                    DBEngine.CompactDatabase "C:\Databases\SSPSdataOld.mdb", "C:\Databases\SSPSdata.mdb"
                    DoEvents
                    
                    'Delete the old copy
                    Kill "C:\Databases\SSPSdataOld.mdb"
                    DoEvents
                Else
                    'Did the compact work?
                    If MsgBox("A problem occured creating the initial compacted file." & vbCrLf & vbCrLf & "click OK to continue without compacting the database or Cancel to exit the backup facility.", vbExclamation + vbOKCancel, "Problem occured") = vbCancel Then
                        Exit Function
                    
                    End If
                End If

This is crutial part of the code. I linked all the tables from the SSPSData.mdb to a front end which was well bloated. I then ran the above without error.


David

 

[Guus2005]

DCrake, this is the code i use:

Public Function CompactDb(strDbName As String) As Boolean
    Dim dbs As Database
    Dim strCurdir As String
    Dim strDbNw As String
    Dim strNewName As String
    
    On Error GoTo CD_Error
    
    'Assume success
    CompactDb = True
        
    Set dbs = CurrentDb
    
    strDbNw = "CompactNw.mdb"
    strCurdir = Mid(strDbName, 1, Len(strDbName) - Len(Dir(strDbName)))
    strNewName = strCurdir & strDbNw
    
    If FileExists(strNewName) Then
        Kill strNewName
    End If
    
    DBEngine.CompactDatabase strDbName, strNewName
    DoEvents

    'Check to see if the file has been created
    If Len(Dir(strNewName)) > 0 Then 'Delete the original mdb
        Kill strDbName
        'Run the compact again back to its original name
        DBEngine.CompactDatabase strNewName, strDbName
        DoEvents 'Delete the old copy
        Kill strNewName
        DoEvents
    Else 'Did the compact work?
        CompactDb = False
    End If

Exit_CD:
    Exit Function
CD_Error:
    CompactDb = False
    ErrorProc Err, Err.Description, "CompactDb", "modUtilities"
    Resume Exit_CD
Resume
End Function

This code doesn't work because the BE database is locked by another process. Even if all the forms are closed. Some old link is keeping the BE database active connected.

I have almost run out of ideas, can you help?

 

[HiTechCoach]

Sounds like you need to make sure all the users are logged out.

See:
Detect and Logoff Idle Users
Detects and ejects idle users after set time period.

But since the FE is connected to the BE how can i compact it?

I would use a different front end that doe snot open any field int he database to be compacted.

 

[Guus2005]

All the users are logged out, i am the only one logged in.

The program uses the switchboard and the user is logged on entry.
In the background i use a hidden form to throw all users out. I am confident no one is logged in. (the application is still in test phase)

Still the code doesn't work.

Any ideas?

 

[DCrake]

as a matter of interest are you checking for the existance of the ldb file before attempting to compact? If is does not exist then there is no reason why it should not compact.

David

 

[Guus2005]

The ldb file is there. I am logged into the FE which makes contact with the BE. My presence is noted in the log.

I will try to create a bat file which waits a few seconds, then opens the backend. Because the backend closes directly, the BE will be compacted. The problem is now to wait long enough before opening the BE.

This solution is not what i wanted, i don't even know if it will work. But it is the best i can come up with.

So now i am searching for a command that waits a few seconds/minutes.

 

[Zigzag]

Why don't you stick with the first part by closing down all users at a known time and then get windows task scheduler to open the backend a minute after that?

This is method that I have used before.

Garry

 

[DCrake]

 Dim db 
   Dim strDbName
   dim strPassword
   dim strMacro
   strMacro = "YourMacro"
   strdbName = "c:\yourpath\yourDB.mdb"
   strPassword = "YourPassword"
   Set acc = createobject("Access.Application")

   ' this temporarily changes the macros security to low to avoid messages.
   'onlt works with access 2000 onwards
   if acc.syscmd(7) >= 10 Then
	acc.AutomationSecurity = 1
   End if
   
   'if you do not need to see Access delete this line
   acc.Visible = True

   Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=" & strPassword)
   acc.OpenCurrentDatabase strDbName
   acc.docmd.runmacro strMacro
   db.Close

Here is an example of some vbscript that you could modify to open your back end via a scheduled task and if you have the compact on close set to true it will compact itself.

David

 

[Guus2005]

@zigzag, @dcrake,
Both contributions are helpfull. But i don't think the task scheduler is accessible to me.
I'll try and get back to you when i am back from the holidays.

Thx for your time!

 

[Guus2005]

When all calculations are done, before the application ends, this vb script is started.

Dim db 
Dim acc
Dim strDbName

WScript.Sleep 300000 '5*60 sec

strdbName = "C:\Temp\ContData.mdb"

Set acc = createobject("Access.Application")

'if you do not need to see Access delete this line
acc.Visible = True

Set db = acc.DBEngine.OpenDatabase(strDbName)
acc.OpenCurrentDatabase strDbName
acc.docmd.runmacro "CompactMe"

db.close

Windows Scripting Hosts reports error: You can't carry out this action at the present time, Code: 800A09B6, Source: (null).

But the Task Scheduler works. On a schedlued time interval it opens the database and closes it. On closing the database is compacted. Finally.

I would rather accomplish it by using code i can control.
Now i have to create a task on the clients login.

I will try to figure out if i can create a Task using VBA.

Thanks!