Access DB size grows large with update

[xtreemnet]

Hi

I am updateing a access table with copying down a field as below:

Table1:

field1 fiedl2
aaa 12
21
52
def 20
36

and so on.
The data above is brought from multivalued database (UniData). I am updating the blank values field1 with the one above. My update routine is using a recordset and using update method. The routine runs OK. but the size of the database goes to 600mb. If I compact and repair it then it comes back to normal size of 26 mb.
Cannot figure out why it grows so much.
Any suggestions?

Thanks

 

[eman6]

I had the same experience.
It seems to me that Access is simply not built quite the right way.
I would modify the question, if you allow me, to:
How can I compact and repair the database programatically?
If I develop an application and deliver it to a client, I cannot ask the client to open the database and compact and repair it regularly.

Sorry that I am not giving a useful answer to your question, but actually enhancing it.

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[dilettante]

To a certain extent this is normal. New/changed data doesn't get added to the database by magic, it is done by writing and rewriting data pages and in many cases new pages need to be allocated to do this. Deleted/replaced data areas aren't immediately reclaimed.

There is no formula I'm aware of to tell you when to compact. The only really bad thing is not to do it at all. You might consider the extended Jet OLEDB property "Jet OLEDB:Compact Reclaimed Space Amount" though:

Microsoft OLE DB Provider for Microsoft Jet

Indicates an estimate of the amount of space, in bytes, that can be reclaimed by compacting the database. This value is only valid after a database connection has been established.

There are a few options to automate Compact & Repair. One is a setting in Access to cause it to C&R whenever the database is closed. Another is to schedule runs of Access with the /compact or /repair switch. Then there is the JRO extension to ADO, though this has been deprecated. You can also schedule runs of JETCOMP.exe against the database. Other methods include Access macros or low-level Jet calls to CompactDatabase.

Personally I favor JETCOMP runs myself. This is an unsupported free Microsoft download, and it always has done well by me. JETCOMP won't do anything about Access forms, reports, modules, macros, etc. that get fragmented due to repeated editing operations, you need to use the C&R functionality of Access to deal with those (this came about as of Access 2000).

See: Jet Compact Utility Available in Download Center

I just rename curr.mdb to backup.mdb and then run [tt]JETCOMP -src:"backup.mdb" -dest:"curr.mdb"[/tt] either using Windows Task Scheduler, automatically after large batch update runs, or via an action I provide to the user in my applications (for example a menu selection).

The tricky part can be getting all the users kicked off the database first.

 

[JerryKlmns]

Hello everybody,

just reference Microsoft ADO Ext 2.8 for DDL and Security
in your project (or your version 2.? of the "msadox.dll") and the code is

Dim je As New JRO.JetEngine
Dim C_R_E_Cnn As New ADODB.Connection
    
    With C_R_E_Cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = Data_Folder & Dbase
        .Properties("Jet OLEDB:System database") =  Data_Folder & Sys_Dbase
        .Properties("User ID") = PowerUser
        .Properties("Password") = PowerPassword
        .Properties("Mode") = adModeShareExclusive
        .Properties("Jet OLEDB:Engine Type") = 5
        .Properties("Locale Identifier") = 1033
    End With

    je.CompactDatabase  C_R_E_Cnn, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Data_Folder & "Compacted.mdb;Jet OLEDB:Encrypt Database=True"
    Kill (Data_Folder & Dbase)
    Name Data_Folder & "Compacted.mdb" As Data_Folder & Dbase

where Data_Folder is the full path to your database,
Dbase is your database name
Sys_Dbase is your .mdw file name
PowerUser with PowerPassword is a user with administrative rights on database and all objects

This routine Compacts-Repairs-Encrypts your DB. If you dont want encruption then delete this "Jet OLEDB:Encrypt Database=True". The compacted & repaired db is named
Compacted.mdb and then renamed to the original name.
I have noticed that when encryption is used, WinZip will not reduce file size!

 

[Sheco]

I just want to point out that when you compact an Access database, it seems to do this:
1. create a new temp database file
2. copy all records into new file
3. delete original file
4. rename temp file

The upshot of all this is that if you set filesystem permissions directly on the file itself then they are lost during a compact/repair transaction. The "compacted" file will only have whatever permissions it inherits from its parent folder. This can drive you nuts if, say, you are using the Access database on a web application, and you had set permissions for the IIS IUSR_MachineName account only on the file but not on its parent folder.

Also a minor concern appears if you have low disc space or a usage quota... you must have enough headroom for the temp file.

 

[xtreemnet]

Thanks guys.