storing photos on db - SQL Server B/E, MS Access F/E

[lorirobn]

Hi,

I need to add photos to my database. Back End is on MS SQL Server (I
believe 2000), and Front End is on MS Access.

I have read about storing the photos as BLOBS, but I am not sure how
to do this with SQL Server. Does this mean store the photo as OLE
image, but do something else to it to make it a "Blob"?

I have also read about linking to the photo rather than storing it on
the db, but I'd be afraid to do that as things get moved around in my
organization.

What we'd like to do is have a window where user specifies the path
the photo is located in (user presses "browse" button), then database
imports photo in and stores it.

Appreciate any help on this... THANKS!

(posting on 2 google groups)

 

[paii, Ron]

Hi,

I need to add photos to my database. Back End is on MS SQL Server (I
believe 2000), and Front End is on MS Access.

I have read about storing the photos as BLOBS, but I am not sure how
to do this with SQL Server. Does this mean store the photo as OLE
image, but do something else to it to make it a "Blob"?

I have also read about linking to the photo rather than storing it on
the db, but I'd be afraid to do that as things get moved around in my
organization.

What we'd like to do is have a window where user specifies the path
the photo is located in (user presses "browse" button), then database
imports photo in and stores it.

Appreciate any help on this... THANKS!

(posting on 2 google groups)

Link to the photo.
Place them in a secure location on your server so users cannot move them
around. To the user it would look like they are part of the DB.

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s

 

[Tony Wickernark]

you can store them as an image datatype.

i'm also of the understanding that if you're talking about small items you
might be able to store them as varbinary and have much much much better
performance

wish i knew where to find out more about that varbinary thing; I think that
i saw it on a MS blog

 

[Larry Linson]

The sample imaging databases at http://accdevel.tripod.com illustrate three
approaches to handling images in Access, and the download includes an
article discussing considerations in choosing an approach. Two of the
approaches do not use OLE Objects and, thus, avoid the database bloat, and
some other problems, associated with images in OLE Objects. It does not
cover every option for handling images (e.g., hyperlink fields, or using
Application.FollowHyperlink to display), but it does have an example of a
BLOB. I have used this techique occasionally since the days of Access 2.0
with server DB backend datastores.

If you are printing the images in reports, to avoid memory leakage, you
should also see MVP Stephen Lebans' http://www.lebans.com/printfailures.htm.
PrintFailure.zip is an Access97 MDB containing a report that fails during
the Access formatting process prior to being spooled to the Printer Driver.
This MDB also contains code showing how to convert the contents of the Image
control to a Bitmap file prior to printing. This helps alleviate the "Out of
Memory" error that can popup when printing image intensive reports.

Additional comments interspersed.

I need to add photos to my database. Back End is on MS SQL Server (I
believe 2000), and Front End is on MS Access.

I have read about storing the photos as BLOBS, but I am not sure how
to do this with SQL Server. Does this mean store the photo as OLE
image, but do something else to it to make it a "Blob"?

No, you just use AppendChunk to fill the OLE Object with binary data (hence
_B_inary _L_arge _OB_ject) and GetChunk to retrieve it. The OLE Object
Field is limited only by the size of the database.

I have also read about linking to the photo rather than storing it on
the db, but I'd be afraid to do that as things get moved around in my
organization.

Surely there is some way to control this, or at least provide "notice" if
the information is moved or the folders renamed? If the latter, your users
could browse for the new location. That's also illustrated in the example,
using the code from the FAQ at http://www.mvps.org/access/api/api0001.htm.

What we'd like to do is have a window where user specifies the path
the photo is located in (user presses "browse" button), then database
imports photo in and stores it.

The code referenced in the preceding paragraph uses the Windows Common
Dialog for this purpose. I'm sure there are other ways, but that has stood
me in good stead for a number of years, and it's in applications I use just
about every day.

Larry Linson
Microsoft Access MVP