Efficiently Storing Images with Access and SQL Server

In SQL Server you have a good choice to save your files(images and others) as a record in database and SQL Server store this file in file system instead of database.

You have some benefit in this way:

• Your files doesn't effect in database file size
• You can add/delete your file with T-SQL
• You can backup of your database and all of your file easily

For this approach you need to enable file stream in database.

You need 3 steps to enable file stream:

1. Enable file stream in service level
2. add file group in database
3. Create your table to store

First step(Enable file stream in service level):

Open SQL Server Configuration Manager and select your database and enable file stream.

Its document is here

Note: if you couldn't find SQL Server Configuration Manager refer to this document

Then in SQL Server Management Studio, click New Query to display the query editor and in the query editor, enter the following Transact-SQL code and click execute:

EXEC sp_configure filestream_access_level, 2  
RECONFIGURE 

Then restart the SQL Server service Note: you can do this in SQL Server Management Studio graphical interface as below:

Second step(add file group in database) :

• add filestream file group to database

• add database file

Or you can do it by following script base on this document:

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\archlog1.ldf')
GO

Third step (create your table and store):

And then easy step is create table:

CREATE TABLE Archive.dbo.Records
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Image] VARBINARY(MAX) FILESTREAM NULL
)

Note: column type as [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE is required and your image column type should be VARBINARY(MAX) FILESTREAM

And then you can insert any data as bellow, more example:

INSERT INTO Archive.dbo.Records
    VALUES (newid (), 3, 
      CAST ('Seismic Data' as varbinary(max)));

But if you want to use MS Access, I think the best way is store filename in database and handle file management by coding.

This really comes down to is the server going to be on the same network.

In other words? Hands down store a link to the file(s) in question. The reason is HUGE.

For example: Say you have a huge folder of PDF's that are linked and managed from the Access front end. Well, now I can use ALL KINDS of tools to process, to browse, to see, to use those files and can do so OUTSIDE of Access. And this applies to darn near any database.

Often say the PDF is 2020-12-06 I34343.PDF

So, we can now grab a invoice I34343 from that folder based on today's date. I don't even need the software to do this. So now other code systems, other programs can directly grab and use those files. Heck even a batch file to print some PDF's each night can be written and run outside of Access.

So, having worked with both files "inside" of a database vs those outside? Hands down the winner is folders and files - and a link to those files. It is trivial to have some button or link in a Access form, and when pressed you load, consume, display, process, print or do whatever with those say PDF files. If you place the files inside of the database, then you can't read and grab and use those files with great ease.

There is however ONE big exception to this rule.

That is if the server + database are NOT on your network and you don't have a common file share system. So for example, you might be building a web site or using hosted SQL server. You have a socket connection to that database, but you will NOT have use of the file system from that server platform. In that case, then storing the files inside of the database can be a great benefit since you don't need a file system anymore.

So now any system that can connect to the database can also pull files. So, I don't recommend storing files in the database - it is a choice with TONS of limitations and simple file operations, copy of files and use those files becomes a royal pain.

But, if you don't have a common file share system? Then files in the database as opposed to file paths is a option. When you have the choice and a file system avaiable? Then for sure go with file paths. It also means for archive, backup, and searching the PDF's etc you can use a truckload of other search tools. If you tie up the files in the database then your options become very limited.

Another really big use case for storing in the database? Thumb nails and previews. In may cases for such tiny files and you have this "extra" preview file for every file on the system? Then you can elminate file clutter and for every say PDF file, then the preview thumbnail file would cause a massive file count bloating. So for small files, then again a use case can be made for storing in the database. For example, on this web page while the PDF files are stored in a big PDF file folder system + hierarchy? I did not want to mess up all those files with a jpg or PNG file preview.

In above, the PDF preview thumbnail is stored in the database. This thus does NOT clutter up the nice PDF and folders with a preview file. And there are other systems that scan/read and take actions on files dropped into the file folders. So once again, external files hands down allows more options and even 3rd party software that can do things on those files without having to use the database (that the 3rd party tools may not even support).

So storing files or images in a database? For lots of small files, then a good case can be made for storing in the database since it can reduce file and folder clutter by huge amounts.

So I ended up doing a little bit of a workaround on this.

I first set up a FileTable based on the feedback from Yari and Guzman in the comments above.

I was having a hard time connecting Access to the FileTable directly, so I used a function to essentially create a directory within the FileTable directory based on the Unique ID of the Record.

Basically, I used the following vba code to make it so Access will automatically make a directory with the name of the ID (unless it already exists), and then open that same directory.

This achieves the same thing I was trying to accomplish without the need to connect to the FileTable directly.

The function creates a new directory at the designated string. The button click command uses that function with the designated parent directory, with the new folder being named after the Unique Identifier of the current record (if it does not exist). It then opens that folder so attachments etc. can be added.

Sub MakeFolder(strPath As String)
    If Dir(strPath, vbDirectory) > vbNullString Then
        Exit Sub
    End If
    MkDir strPath
End Sub

Private Sub Command5_Click()
 MakeFolder "\\ServerName\MSSQLSERVER\FileStream\DocumentStore\" & Me.ID
 Shell "explorer.exe" & " " & "\\ServerName\MSSQLSERVER\FileStream\DocumentStore\" & Me.ID, vbNormalFocus
End Sub

The end result is then present on the SQL FileTable as I originally wanted.