Image ctl causes bloat when using text field to store path/filenam

[Pete]

I am using Access 2003 db (2000 format) to manage photos.
I have a form/subform where the subform (Single Form) displays the photo for
the current record using the Image control.
The detail table holds the path and filename only in a 'Text' field and the
OnCurrent event assigns the path & Filename to the Picture property of the
image control. This all works fine.

I have checked “compact on close†option and manually run a “Compact &
Repair†several times.
The db is not currently split.
I have used a similar setup to the Microsoft article:
http://support.microsoft.com/kb/285820

The problem is that, with only around 5 records added - storing text & not
OLE objects, the db size has increased from ½ Mb, to over 35 Mb.
As I am only storing the path/filename to the photo, why does Access inflate
the mdb file?
Many thanks.

 

[Pete]

During my attempts to find different solutions on the same subform, which
included:
a. using OLE fields to store the photo
b. assigning the picture property the path & filename
etc...

I believe the mdb file has become corrupt in some way.
I decompiled the original file and cretaed a blank db on copied the objects
accross. It was while I was copying the subform (displaying the photo which I
tried using several methods) which I noted took a significant amount of time;
and I presume is the cause of the bloat.
So I created a blank subform from scratch and copied everything except the
image control. I recreted the image control from scratch, saved the subform,
and compacted the db.

I addeed some more records and I now have an mdb file with an acceptable
working size of around 380kb.

 

[Gina Whipp]

Pete,

Perhaps you should tell us your table structure and approxiamately how many
records you have entered. Also, have you turned off the Name AutoCorrect
and Compact on Close? Both have been know to cause issues.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Pete]

I have a very simple 1 to many relationship, where the many table is storing
only the path\filename in a single field. The OnCurrent event of the subform
assigns the path\filename to the Picture property of the image control.

I did have the "Name AutoCorrect " option checked on the bloated mdb file;
but have Unchecked this on the newly created mdb file.

The "Compact on Close" option was/is checked on both versions. Is this
option a possible candidate for bloating?
Many thanks.

 

[Gina Whipp]

Pete,

Yes it is, please uncheck.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Pete]

Many thanks for the advise Gina.
This is very confusing. I thought the whole point of this option was to keep
the file size to a minimum at all times??

If I uncheck the “Compact on Close†option, I presume I then need to do a
manual Compact and Repair periodically. Does this manual process negate the
bloating effect which you say the auto option produces?
Thanks.

 

[Gina Whipp]

Pete,

While you are correct about the keeping the bloating down to a minimum. The
Compact on Close will attempt to do so while the database is use... So
Sally will still be trying to work while you compact and oops the database
corrupts while trying to compact because Sally was still working. The best
way is to do this periodically. However, if your database is growing THAT
much in size from day to day it might pay to look at your table structure.
I have had databases running for years and even without compacting which for
some that could be six months the growth is so minimal that it's not worth
mentioning.

If you are doing alot of Make Table, Delete and Update queries this can
cause bloating more than would be 'normal'. If you are doing none of the
above but you grow 'more than you think you should' then as I mentioned look
at your table structure. If you're unsure post the size of your database at
the beginning of the day and at the end of day and we'll have a look-see!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Pete]

Many thanks again Gina,

All this bloating has occurred during - and still under - development. The
problem, I have since discovered, is with the Image Control. I first had the
problem with this control in a form and fixed it by creating a new db. I then
got the same bloating problem in a report containing the Image control. I
tried removing just this control from the report and compacted the db.
This reduced the file size from 50+mb to 640 kb!!

An additional problem I have just discovered is that if the image size is
over 2Mb, the Image control on the report does not display that image (jpegs
in this case).
I wonder if this is related to the bloating problem??

I have also found that even when the report is closed and I hover the mouse
pointer over the report icon in the db window, Access (2003) continually
flickers up an "Importing <filename>.jpg" box with what looks like a tooltip
of Print (\\<share name>\<printer name> which also flickers continually
(while the mouse pointer is over the report icon). The Local Area Connection
icon stays on during the flickering, as the images are stored on the network.

The author also wishes to point out that "at no point were action queries
used (or harmed) during the making of this db†)

 

[Gina Whipp]

Pete,

Are you storing the image in the database? I thought you were storing just
the path but from what you are saying it sounds like you are iehter storing
it in your database or embedding it in the report and/or form.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[aaron.kempf]

I would ignore Gina, her answer is to use Jet, no matter how
applicable.

I'd just move to SQL Server. SQL Server has 10 different ways to
store images in a database, the latest (filestream or remote object
store) do not have any bloat probelms

-Aaron

 

[Pete]

Gina,
Apologies for the delay; just taken a weeks holiday.

Yes, I am only storing the path/filename, not the actual image. I need to
assign the Image control an actual image on the report in the frst instance,
otherwise, Access just deletes the control.

Pete.

 

[Gina Whipp]

Pete,

I know you said you are using code similar to what is available from
Microsoft. But similar to implies changes have been made. Can you post the
code you are using. I have a database which has MANY image controls on
reports and forms and so not see this bloating so perhaps the code will lend
a clue!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Pete]

Gina, as requested:
This is the only code in the whole report.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.imgPhoto.Picture = Me.txtSmallFile
End Sub

....where 'txtSmallFile' is a textbox bound to the path/filename field on thy
report.

 

[Gina Whipp]

Pete,

That is not similar to as you have eliminated the entire DisplayImage part
with would handle errors. As your code is right now, it will 'break' on
error. However, that aside, still doesn't explain the bloating. Are you
running alot fo Update/Delete/Make Table queries? Also, are you still
developing (making forms, queries, tables, etc...)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm