Question Access 2007 DB becomming very large

[jiblankman]

I have been making a personal database to catalog articles I want to save from a number of woodworking magazines. The DB is fairly simple but I cannot attach it due to the size. There are three tables; three forms linked to the tables; and some reports. I want to include a picture of each of the projects (or article title). Here, I believe is the problem.

After adding 144 articles, the DB was 95 MB. I looked at some of the images and realized that they may be too high resolution, so I was going to replace them with smaller images. I removed the first 31 images and replaced them with much smaller jpegs. The typical image size went from 300 kb to 20 kb. The database went to 114 MB.

I still want to add about 1000 articles to the DB but the size is becomming unwieldy already. I have tried compressing the database using the built in compress feature, but it does not change the size.

If I create a .zip file, the final size is 43 MB. This should get smaller after I replace the rest of the images with jpegs.

Can anyone help me make this a more useful database? I was very happy with the design until I realized how the size was getting out of hand. Thanks.

Jeff

 

[jdraw]

Do you compact and repair from time to time?
Do you really storee the images within the database? The consensus is that this will cause bloat. The alternative is to store the image in the file system and keep a link (drive/folder and filename) in the database.

 

[jiblankman]

I have the database set to compact and repair on close.

I have been copying and pasting the images into the database. That is probably the problem. I will look into using a link instead. Thanks for the suggestion.

Is there a way to automatically generate the link path for new records? For examples, it I name the image files to match the record number (e.g., 1.jpg), can I create an automatic link to the file c:\users\me\[record number].jpg?

Thanks.

 

[shadow9449]

I think that in Access 2007 there is a setting to tell it to save images as jpg instead of bmp. If you don't check it, then it will save images as bmp whether you like it or not, and the database will become enormous very quickly.

Personally, I don't save images in a database. I save them on a folder in the same directory as my database's backend and have them load in dynamically. That keeps the database far, far smaller, no matter what image format you are using.

SHADOW

 

[Simon_MT]

Jeff,

c:\users\me\[record number].jpg

You certainly can and it is still a better way to do. You just put in into your query and create an Imagecontrol.

Simon

 

[jiblankman]

Thanks for the help. I am in the process of removing the images from the database and creating individual jpgs. I will need to learn about imagecontrols as i have never used them. I will post my results when completed.

 

[jiblankman]

Ok. I removed all of the images from the database and see a dramatic difference in size. It went from 114MB to 500 kb.

I now have a folder in the same directory as the database. The folder contains files named like "image1.jpg", etc. Image1 is the image that should be displayed with the item ID 1. I do not want to have to add an entry into each line of the underlying table with image#.jpg and then have to make sure to add this for all new records.

Is there a way to automate this so that a box on the form/report will contain the image associated with the ID (assuming that the # in image#.jpg is the same as the ID)? Thanks.

 

[Simon_MT]

Your form should be based on a Query. In this query you create an expression, there are two parts:

1 Directory information
2 Image file

For example:

ImagePath: "C:\Databases\Images\"
ImageFile: [ImagePath] & [RecordID] & ".jpg"

Use the standard "Image" control and make the Control Source to [ImageFile]

Simon

 

[jiblankman]

I must be doing something wrong. I have the following query:

SELECT Articles.ID, Articles.ProjectName, Articles.MagazineTitle, [ImagePath] AS Expr1, [ImageFile] AS Expr2
FROM Articles
WHERE ((([ImagePath])="Magazine Index Images\Record") AND (([ImageFile])=[ImagePath] & [ID] & ".jpg"));

When I open the form, it asks for the ImagePath and ImageFile. Where am I going wrong?

 

[jiblankman]

Never mind. I figured out what I messed up and corrected the SQL.


Thanks for your help, this was an interesting learning experience and a success. My DB went from 114MB to about 650 kb with a 2.5 MB folder of images. The images load faster, especially for some of the higher resolution ones, and it is much more portable this way.