Attachments

[CLSkcab]

Background: Frontend MS Access 2010/ VBA; Backend MS SQL Server 2008. Learning Access VBA and SQL

I have an Access application that needs to have the backend DB store an attachment. The Access code is written in VBA. How can this be accomplished? I would like to do it much like a native Access program. Please see attachment.

The best I could find was to create the field in SQL as a varbinary(MAX).

Thanks in advance.

[twinnyfo]

From what I understand about SQL Server, it does not support attachment type fields in the same way as Access. It might be wisest to try to save the name and location of the file in a text field and then use that field as a pointer to open the file with its native program.

[jforbes]

There are two basic schools of thought when it comes to Attachments and SQL Server.

One is what Twinyfo is talking about, by saving the URL to the Attachment, typically in a NVARCHAR(255). This is probably the most common and definitely the easiest way. Then you can launch the URL with something like http://allenbrowne.com/func-GoHyperlink.html or something like this:

Function OpenAttachment(ByVal sTargetAndLocation As String) As Boolean
    Dim dReturn As Integer
    OpenAttachment = False
    dReturn = Shell("explorer.exe " & sTargetAndLocation, vbNormalFocus)
    If dReturn >= 0 Then OpenAttachment = True
End Function

The other is to save the Attachment into a SQL field and this one is much trickier. Basically, it is using a BLOB/VARBINARY(MAX) or a hybrid type Blob (FileTables and FileStream if you want to Google them) to store the file into the SQL Server Database. I've done this in the past by using VB.NET's Filestream object to store into a VARBINARY(MAX), it was slick but it wasn't easy to figure out. It would be extremely difficult to replicate that code in MS-Access, so I really wouldn't recommend it unless it is a hard requirement. Oh, lastly, to use the Attachment you need to stream the Object back out of the Database into a physical file before it can be read by an external application.

The benefits of storing in SQL is that you wont have to worry about Windows Security on the physical file and you can have everything in one backup so you have little chance of loosing an Attachment. If you can live without these benefits, I would recommend saving the Attachment on a server somewhere and storing the URL in the Database.

I'm sure you'll find other opinions on this subject. Good luck.

[twinnyfo]

@jforbes,

Great explanation for our friend!

@CLSkcab,

Let us know what you decide and if there are any hitches as you go along.

[CLSkcab]

It is too complex for me at this time and it is a low priority item. Thanks anyway.