Compact on close or not?

[spikepl]

I have a DB with some tables, the contents of which are deleted (but not the tables themselves) each time new data is entered into the db. For this reason I have "Compact on close " ticked.

In this thread http://www.access-programmers.co.uk/forums/showthread.php?t=202759

missinglinq wrote:

Two things commonly considered to be causes of corruption in databases, even in the development stage, are Compacting & Repair and Name AutoCorrect.

Should one just let it balloon? Or what would the recommended operational maintenance practice be?

 

[SpentGeezer]

You should always make a backup before Compacting and repairing. My suggestion would be to create a macro that creates a backup then compacts and repairs on closing. The backup would overwrite the existing backup each time (so you don't balloon out your backup folder). Is this single or multi-user?

 

[spikepl]

This particular one is a single-user one.

 

[spikepl]

OK I found some code here and there, so I could automatically copy/backup the DB and retain Compact on close. What event should trigger it? I have a form that opens at startup, should I put the code in On Unload of that form? Or any other suggestions?

 

[SpentGeezer]

OK I found some code here and there, so I could automatically copy/backup the DB and retain Compact on close. What event should trigger it? I have a form that opens at startup, should I put the code in On Unload of that form? Or any other suggestions?

Can you add it to the same event that calls the compact and repair function so it backs up then compacts and repairs?

 

[ChrisO]

May I suggest you do not overwrite existing backups?

A database file can have a corruption which may not raise its ugly head each time it is used. Automating a backup which overwrites the existing backup would simply cause the backup to be corrupt as well. Disk space is cheap; keep the last 100 sequential backups.

Chris.

 

[missinglinq]

CrisO's suggestion is right on the mark! Corruption is a truly strange thing and it can, indeed, go undetected for a long time, just waiting to rear it's ugly head!

 

[Galaxiom]

The data should always be in a separate database from the front end even for a single user database. This helps reduce the possibility of corruption.

The case is even stronger where data is repeatedly imported then deleted. Best not to mix this data with the stuff you want to keep permanently. This temporary data should be written to yet another database which I have dubbed the "Side End". When I elucidated this concept here some time back a number of posters remained unmoved while at least one adopted it enthusiastically. (Search the site for "Side End" if you are interested in the debate.)

Compact on close will compact the front end only. With a well structured database (data not written to the front end) it won't really need compacting so there is no point to using Compact on Close.

However the Side End will bloat unless it is compacted. Just command it to compact from the front end. Alternatively use a disposable side end and regenerate it as required.

 

[boblarson]

Compact on close will compact the front end only.

Compact on Close set on the backend database will compact when the last person exits the backend (when their frontend no longer has it open). But it is not really a good idea to set it for that as it will potentially cause a problem if it is attempting to compact when someone else tries to open up their frontend and use it at the same time. Best to do that kind of maintenance off hours.

 

[Galaxiom]

Yes. The thought of someone getting into a database while it is compacting is the stuff of nightmares. I don't like the idea of Access doing it unsupervised.

When I compact I check that the backend is not in use (absence of the ldb file). Then I use the facility in AutoFEUpdater to lock users out. When the compactis completed I run the developer front end on it to make sure it is working before letting anyone even try to get back in.

 

[boblarson]

As an alternative manual process, I just rename the backend file while I compact it. So if it is named IRDB_BE.mdb I just name it IRDB_BEx.mdb and then someone can't connect to it while I'm doing it. Then I rename it back.

 

[spikepl]

Thanks for all the contributions. I am still stunned by the rather shocking news, that the "Compact & Repair" facility can actually "unrepair" my DB

In any case: for the moment I have a single-user unsplit DB. I back it up by calling some code from my main form's On Close handler. Each backup is tagged by date and time, so no overwrites. Bob's "side-end" idea is duly noted for the future.

Oh, if anybody needs code, then have a look below. The code I call On Close creates a Backup folder - if it does not yet exist - in the folder in which the db is located, and copies to it the entire DB, with a Backup_CurrentDate_CurrentTime tag added to the name of the db.

Public Sub BackupCopy()

    'This function will allow you to copy a db that is open,
    
    'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!
    
    Dim fso As FileSystemObject
    
    Dim sSourcePath As String
    Dim sSourceFile As String
    Dim sBackupPath As String
    Dim sBackupFile As String
    Dim sBackupFileName As String
    Dim sBackupFileExtension As String
    Dim f
    sSourcePath = CurrentProject.Path
    sSourceFile = CurrentProject.Name
    
    'create backup directory, if not yet exists, one level below current
    
    sBackupPath = sSourcePath & "\" & "Backup"
    
    Set fso = New FileSystemObject
    
    If Not fso.FolderExists(sBackupPath) Then
       Set f = fso.CreateFolder(sBackupPath)
    '   CreateFolderDemo = f.Path
    End If
    
    'get name and extension of current db
    
    Dim pos As Integer
    Dim i As Integer
    
    For i = Len(sSourceFile) To 2 Step -1
        If Mid(sSourceFile, i, 1) = "." Then
            pos = i + 1
            Exit For
        End If
    Next
    
    sBackupFileName = Left(sSourceFile, pos - 2)
    sBackupFileExtension = Mid(sSourceFile, pos, (Len(sSourceFile) + 1 - pos))
    
    
    sBackupFile = sBackupFileName & "_Backup_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & "." & sBackupFileExtension
    
    
    fso.CopyFile sSourcePath & "\" & sSourceFile, sBackupPath & "\" & sBackupFile, True
    
    Set f = Nothing
    Set fso = Nothing
End Sub

 

[DCrake]

Bit concerned that you are attempting to back up an open file as this may also copy the ldb file as well, not sure on that point. Also you really should have the database split between front end and back end as the main thing you want to back up is the data.

Let me give you an example why from a developers point of view as opposed to an end users point of view.

So today you make a backup of your mdb (unsplit database)
Tomorrow you find a flaw in the system and you spend quite a while identifying the problem and more time recoding your app to condent with this issue. You may also add a new form, a couple of new reports and lets say another 3 or 4 functions in a module.

Then you peform another backup

You then realise that for some reason you need to do a restore from a previous backup because of the data, not the programming. So you take yesterdays backup and restore it to its original name. Opps you have just overwritten all the amendments you made to the application. If you had only split the database and backed up the data only then this would not have happened.

Even if the application is a single user app at present you still need to split it. Further down the line it may become a multi user and as everyone knows - or should know - code that works in an unsplit application does not always work in a split application. For example using the .Seek method in recordsets.

Food for thought.

 

[spikepl]

Thanks for your concerns, but this app was never meant to be a multi-user app, due to the user requirements, and hence due to the way it is coded.

As to splitting it - yes, once it reaches production stage. The way the develop-test-get-feedback-get new-additions cycle runs for the monent, it would just become a huge pain in the butt. I believe I have navigated around the no-no's for a split application, but the proof of the pudding shall emerge in due time

 

[DCrake]

No one is saying that it is a 100% requirement that an application is split, however, seasoned developers will tell you that splitting at the beggining is the considered option.

If you are prepared to make amendments to your code, if you do use code, for parts that do not work in a split setup then ok, personally I would not want to. Time is money, is food on the table.

 

[spikepl]

Time is money, is food on the table.

Exactly. That is why letting it remain unsplit, for now, is the most efficient solution in this particular case.

 

[boblarson]

As to splitting it - yes, once it reaches production stage.

Just an FYI -

I split immediately upon starting building it. The reasons?

1. Splitting helps me know what is going to happen right now instead of seeing different performance, etc. between unsplit and split and then having to go figure out the fixes, instead of just knowing what is happening up front.

2. I usually have most of the table structure done up front so it isn't a big deal. Then I can concentrate on the other stuff. I don't like the idea of corruption hosing my tables as well as other objects, so once I know I have my tables mostly the way I want them - it is split.

3. Using the same backend I can deploy updated frontends to my testers while I continue to work on the frontend and their test data remains static so they can see the differences that other versions of the frontend make.

So, that's the way I approach it. Doesn't mean you have to as well, but I have given the reasons I do it this way, just in case you find something that may help.

 

[Galaxiom]

Bob's "side-end" idea is duly noted for the future.

Ahem. Without meaning to grandstand, the Side End was a concept (well at least a term) I introduced to this forum. As far as I can tell, the following quote was the first time I mentioned it. In fact it was in response to a comment by Bob about the need to compact front ends because of temporary tables.

I also split the front end from the local temporary tables (the SideEnd). IMO the same principles of splitting the program from the primary data should apply to temporary tables.

http://www.access-programmers.co.uk/forums/showthread.php?p=900437

I go into some detail about why it should be used an where to locate it.

After reviewing the posts that include the term, the principle of a Side End seems to have been accepted as a good practice by several resepected developers here.

I have little doubt the concept had already been used by some as a matter of course before I ever touched a database but I have never come across anyone promoting it outside the posts on this forum. Writing temporary data to the front end seems to be a quite common practice but hopefully not as common as it used to be.

 

[spikepl]

#17

I do not disagree with you, but the discussion of the issue is turning a bit pointless. Yes, your recommended practice IS admirable, and I would follow it in any other case. It would still just be a pain in this particular instance. The data structure is by no means fixed as we keep changing it, since new requirements emerge based on what we can do, and my customer's customers see new possibilities. I do not dictate this process, but have to live with it.

#18

My apologies for wrongly assigned credit.

 

[Galaxiom]

The data structure is by no means fixed as we keep changing it, since new requirements emerge based on what we can do, and my customer's customers see new possibilities. I do not dictate this process, but have to live with it.

Feature creep. The developer's nightmare. The best way to deal with it is tight original specifications and a clause in the contract that cost them big money for adding features later. Otherwise they will screw you an endless line of "can we just add this little thing".

Having said that, some data structures are far more amenable to extension than others. If you are faced with constant changes in specification and having to add field and table you probably should look at a different way to achieve it.

It might be too late to incorporate it efficeintly in this database but bear it in mind for future designs.

Hold the data structure in a table that allows new records to define apsects of the data that you are currenly adding as new fields. A general data table includes this value in one of its fields along with a foreign key to its related table allowing new features to be added without changing the table structure.

For example instead of adding another field to the customer table to record a new aspect of the customer, have a related table that stores many different aspects of the customer each identified by a field with a key to the name of the aspect being recorded.

These aspects are displayed in a subform in either continuous or datasheet mode so there is no need to alter the form when a new aspect is added.

Hope this makes sense.