OLE Object Makes Database to Large

[plfiredis]

Fire District has database for running cards -- directions from each
firehouse to each street in the district. My databse is split - front end
and back end. We want to add maps of each street. We have scanned the maps
and I created a table for the maps and linked it to the table with the
directions. I'm pulling the maps in as a ole object. The problem is the
database is too large with the maps. Is there a better way to bring the maps
in so they show on the form for each street. We tried scanning the maps as
pdf and jpeg, but they are ~ 65 to 80 kb each and I have 1000 to pull in.
Any suggestions would be greatly appreciated.

 

[Clifford Bass]

Hi,

I would think Access could handle that number of images. What happens
when you load all of them?

If that is not feasible, how about storing the files separately outside
of the database and just storing the paths to the files in the database?
Then in your On Current event you could do something like this:

imgMap.Picture = [txtMapPathName]

Where imgMap is a unbound image control and [txtMapPathName] is a
textbox, possibly invisible, that is bound to the map path name column in
your table.

There is also in Access 2007 the Attachment type that might be
appropriate. I have not used it yet, as so cannot advise on it.

Clifford Bass

 

[Fred]

Storing images using an OLE field causes huge bloat and has other problems,
at least for all Access versions up through 2003. I heard that 2007 might
have fixed the problem but can’t vouch for that.

The way to do this is to store the pictures in a separate folder, and then
store the location (path) and filen name in 1 or 2 text fields. Then use
code to display the image in forms and reports. I’m not good enough to write
the “how to†from memory, but there have been many posts in the Access forums
on how to do that.

 

[Fred]

Storing images using an OLE field causes huge bloat and has other problems,
at least for all Access versions up through 2003. I heard that 2007 might
have fixed the problem but can’t vouch for that.

The way to do this is to store the pictures in a separate folder, and then
store the location (path) and filen name in 1 or 2 text fields. Then use
code to display the image in forms and reports. I’m not good enough to write
the “how to†from memory, but there have been many posts in the Access forums
on how to do that.

 

[Clifford Bass]

Hi Fred,

Thanks for the information. I have not used Access to store images,
but rather a different back-end. Is an OLE field essentially a long
varbinary? Or something different? Could you store images in a memo field?

Clifford Bass

 

[Fred]

Hello Cliff,

I'm not smart enough to answer your question varbinary question. What I
wrote is what I learned both from lots of experience doing it both ways, and
from seeing it asked and answered in these Access forums lots of times.

Sincerely,

Fred

 

[Clifford Bass]

Hi Fred,

Just found the answer at
http://msdn.microsoft.com/en-us/library/aa211091(office.11).aspx which says
that the OLE Object is a long varbinary. A memo is a long varwchar, which
probably would not allow for binary data. It probably would clash with the
UNICODE encoding in the memo field.

So, from your experience and others, Access just does not handle it
well. At least pre-2007. Useful to know.

Thanks again,

Clifford Bass

 

[plfiredis]

Thanks for your suggestions. I'll try to figure that out. I am using 2007
and it is having problems. I brought in about 400 maps (ole) and it
corrupted the database because it was too large.

 

[plfiredis]

Fred,

When you say to store the path and file name in one or two text fields, do I
create a seperate table for these and then link the tables?

Thanks,

Patty

 

[Fred]

Hello Patty,

It would just be one or two text fields in the relevant table. (we do it
using one, which is the combined path and file name)

From your post, I'm guessing the relevant table would be your RunningCard
table.

Sincerely,

Fred

 

[Fred]

BTW, below is some of the text I saved from posts on this.....I just "got
by" on this, did not write these nor do I know them well enough to answer
questions without digging back into what we did.

- - - -

Store the pictures in a separate folder from the database. Add a
field named PicturePath to your table and record the full path to the
picture for each record. Add an image control to your form. Be sure you have
PicturePath in the recordsource of your form. Put the following code in the
Current Event of the form: Me!NameOfYourImageControl.Picture = Me!PicturePath.

- - - -

Add an image control from the toolbox for displaying your pictures. Select
the image control, open properties, find the picture property and bind it to
the path to your picture in your table as Fred describes.

 

[plfiredis]

Thanks Fred. That should help alot!