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.
26
