How to Solve Bloated Access 2007 DB

[flyinghippo99]

Hi All,

So, I've built this fancy db app with nice frontend and it does a bunch of CRUD operations , etc. etc.

What I've noticed is that the db gets FAT and BLOATED very quickly. I tested it by viewing the data in various slices and the db grows by 2-3x in matter of minutes. If I continue doing the various view slices who knows how big it will get...

Now, to give some context, my view operations are bit complex/convoluted involving a bunch of union queries, intermediate tables creation(hundreds but they are slim one column wide) then combining it all together in a final view. Perhaps, this
is where the bloating is coming from(?).

I set the db app to compact the db after each exit of the program. It doesn't help. The bloating continues. So, I always keep an original non-bloated(or rather less
bloated) version which I copy over after my testing. Obviously, this is not a workable solution in the long-term since the user will start using it and it will really get bloated. This is even before splitting it FE/BE.

So, the question is this an intrinsic nature of Access? That Access naturally gets bloated over time with usage? Or is this a function of my complex queries/table creations/ADO/VBA/DAO stuff? Or some combination thereof? And most importantly, how to fix it?

Please advise.

Thanks!

flyinghippo99

 

[boblarson]

1. Are you adding images or attachments?

2. Are you doing any Make Table Queries?

3. Are you doing a lot of deleting and appending of data?

You should split it so you can also see which parts are more prone to the bloating.

 

[flyinghippo99]

1. Are you adding images or attachments?

2. Are you doing any Make Table Queries?

3. Are you doing a lot of deleting and appending of data?

You should split it so you can also see which parts are more prone to the bloating.

Bob,

1) No

2) Yes, a lot of them

3) Let me think. Delete a lot? NO. Appending a lot of data? Yes!

So, how should i optimize or rewrite to decrease geometric growth in size and bloating?

thanks!

flyinghippo99

 

[John Big Booty]

You could do a regular Compact and Repair on your DB

 

[Galaxiom]

Firstly split your database into three sections. The FrontEnd, the BackEnd and the SideEnd.

The SideEnd is another local database and is used for the temporary tables. Some deveolpers write temporary tables to the FE but it is a bad practice.

Hundreds of temporary tables just to view the data? It sounds very likely that your data is not normalized.

 

[flyinghippo99]

You could do a regular Compact and Repair on your DB

thanks for the suggestion. I have it compact & repair after quitting the application.

The bloating still persists...

 

[flyinghippo99]

Bob,

1) No

2) Yes, a lot of them

3) Let me think. Delete a lot? NO. Appending a lot of data? Yes!

So, how should i optimize or rewrite to decrease geometric growth in size and bloating?

thanks!

flyinghippo99

I take 3) back. There are lots deletes too and lots of appending data...

 

[flyinghippo99]

Firstly split your database into three sections. The FrontEnd, the BackEnd and the SideEnd.

The SideEnd is another local database and is used for the temporary tables. Some deveolpers write temporary tables to the FE but it is a bad practice.

Hundreds of temporary tables just to view the data? It sounds very likely that your data is not normalized.

SideEnd! Sounds interesting. Never thought of that. So, the FE would do the intermediate calculations in the SE db? And where would this SE sit? FE is on the user's desktop. The BE is on the network. Should the SE be on the user desktop or on the network?

As to why I have hundreds of tables, it's a bit complex and long story. It's part of a solution I devised to allow heterogenous data types(e.g. NAs and numeric) together in one view. Until I can come up with a better "methodology" to do this, this is the way it's going to be. How else can you combine different data types in one column?

 

[Galaxiom]

SideEnd! Sounds interesting. Never thought of that. So, the FE would do the intermediate calculations in the SE db? And where would this SE sit? FE is on the user's desktop. The BE is on the network. Should the SE be on the user desktop or on the network?

The Side End is like a scratch pad for the FE. It is on the client computer.
I compared a couple of different locations here:

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

My preferred location is user's Local Settings folder. However the Front End needs to generate this database on demand if the user has a roaming profile on a domain because Local Settings is deleted entirely at Logoff.

The tables also need to be linked dynamically.

 

[flyinghippo99]

hi All,

Is there a size limitation on how many rows an Excel 2007 spreadsheet can have when importing to Access 2007? I have about 9300 rows of data in Excel(not big) and the eventual size will be much bigger. The file size is not even a 1000 kb.

But even now, it's taking forever to import just 9300 rows of Excel data. Geez. I did it through the import wizard as well as through the TransferSpreadSheet function, which normally works like a charm.

In fact, it's still hasn't completed. I killed the process the first few times. But now I'm going to let it run to see how long it takes to import. Something is wrong... Shouldn't take this long...

Any ideas/suggestions?

thanks!

 

[NigelShaw]

Hi

I recently had serious gloating issues, it turned out to be sn allergy to mushrooms fnar.....

Seriously though, I had a reference to a flexgrid and in a rush, I copied it to another form rather than create a new one. The result somehow was my db boosting from 3 mb to a massive 350 mb in hours. More interesting, a copy of the db also inflated other closed copies of the same db. Bad reference. Once I deleted it and made a new grid, boosting disappeared

Gth

N

 

[Poppa Smurf]

Just a suggestion have you considered using ARRAYS to do your data manipulation?