I'm trying to store files on a linked SQL Server table, much the way I would using the Attachment data type in Access. (I couldn't use the Attachment data type, though, due to size restrictions - we're storing about a gig a week.)
Almost everyone says to store the attachments on a file system and save the location - I can't do that because of varying permissions. The files HAVE to be stored in the SQL Server table for security reasons.
So how do I do it? I've tried converting the files to binary and updating the recordset that way, but it always comes back as null. I'm not sure what I'm doing wrong.
This is an amalgom of several different suggestions I found online, none of which I could get quite right. Please help - this is literally make my head swim.
Thanks!
Almost everyone says to store the attachments on a file system and save the location - I can't do that because of varying permissions. The files HAVE to be stored in the SQL Server table for security reasons.
So how do I do it? I've tried converting the files to binary and updating the recordset that way, but it always comes back as null. I'm not sure what I'm doing wrong.
Code:
Public Function Test(strFile As String) As Boolean
Dim rsAtts As dao.Recordset
Dim ifilenum As Double
Dim btAR() As Byte
Set db = CurrentDb
Set rsAtts = db.OpenRecordset("tblAttachments")
ifilenum = FreeFile
Open strFile For Binary Access Read As ifilenum
Get ifilenum, 1, btAR()
Close ifilenum
With rsAtts
.AddNew
!lngInquiryID = 1003
!attFile = btAR() ' This is the varbinary field in the SQL database
!attname = Dir(strFile)
.Update
.Close
End With
Set rsAtts = Nothing
Set db = Nothing
Test = True
End Function
This is an amalgom of several different suggestions I found online, none of which I could get quite right. Please help - this is literally make my head swim.
Thanks!