Could this causing db Bloating

[T''Kai]

I have this code running on several mdes. It shows the photo a client when
the users navigate to a record. Could this code and the manner in which I
show photos be causing the database to grow exponentially and cause
corruption. Today I compacted this database at 7am was 61.1 MB at noon it
was 73. 8 MB.

This has be going on for quite sometime. If I don't compact the database at
least 2 to 3 times a week it will crash. For some reason the only table that
crashes is the "Demographics" table. This table when corrupt will show #
signs in all the fields of the corrupted record. There are no memo fields or
self-joins.

The code is this:


strFindPhoto = "\\Server1\Error Logs\client_photos\" & Me.File & ".BMP"
'"\\Server1\Error Logs\client_photos\" & Me.File & ".BMP"
'C:\Documents and Settings\rozannea\Desktop\Shared Laptop\NECHV\client_photos
If Len(Dir(strFindPhoto)) > 0 Then
Me.imgPhoto.Picture = strFindPhoto
Else
Me.imgPhoto.Picture = "\\Server1\Error Logs\client_photos\0.BMP"
'"\\Server1\Error Logs\client_photos\0.BMP"
End If

Any help would be appreciated. Thank you.

 

[Allen Browne]

That should not bloat the database incessently.

You say this occurs in an MDE, so the code cannot be decompiling.

First thing to look for would be temporary tables in the front-end, where
data is being added/deleted (or if you are creating destroying tables or
queries in the front end.)

The corruption is a concern. Is the database crashing?
Is it split, so each user opens a separate copy of the front end?
Is the bloating occuring i the front end, or the back end?
What version of Access is this?
Are then OLE Objects, hyperlinks, or Attachment fields?

 

[Tom Wickerath]

I doubt the code snippet you showed is the problem, especially since it
indicates that the photos are being stored externally to the database. Here
is a link to several Microsoft Knowledge Base articles on the subject of
bloat. Go through each one and see if anything applies in your case:

http://tinyurl.com/2dmpw


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[david]

Is Me.imgPhoto a bound field? Is the behaviour the same in Access 2007
Runtime? Growing from 60 to 70 MB after compacting doesn't mean
anything - it's only a problem if it continues to grow. How big does it get
before crashing?

(david)

 

[T''Kai]

I thank you all for responding: In answer to your questions....

There are no temporary tables in the front-end. All tables are linked to the
back-end. The database is crashing when it reaches approximately 80MB. Each
users has there own mde on their personal drive on the server. It is the
back-end that is crashing. There are no OLE Objects or hyperlinks or
Attachment Fields. The photos are temporarily placed in an "image" with the
picture type being embedded vs. linked, I don't know if that would make a
difference.

The table called Demographics is the primary table in the db and almost all
queries call on this table. When the back-end crashes, the primary field in
this table "File" always loses it primary status. The MsysCompactErrors table
always says "Could No Find Field Description".

Hopes this anwers everyones questions. I am at a lost as to what the problem
is.

Help!

Thanks

 

[John W. Vinson]

I thank you all for responding: In answer to your questions....

There are no temporary tables in the front-end. All tables are linked to the
back-end. The database is crashing when it reaches approximately 80MB. Each
users has there own mde on their personal drive on the server. It is the
back-end that is crashing. There are no OLE Objects or hyperlinks or
Attachment Fields. The photos are temporarily placed in an "image" with the
picture type being embedded vs. linked, I don't know if that would make a
difference.

The table called Demographics is the primary table in the db and almost all
queries call on this table. When the back-end crashes, the primary field in
this table "File" always loses it primary status. The MsysCompactErrors table
always says "Could No Find Field Description".

Hopes this anwers everyones questions. I am at a lost as to what the problem
is.

My guess is that there is some systems table corruption in your backend
database, and that the code is unrelated to the corruption. I'd suggest
creating a new, empty database and importing all of the tables from your
currnt backend into it; reestablish relationships if necessary.

There are some good resources on corruption at Tony's site:
http://www.granite.ab.ca/access/corruptmdbs.htm

 

[T''Kai]

John, I will try that this weekend. Thank you.
Do you think the image object having a type of embedded instead of linked
would cause bloating?

 

[John W. Vinson]

John, I will try that this weekend. Thank you.
Do you think the image object having a type of embedded instead of linked
would cause bloating?

I really have no idea. I would have *expected* that bloating would happen only
if the image object were being stored in the database that's bloating, which
does not appear to be the case here, but I have not studied the subject.

 

[Fred]

Embedding images always causes huge bloating.

You might have thrown people off from noticing this when you said "There are
no OLE Objects"

Certainly up through Access 2003. Heard rumors that Access 2007 is better
but can't vouch for that.

 

[david]

Yes, embedding images in a table in the Back End would cause
bloating in the Back End.

Embedding images in an unbound control in the Front End shouldn't
be causing bloating in an unrelated table in the Back End, and,
80MB of bloat is no reason to see the database crashing and
corrupting.

Or I would re-create the one corrupt table from scratch, then
append all the data into it.

(david)