Efficiently Storing Images with Access and SQL Server

Question

I am currently working on a project where I will be creating a relational database in Microsoft SQL Server with Access serving as the front end for users to input information.

One of the requests the users had was to allow image files to be uploaded that would be linked to a specific record.

While I know Access has the ability to allow attachments such as image files to be placed into records, from what I have read it sounds like this is a pretty inefficient way of storing files and could slow down performance in the long run.

I have seen many people suggest storing the image file location as the actual entry, and storing the image on the file system instead.

The question I have is whether there would be a good way to basically allow users to upload images through Access, but have those images saved on the filesystem and just make the record automatically turn into the file location. (In other words, the file would be automatically saved and given an ID in a specific folder, and the record itself would contain the file path)

I am open to other ideas as long as they would still be scalable and work efficiently. I have seen some ideas such as have the file saved to a separate table with an automatically generated idea, but I was not sure if that would be something that would work well.

Any advice would be appreciated, whether that would be methods for achieving the same kind of results through either Access or SQL Server. I also know how to code a bit in Python3 if that would be an easier way to achieve this, but as much as possible I would like for the end user to be able to achieve everything through one interface.

Answer 1

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.

Answer 2

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.