By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Compress Access database using VBA

P: 1
Hi...,

I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

Thanks,
Narayanan
Dec 20 '06 #1
Share this Question
Share on Google+
8 Replies


missinglinq
Expert 2.5K+
P: 3,532
You can try compacting the db. If your app is truly bumping the 2GB limit, you need to split it into FE/BE and move the backend to SQL Server or
My SQL or some such.
Dec 20 '06 #2

Expert 100+
P: 1,892
I'm not an expert with Access, but this is some VBA that may help I've also seen some scripts that will auto compact your DB every 8 or so times it is open. Like stated above, if your DB is that big you may also want to consider a different DB solution or archive some of the items in your current DB.

HTH,
Aric
Dec 20 '06 #3

ADezii
Expert 5K+
P: 8,669
Hi...,

I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

Thanks,
Narayanan
Have you considered Tools, Options, General, Compact on Close. Selecting this
Option will automatically Compact the Database every time it is closed. Hope this helps..
Dec 20 '06 #4

NeoPa
Expert Mod 15k+
P: 31,615
Unfortunately,
...Access 2K onwards seems to be a bit crappy with its space usage.
...Anything anywhere near 2GB will take ages to compact.
...When a db gets anywhere close to the limit it is unable to execute various tasks without crashing.

All things to bear in mind.
You can split the back-end database into multiples or consider a different solution (SQL server etc).
Using a db that regularly reaches that sort of size is trouble.
Dec 28 '06 #5

Expert 5K+
P: 8,434
Unfortunately,
...Access 2K onwards seems to be a bit crappy with its space usage.
...Anything anywhere near 2GB will take ages to compact.
...When a db gets anywhere close to the limit it is unable to execute various tasks without crashing.

All things to bear in mind.
You can split the back-end database into multiples or consider a different solution (SQL server etc).
Using a db that regularly reaches that sort of size is trouble.
Hahaha... tell me about it! :)

A couple more things to keep in mind.
  • NeoPa, you recently posted a routine to do the compacting - how about dropping a copy (or link) here?
  • In my experience, although a close-to-2GB database may take some time to compact, it depends on what's in it. The more indexes, the longer it will take. And of course it depends heavily on the hardware. My work PC takes a few minutes.
  • There are lots of performance considerations when splitting you database into front- and back-end. But I expect you can make significant gains if the back-end goes into a "real" databsae (SQL Server, etc.). I didn't have that option. Splitting and using Access for both may be counter-productive. On the other hand, it may be possible to overcome this with some tweaking.
  • If you do split, you might save yourself some headaches by having any frequently-updated data in a separate database to static data, so the static data never has to be compacted. Database splitting is a complex subject though, and I'm certainly no expert in the area.
  • Have you looked into ways to reduce the size? For example, I was able to make some good gains by extracting repetitive text values to a lookup table. If you don't have Unicode compression on, I hear this can also make a big difference.
Dec 28 '06 #6

Zerin
P: 64
Dear members,

Got some information about Compact DB.You can go for:

http://support.microsoft.com/default...b;en-us;306287


Best regards,

Zerin
Dec 28 '06 #7

Zerin
P: 64
Hi...,

I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

Thanks,
Narayanan

Dear Narayanan G V ,

I am suffering by the same problem also and decided to switch my database into SQL Server.If possible, you can do it too.

Or just learn socket programming and threading by VB.After you finish learning, I think you'll get the idea.Other than that.........................!? I really don't know. May be exparts from the forum can help you.

Zerin
Dec 28 '06 #8

Zerin
P: 64
Hi...,

I am facing a problem with the Access database file once the size reaches 2GB (Access 2K)... Is there a VBA code which can enable the file to compress itself automatically when I complete running a module...

Thanks,
Narayanan

Hello,

You can read this also: http://www.rondebruin.nl/zip.htm

May be you can get some interesting information.

Zerin
Dec 28 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.