CODEWITHSUNDEEP
sqlsql-serverimagems-accessvarbinary
breezett93
breezett93

Reputation: 1

Using Microsoft Access program to upload images to be stored in SQL Server database

I have an Access back-end that is going to be converted to SQL Server. The front-end will stay the same using Access. The issue I am having is how SQL Server handles images differently than MS Access.

Currently, a user adds a picture to the record via the attachment data type which, to my understanding, isn't possible in SQL Server. I saw the image data type is deprecated which leaves varbinary(MAX) and/or filestream as the options.

I want to go with storing the images in the filesystem as the size is greater than 256KB, but I'm not finding any documentation about accomplishing that with an Access front-end.

Upvotes: 0

Views: 733

Answers (1)

Parfait
Parfait

Reputation: 107687

Consider running an MS Access pass-through query to upload user's image. Specifically, pass the file name into an SQL query as shown in MSDN docs for large-value data types. For this, the user will need OPENROWSET privileges and the image file may need to be accessible on client machine or server.

INSERT myTable (myImageColumn, ...other columns...)  
SELECT myPicData.*, ...other values...  
FROM OPENROWSET
     (BULK 'C:\Path\To\Image.jpg', SINGLE_BLOB) AS myPicData

Upvotes: 1

Related Questions