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

Question

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.

If you use File Table SQL Server will expose a Windows file share for you to save the files in using filesystem APIs. learn.microsoft.com/en-us/sql/relational-databases/blob/… — David Browne - Microsoft
– David Browne - Microsoft, Commented Jul 1, 2020 at 23:16
If it's possible to have the image be visible instead of the file-path while the user is in the Access front-end, that'd be ideal. Is that possible? — breezett93
– breezett93, Commented Jul 6, 2020 at 20:56

Parfait · Accepted Answer · 2020-07-01 22:17:32Z

1

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

answered Jul 1, 2020 at 22:17

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related