MS Access Attachments to SQL Server – Images or Varchar(max)

ms accesssql server

I am trying to move a database from MS Access to SQL Server 2014 and I am using SSMA for Access. A few of the tables have attachments (pdfs, jpgs, emails, etc.).

When I move it to SQL Server, the attachments show up as File Names:

Is there anyway to do this from MS Access side or SQL Server side?

I downloaded all the files to my system and was thinking of matching the names with the files in the folder to upload them to the database through SQL, but then there are multiple files for one row in some instances, so basically I'm not sure how to go about it.

Best Answer

If the files was in the same sql server you can you the OPENROWSET like this

Select * from Openrowset(Bulk 'C:\HaxLogs.txt', SINGLE_BLOB) as a

Or in the remote server

Select * from Openrowset(Bulk '\\server\backup\CSVcontagem_SEND_Akarollynegs.csv', SINGLE_BLOB) as a

Related Solutions

Sql-server – Pull Access schema into a SQL Server table

The way we solved this was to use a template db to auto create views against the access linked tables. Each view was created as

CREATE VIEW AS 
SELECT *
  FROM LinkedServer...Table

Once these views had been created we could pull the column listing from sys.columns joined over to sys.views.

SELECT v.name, c.name
  FROM sys.columns c 
       JOIN sys.views v ON c.object_id = v.object_id 
 WHERE v.name = @viewname

We could then insert this into the table we needed to work with.

SQL Server Data Folder Access Rights – Configuration Guide

Have you looked under Advanced Security Settings?

The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2.

For the SQL Server Root Directory, I'll give full control to the SQL Server Process account.

Best Answer

Related Solutions

Sql-server – Pull Access schema into a SQL Server table

SQL Server Data Folder Access Rights – Configuration Guide

Related Question