Solved: Advice for Reducing Database Size?

[GaryB]

Hi,


I have a database that has been running for the last 5 years. It has grown to about 68 MB and I'm getting a little concerned about the size. I tried creating a new database and importing tables, queries, forms etc... into the new one, but, when I finished, any code that had been attached to an [Event Procedure] didn't make the trip. Not only did I lose all the code, it also would not let me even get into any code. So my question is kind of 2 fold.

1. Does anyone have any input how I can reduce the size of the database?

2. Any ideas why the code disappeared?

As always, any help is greatly appreciated.

Thanks in advance,

Gary

[stanl]

Hi,

1. Does anyone have any input how I can reduce the size of the database?

Gary

In my experience, we've looked for an archiving schema. Usually a date field, where we could offload records before a specific date; logical fields, i.e. records flagged as inactive. If ever needed again, the data was easily retrieved. But, without describing what is in your database, it is hard to give a simple answer. And I assume you regularly issue Compact and Repair.

.02

Stan

[GaryB]

Hi Stan,

Thanks for the reply. Yes, I do compact and repair on regular basis. The database contains records of job folders, inventory items, purchase orders, delivery receipts etc..
all of these items have records dating back 5years. Most of them we use as reference for new orders coming in. When I created the new DB and imported the files, the size of the DB diminished by 20MB, but as I stated in my post, none of coding made the trip over. Definitely has me baffled!

Gary

[XL-Dennis]

Hi Gary (and hi Stan) :hi:

In my experience the compact and repair can have a negative impact on the peformance of the DB.

A general recommendation is to separate code and data, i e use two databases. Easy to say but difficult to follow.

In my opinion simple textfiles may be useful to store historical data and offload the DB in use.

Are you unable to access the code in the present DB? Sounds weird and may indicate seriously problems with it... It propably explain why You are not able to port code etc to new DB.

Kind regards,
Dennis

[GaryB]

Hi Dennis,

No the present db works fine. I do have this spreadout into 3 databases. One is an order entry database that holds things like the job folder, deliver recpt, p.o order info etc.., another is for customer info and vendor info and the third one is dealing with a company wide calendar. I linked them them all to may main db and the main db has all the forms, queries and reports in it and even with spreading things out it still is on the large side. Everything is working fine, I'm just doing preventative stuff now while it all works.

Gary

[XL-Dennis]

Gary,

Sounds good :)

Hm, it become more mystery why you can't port the code. I'll digg into it and see if I can create a similar situation.

Kind regards,
Dennis

[xCav8r]

I would recommend trying to import every element of your database into a new blank file a few more times if you haven't already done so. The modules should be coming along for the ride. It could be that some or part of your database has been corrupted. If that's the case, you should back it up and try decompiling it. If that doesn't work, you may need to do it manually. Uffda!

I divide all Access applications into two major components: a back-end and a front-end. The back-end contains 99% of the tables and nothing else. The front-end contains everything that the back-end doesn't have--things like queries, forms, reports, code, etc. I further divide the front-end into a master version and a prototype version. The master, though regularly compiled, compacted, and backed up, is used strictly for the creation of MDEs. All development is done in the prototype version, and when things like forms, reports, pages, and new modules are ready for production, they are imported from the prototype into the master. (Incidentally, the prototype version is connected to a prototype back-end as well--recreated with some frequency by copying the production back-end.) The measure of the size of my Access application, therefore, is always the sum of the back-end and MDE front-end. Also, it's helpful to consider the sizes of these components discretely when thinking about upgrading.

PS. I edited the thread title because it was in ALL CAPS.

[GaryB]

Good Morning Xcav8r,

The first thing I did was import to a blank file - 3 times so far with various problems every time. It seems most of them are connected to event proceedures and/or macros. I am in the process of doing it "ugh!!" manually and I have to admit this is a real pain. What I might try doing is all the tables and queries via import and the forms and macros one at a time. What you described for front end and back end is very close to what I have done and I too use a prototype for design and testing. Thanks for the input.

Gary

[xCav8r]

Have you tried backing it up and decompiling it?

[GaryB]

No, that's the next thing on my list. Not sure when I can get to it at this point.

Gary

[xCav8r]

It doesn't take much work. Duplicate your application, and then open the duplicate with the decompile switch (http://www.mvps.org/access/bugs/bugs0008.htm) and let it do its work. It's certainly worth a try and might save you a lot of time of manually exporting your forms' class modules. If it does fix the problem, it will also help keep your application compiled on future compacts, if that's something that's not happening.

[GaryB]

Yes, I am definately trying it first before I manually move anything.

Thanks again and again and again and again - Ha!

Gary

[GaryB]

Ok,
Did the backkup and decompile - pardon the next stupid part of this but, now what?

Gary

[xCav8r]

Still won't import the forms' class modules to a new db after decompiling?

[GaryB]

Ok,

Got it! Too dense sometimes! Let me give it a try later today and I'll post the results.

Thanks

Gary

[GaryB]

arrrrrrrrrrrrrrgh!!! Opened a new database, imported tables, queries,forms,macros etc... and the *&^%* database doubled in size!!! The code worked, the macros worked but the issue is reducing the size of the database. I did another compact and repair and it did reduce it about 20MB, so maybe that's all there is!!

Gary

[xCav8r]

That's because it was decompiled. BTW, we talking about the cumulative size of your components or just the application being 60 odd megs?

[GaryB]

It did the same thing to me yesterday before I decompiled it. Just the application size. I have been highlighting the mdb file in the directory and bringing up properties. The reason I'm worried about the size is based on an estimating program we bought a few years back that was built in Access and the programmers for the company we bought it from said that Access had a limitation of 20MB for a database before it started having problems. Since I'm up over 60 on my DB I was just being cautious. Make any sense to you?

Gary

[geekgirlau]

Gary, the problem you mentioned with importing has to do with the verions of a particular dll - have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;304548.

[GaryB]

Hi Geekgirl,

Thank you for the dll tip. I just read throught it. It looks like a lot to do to fix it. I did a search and found the dll - Vbe6.dll that the article mentions. Let me ask a very stupid question. Is there a limitation to size a database can be in Access 2000?

Gary

[geekgirlau]

2002 has a size limit of 2GB :rofl: but I wouldn't expect to be able to get anywhere near that size.

In my experience, the limit depends on the individual database, and you have to make a judgement as to when performance is being adversely affected.

[GaryB]

Something that came to mind as I was reading through the support pages. I never got an error message in any of this. It just stopped working. Not only did my command buttons stop functioning, but, even the built in close buttons wouldn't work. I had force quit the program to get out of it.

Gary:banghead:

[GaryB]

2GB and my DB is 60MB and working fine. Maybe I'm being over protective, but, now that we have experienced this problem with importing and code not working, I feel obligated to see it to the end and try and help figure it out. Now 2GB is for the 2002 vers, but, does anyone know what the 2000 vers limit is?

Gary

[GaryB]

I want to thank everyone for their help on this. I retraced my steps and started over in a new blank database-right clicked on table and imported from my existing database, leaving out all he mysys tables - did the same steps on queries, forms, macros and modules. Compiled the database and then ran compact and repair database and everything is working and I reduced the size by 20MB. I am Marking this solved. Once again, thank you to everyone for their input.

Gary