CODEWITHSUNDEEP
sql-serversql-server-2005ms-accessole
BIBD
BIBD

Reputation: 15404

How do I store pictures in SQL Server (MS Access interface)

Following podcast #59, I've been considering moving some images from an MS Access DB into MS SQL Server (it's the only piece I haven't migrate over yet). Right now they are stored as OLE Objects in the MS Access DB.

However, I haven't got a clue what data type they should be stored as on the SQL Server side, or how to get them inserted through the interface (MS Access via ODBC).

Upvotes: 1

Views: 5125

Answers (4)

MaxD
MaxD

Reputation:

You can try to compare em, it can be interesting as AccessImagine is newer one.

Upvotes: 0

David Walker
David Walker

Reputation: 1506

We have used DBPix from Ammara, www.Ammara.com for 5 or 6 years now to handle the capture and display. Very easy to use ActiveX control with complete sample code in their documentation, and affordable.

Our team preferentially stores images on disk and refers to them by storing their location in a varchar, but the control will handle both methods.

Upvotes: 0

MaxD
MaxD

Reputation:

There is several BLOB-type fields in MS SQL Server for storing binary data - but some of them have size limitations.

If you use SQL 2005 or higher, the best choice is varbinary(MAX). It can store really big data pieces.

Be warned, that keeping pictures in OLE format will cause a lot of problems for you in future. While you're moving on, consider converting your images to usual graphic files (like JPG). It will save resourses of SQL Server and improve Access displaying speed.

I recommend to take a look at AccessImagine (http://access.bukrek.net), it would help you to avoid a lot of headaches about the images. Just look at video.

Upvotes: 1

Michael Todd
Michael Todd

Reputation: 17061

I would use the image type, and I would NOT use Access to transfer them. I'd use the Sql Server Import and Export Wizard to do it. To do so, right-click the database you want to import to in Sql Server Management Studio, select Tasks, and then select Import Data.

Upvotes: 2

Related Questions