store photos on db - SQL server back end, access FE

[ITlori]

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.

Appreciate any help on this...

 

[jrbarnett]

blob" is a generic acronym for "Binary Large Object".
In SQL Server, the field type to use for blob data would be varbinary for SQL Server 2000 or 2005.

You are right that loading a file into a database field will significantly increase your storage requirements for the plain data and decrease performance over storing a text file path and just putting it in a known location on disk and opening it from there.

One thing - if you're storing large amounts of data in SQL Server, don't use linked tables in Access. They tend to keep locks open for long periods of time and as a result, slow down the system quite significantly.
Instead, use unbound forms and SQL Server stored procedures/ADO connections from Access (or an ADP file) to connect. Its a lot more complex to build, test and deploy, but the end result is a far faster, more secure system.

John