Access 2007 Attachment Type in SQL 2008R2

Question

I am currently helping a user move her Access DBs to a SQL 2008 backend.

The migration goes smoothly except for the "attachment" function from access. Basically, her user base uploads pdf, doc, xls etc. from shared drives into the database. I've done some research and noticed that the varbinary(max) data type in SQL might be able to mimic this, however the attachment functionality of her front end Access program still doesn't seem to work when I change the data type. 

Any suggestions would be much appreciated.

Kyle

Answer 1

I should note that traditionally one stores the actual document in a Shared Drive on the Server and only stores the path to said Drive in the database.  The reason being storing the actual documents within the database does cause it to reach the 2 gig limit (Access) or the 10 gig limit (free SQL Server) more quickly.

All that said, the developer may be hesitant but I don't see any other choice besides the one I mentioned above.

Answer 2

Yes, we are actually looking into it now. My access developer is hesitant to change code, although that's what I think may have to happen for her application

Answer 3

Yes, I'm using SSMA, I've also tried the traditional upload with SQL by using the upload feature.

Answer 4

There really is no attachment Data Type on the SQL Server.  The closet thing that comes to mine is FILESTREAM Storage, have a look at...

https://msdn.microsoft.com/library/hh461480?f=255&MSPPError=-2147217396

Answer 5

Hi Kyle,

How are you migrating the data into SQL Server? Have you tried using SSMA? Just curious...