Multiple compacting database VBA

[frederik]

Hi,

I have a VBA-program in Access. Purpose of this program is to calculate statistics from individual data stored in Access tables.

Because of the large amount of data and data-manipulations, I need to compact the database very frequently. I would like to automate this.

My program works as follow:

Private sub startbutton()

Call function1
Call function2
Etc…

'Compact database -> all calculations are being done for year X, I need to 'compact to continue with the next year Y

CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("compact and repair database...").accDoDefaultAction

End sub
​

So far, anything works good. After the first compact, the mdb-file with the form restarts automatically. It should now automatically calculate statistics for year Y. So I repeat the same procedure as with the start button, but now under a load_form event:

Private sub load_form

Call function1
Call function2
Etc…
​

And now, here comes the problem:

CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("compact and repair database...").accDoDefaultAction

​

ð error message: Method ‘accDoDefaultAction of object _CommandBarButton failed.

It's bizarre that it does the compact the first time, but any other time, it gives errors.
Does anyone know any alternatives so I really can do multiple compactings?

I was thinking of using a second database which does the compacting, something like that: (all automatically)

1) calculate year X in original mdb
2) switch to db2.mdb which controls the compacting of original.mdb
3) switch to original mdb which continues calculating year Y
4) switch to db2.mdb which controls the compacting of original.mdb
5) switch to original mdb which continues calculating year Z
etc...

Is this possible in an automatic way?

Is there anybody who can help me?

Thanks in advance.
Frederik

 

[Rabbie]

remember the DB can only be compacted if you are the only user at that time. I found the easiest way to make sure the DB was compacted regularly was to follow this procedure from Access Help

Compacting does not occur if you close a shared Access database (multiuser (shared) database: A database that permits more than one user to access and modify the same set of data at the same time.) while another user has it open.

1. Open the Access database or Access project that you want Microsoft Access to compact automatically.
2. On the Tools menu, click Options.
3. Click the General tab.
4. Select the Compact on Close check box.

 

[frederik]

Thank you Rabbie.

What I actually need is that my Access program can continue to run after compacting. The mdb-file has to re-open automatically after compacting.
(just like it happens when you choose compact and repair from the menu Tools -> Database utilities -> compact and repair )

I COULD after the calculation of each yearresult close the database to compact and MANUALLY re-open it to continue with the next year. But I would like to AUTOMATICALLY have the process compact -> calculate year X -> compact -> calculate year Y -> compact -> calculate year Z -> compact -> calculate year A -> and so on...

Is there a way to do so?

Thanks in advance
Frederik

 

[datAdrenaline]

Do you modify the design of any objects with your code? ...

....

Aside from that Q, you should separate the data from the code. This is called a "Split" configuration. You will have one db that has nothing but the table schema (table, relationships, indexes), this is called the "Back End". Then you will have another db that has all the application objects (code, forms, reports, queries, macros, ANYTHING but 'raw' tables), this file is called the Front End. In the Front End, you then LINK to your Back End tables to create 'Linked Tables'. Linked tables work and act just like Local tables with respect to data manipulation.

By using linked tables, you can close all data bound objects in your Front End. like forms, reports and such, which in turn CLOSES your Back End, and thus allows you to Compact it with ease (as long as no one else has it opened).

To compact the back end, (remember to close all databound objects, as I mentioned!), Then rename the current Back End to a backup type name (note keep all the names in string type variables) ...

Name strBackEnd, strBackEndBackup

I will then Compact the BACK UP file to a destination file that is the same as the Back Ends original name ...

DBEngine.CompactDatabase strBackEndBackup, strBackEnd

Then after you compact, you can optionally delete the back up file ... but I tend to keep it just in case ...

Kill strBackEndBackup

...

By using the Split configuration, your application (Front End) never has to quit, or restart ... it is just manipulating data and the files that contain that data.


Hope that helps ...

 

[frederik]

Thank you datAdrenaline. You gave me good inspiration.
I do not modify the design of any object with my code.

I was already using linked tables to refer to the individual data (back-end). But to save the results of the data-manipulation, I use tables in the front-end. Maybe, there's my problem.

I will try to save the results in another database than the front-end.
Hopefully this will keep my front-end database 'clean', from where I can compact all other databases.

I will post the result here.

Frederik

 

[Pete666]

Try the module below... you can set the size that you want the database to compact at.. I have added an extra msgbox to allow the user to decide.

Public Function AutoCompactCurrentProject()

Dim fs, f, s, filespec
Dim strProjectPath As String
Dim strProjectName As String

strProjectPath = mydbPath
strProjectName = Application.CurrentDb.Name
filespec = strProjectPath & "\" & strProjectName

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)

'convert size of app from bytes to Mb’s
s = CLng(f.Size / 1000000)

'edit the 20 (Mb’s) to the max size you want to allow your app to grow.
If s > 10 Then
If MsgBox("The RM Management system is over 10Mb in size." & vbCrLf & vbCrLf & "If you are the only user in the database please click OK else Cancel.", vbOKCancel, "Compact") = vbOK Then
'compact app
Application.SetOption ("Auto Compact"), 1
Else
End If
Else
'no don’t compact app
Application.SetOption ("Auto Compact"), 0
End If

End Function

 

[datAdrenaline]

Hello Pete ...

Cool code! .. but it may be important to note that the code does not does not perform the compaction ... it sets the option to compact (or not) on db close.

 

[DCrake]

Simple Software Solutions

With regard to compacting issues and using front end / back end set ups. I have a tendancy to run 2 back ends from 1 front end.

In a well normalised situation the back end tables fall into 2 basic categories.

Data Tables
Lookup Tables

Data tables are those that change on a day to day basic and form the crux of the application.

Lookup Tables support the data tables and the front end and as a rule do not change on a day to day basis. Imagine if you have a national masterfile of GP's in your back end this one table could double its acutal size.

Placed this and similar ones into a Masterfiles mdb that the front end links to. Another good reason for this is when you want to update the masterfile you can do this without interfeering with the actual data tables back end.

The size of your data backend will reduce significantly and as such will increase effiency and performance.

CodeMaster:

 

[frederik]

My problem is solved.
I save the results now into a table of another mdb-file. (with linked tables). This file can be compacted when necessary, while the application keeps clean and runs continuously.

Thank you datAdrenaline and Dcrake for your input.

 

[datAdrenaline]

You are most welcome!