Bloated Access Database? 2

[AlexCuse]

I have inherited a SQL Server database using an Access front end for reporting. I have only been working with it for about two months, and in those two months the database size has grown from about 144,000 k to about 745,000 k. No substantial data has been added, because it all lives on the SQL server. I have tried removing queries, reports, and even tables (including the linked tables). I even tried making a copy of the database and stripping it of literally everything except one code module. The size remained 745,000 k!

It has gotten to the point where closing out of the database takes about twenty minutes, usually creates a db1.mdb copy of itself, and really ties up anyone's computer who is unfortunate enough to need to use it's reporting functions. Has anyone else had similar problems? I would like to find a solution besides replicating the database one piece at a time, because it links to a ton of SQL tables. Any advice you all could give would be greatly appreciated.

Thanks a lot.

Alex

 

[longhair]

AlexDMW,
compact and repair?
regards,
longhair

 

[AlexCuse]

No dice on the compact and repair, that was my first thought also. I have tried it several times. It usually doesn't even get through the compact and repair without error.

 

[longhair]

AlexDMW,
did you try it locally?
copy the db down to you local machine and run it.
compact and repair not working could be due to network, people accessing it, etc.
regards,
longhair

 

[RoyVidar]

Are you talking about the SQL server database or the Access front end?

For the first, check out forum183

For Access, if it doesn't get through the compact and repair without error, then you probably have some kind of situation on hand - my guess would be some kind of code corruption (this is something I often guess, at, btw ;-) )

One method that's supposed to clean up a bit both with regards to code corruption, and clean up a bit more/other things than compact/repair, is the /decompile. Check out the detailed instructions here

http://www.granite.ab.ca/access/decompile.htm,

don't forget trying this on a copy.

Creating a new db is probably also an option. I think I'd copy the text of the module(s), not import them, though the export/import from the VBE IDE should be somewhat more safe than the File | Get External Data from the GUI.

Whatever you try, as longhair says, copy it locally before doing anything, then also open without running any code (holding shift key)

Roy-Vidar

 

[longhair]

and if it just started really growing, what changed around the time that it began? sounds like recordsets may not be getting cleaned up.
along the lines of RoyVidar's suggestion - after you take a copy of the db down locally, open up a blank access db and copy (you can drag and drop them) the objects (except module(s)) into the new one. what is its size? then follow his suggestion for getting the code into the new db. any size difference?
regards,
longhair

 

[AlexCuse]

thanks for the good suggestions guys. To answer your question, there are some tables on the access side but the large ones exist on SQL. It is mostly queries, views, and linked tables used to generate the reports. As you can probably tell I didn't build this thing, and it is driving me crazy trying to fix it. I'll let you guys know if I am still having problems after all this.

 

[JoeAtWork]

there are some tables on the access side"

Programmers sometimes use local tables to cache temporary data. There might be some operations that are adding and deleting records to those tables. Even if the temporary records are deleted, the space is not reclaimed until a Compact & Repair is done. I might start searching through the code to try to find how those tables are used. If you are finding it hard to search through the code, on a copy of the database you could try deleting those tables and see what breaks - the error should point you to the code.

 

[longhair]

AlexCuse,
"there are some tables on the access side but the large ones exist on SQL"

then the issue is with (or at least more with) sql.
are you truncating?
regards,
longhair

 

[JoeAtWork]

I think AlexCuse needs to clarify what is "bloating" - the Access frontend, or the SQL Database.

Alex, when you originally said "database size has grown from about 144,000 k to about 745,000 k" - exactly which file are you talking about?

 

[AlexCuse]

The access front end is growing. SQL DB size is relatively small and has not grown any more than expected with normal monthly loads. Import tables are being truncated (These are equivalent to final staging tables in a better developed system) but tables used by the views for reporting are not truncated. The more I look at the way this is set up the more skeptical I become. It looks like someone at one point removed PK constraint from some of the tables in order to force data into them, and as a result I have a pretty large number of dups in there. I am starting to think that the lack of PK may be putting too much strain on Access. I'd like to know anyone's thoughts on this...

 

[JoeAtWork]

Well, the lack of a PK (and/or enforced relationships to other tables) leaves your database unable to defend it's own data integrity. You may want to do a review of the database design and try to fix what others have broken. It's a thankless job usually, but since it's "yours" now it's probably better to fix it up ASAP before it causes you bigger problems down the road.

Regarding the bloating problem, I am still suspicious that the local tables are being used to hold temporary data. The solution would be to compact & repair the front end on a regular basis. But if everybody has their own local copy of the front-end then you are in for a distribution headache. Unless you use the following strategy:

1. Compact & repair the front-end, place it on a server everyone can access.
2. Write a batch file that copies the server copy to the user's temp directory. Then have it launch Access with the local copy.
3. Make a shortcut that points to the batch file. Have everybody use this shortcut to start the application.

That way, everytime they start the application, they are using a fresh copy of the front-end. It also makes it easy to distribute a new version of the front-end.

But first you need to solve your Compact & Repair problem. I have often seen that fixed by opening the database with the /decompile swich. Look at the link provided by RoyVidar (or do a search in Access Help).

 

[JoeAtWork]

Hmmm, I just re-read your "144,000 K", that's like 140 MB. That seems huge for a front-end. If Compact & Repair doesn't shrink it to below 10 MB, then my solution for distributing the front-end probably isn't feasible.

 

[AlexCuse]

You're right Joe, I was amazed to see that it was 144,000 when I started. To have it now up to 745,000 or whatever it is up to now just blows my mind. The long term solution is going to obviously be to migrate the reporting into SQL (or my boss is thinking about doing reporting through SAS so that would be another headache) but until some more pressing issues are ironed out I need to work with what I've got :-(

 

[franksirvent]

I am not sure if it helps but I had the same problem once.

The reason was because I had a table on the front end which was not linked to the server or anything, but it was actually part of the MDB front end...

I used it to store temp queries....so it just kept on growing and growing...

Then I finally found the problem, which was that my code was not deleting any records from this temp table, so it just kept on adding new records....

So it had thousands and thousands of records...which bloated the FE

I just added some code which would delete all records from the temp table when exiting to the Main Menu, and again when quiting the program all togehther.

Did wonders

I am sure that's not the reason but I just thought of sharing my experience

 

[AlexCuse]

I finally had time to go through all the code, and what happened was my predecessor wrote data scrubbing functions that are used in the process of importing to SQL in the reporting front end instead of it's own DB (Why it was not done in SQL in the first place is beyond me, but to each his own). There was code to clear the tables used to generate 'good' and 'exception' files to export, but there was no code written in to clear the table that the initial file is brought into. I cleared this table and a few others (for scrubbing different file types) and then did a compact and repair and front end is now down to 9.4 megs (REJOICE!):-D. Unfortunately I can't use it yet because there are some reports to validate but at least I'm making progress. Thanks for all your help guys!

 

[easyit]

It has gotten to the point where closing out of the database takes about twenty minutes, usually creates a db1.mdb

switch of compact & repair on close, it is probably on.