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).
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.
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.
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.