Front End Database Getting Very Large 3

[BlackTL]

Dear All,

I am working on an Access 97 database that I have been noticing is getting very large. It is currently 153MB in size (just the front end). I thought it was kind of odd so I created a backup and deleted all my forms, tables, macros, modules, and queries to see if the file size would reduce. I checked the size of the mdb file after I deleted everything and the file size is still the same, 153 MB. I know there is something I must be doing wrong here. Would anyone be able to assist me in determining what's going on?

Many Thanks,

Roshan

 

[psemianonymous]

The biggest source of bloat is embedded images.

The other big source is using temp tables, or constantly importing/deleting tables/big chunks of data.

Your friend in this is Tools->Database Utilities->Compact Database (may be listed as compact & repair). This will free up any empty space the database is hogging.

 

[BlackTL]

Hi and Thanks for your reply. I tried compacting but I get an error stating the records could not be read because of my permissions. I don't know why I am receiving this. Please advise.

Cheers,

Roshan

 

[psemianonymous]

Two more options:

1. You actually don't have permissions, for some reason. Give me the specific error message, and maybe we can whittle this down.

2. Your database is corrupt. See the link below for a very good list of ways to recover.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[BlackTL]

For some reason, even though I get the error message, the compacted file is still created under the name "db1.mdb". Is this a temp file or is it supposed to be the new name of the compacted file?

What if I try this:
(1) copy all my forms, modules, etc into a new database. Should this fix these problems?

Another question regarding the size of the database. The database is used by users via Citrix. It is copied from the Citrix server to the user's local temp directory. Do I have to worry about the database file size growing since everytime the user opens it, it will be replaced by a fresh one from the citrix server?

Thanks

 

[psemianonymous]

The compilation process looks like:

1 open source.MDB

2 create db#.MDB

3 append 'compacted' information into db#.MDB

4 delete source.MDB

5 rename db#.MDB to source.MDB


...and no one is the wiser. Your problem could be occuring either on step 3 or 4. If you don't have 'delete' rights on the directory, it's likely #4. Maybe.


Or it could be somewhere in #3, indicating corruption.

if you can import all objects into a new database file, without problems, this 99% guarantees your source MDB isn't corrupt and/or fixes the corruption problem. Also, if it fails, you can guarantee the source.MDB is corrupt.


And about file size: it's all you. If the file is as huge as you say, it would be problematic to copy it anywhere, but maybe not. Definitely get the compact working, and then we'll see.

PS--are you copying it to the user's "local" drive in their remote WAN location, or are you copying it to their "local" drive on the citrix server itself?

 

[BlackTL]

Hi and thanks for your reply.

I will try and copy each form/query/etc. to a new db and see what happens. Is there an easier way to copy the queries/forms than to click copy, paste, and typing in the name of the query/form? I have about 200 queries.

In regards to your Citrix question, whenever someone logs into my database through Citrix, it copies the file to their local drive to a temp folder. Therefore, am I to assume that everytime the file is copied, it will bring the front end back to its original size?

Thanks much,

Roshan

 

[rkasnick]

Roshan, you should open your source database in programmer mode, click FILE | GET EXTERNAL DATA and in the navigation box that opens navigate to and select your old database, then when it opens, click 'select all' on the right side of EACH tab listed, then click OK. Access will copy all selected objects to the new database. Then, open a module and click 'DEBUG | COMPILE' and you're all set.

Regards

Rod

 

[BlackTL]

Damn you guys are good! My database is now 1.98 MB!!!

Thanks for all your help guys!!!

Roshan

 

[BlackTL]

I just gave stars to both of you.

Thanks again.