Store file in SQL server (1 Viewer)

[amir0914]

Hi everyone,
newly I migrated from access back-end to SQL Server, and I create all tables with sql server and connect it with access by OCDB. But I have a question, How can I store and read file in sql server like "attachment field type or OLE-Object type in access? For example I want to insert an image or pdf from access form to sql table.
Thanks in advance.

 

[pbaldy]

I think most of us store the path to the file rather than the file itself.

 

[Galaxiom]

They are stored in a varbinary(max) column. The file can be converted to a binary via a Stream and inserted. I've never tried it through an Access linked table but it could be definitely achieved with a passthrough query.

Generally best done by implementing FileStream on the server. (This feature is not installed by default.)

A FileStream can also expose the varbinary column as a read-write Windows File System Share using a FileTable. Files can simply be moved into the share and they become available in the database with the path and other information in other columns. This way you simply copy the file through Windows and presto.

The advantages over storing them in the ordinary file system is that they are far more rapidly accessed and can be Full Text Indexed, supporting contextual searches such as queries asking which files have two words within a certain number words of each other etc. Grammatical variants such as plurals and other tenses of the word are also managed in the query.

Of course files can take up a lot of space in the database but this is not usually an issue in SQL Server which has 10 GB available even in the free Express version or 6 Petabytes (PB = 1024 TeraBytes) in the full version.

 

[amir0914]

Thanks for your response, is there any limit to varbinary(max) column? and do you have any example or sample code to convert file to binary code and insert to varbinary(max) via vba?