Store Attachments in SQL Server (1 Viewer)

willknapp

Registered User.
Local time
Today, 05:42
Joined
Aug 16, 2012
Messages
93
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.

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!
 

MarkK

bit cruncher
Local time
Today, 02:42
Joined
Mar 17, 2004
Messages
8,178
I don't think you can just assign a Byte() array to the value property of a DAO.Field. Check out the AppendChunk() and GetChunk() methods of the DAO.Field object instead.
 

willknapp

Registered User.
Local time
Today, 05:42
Joined
Aug 16, 2012
Messages
93
I don't think you can just assign a Byte() array to the value property of a DAO.Field. Check out the AppendChunk() and GetChunk() methods of the DAO.Field object instead.

I'll be sure to check them out - at one point this was an ADODB recordset, but I'm woefully unskilled in deailng with ADO compared to DAO (my preference.) Perhaps that's where my troubles began.
 

MarkK

bit cruncher
Local time
Today, 02:42
Joined
Mar 17, 2004
Messages
8,178
Here's how you can get the byte array out of a DAO.Field . . .
Code:
[COLOR="Green"]    'declare byte array[/COLOR]
    Dim bytes() As Byte
[COLOR="Green"]    'with previously opened recordset having a OLE Object field called "Blob"[/COLOR]
    With rst.Fields("Blob")
[COLOR="Green"]        'we resize the array[/COLOR]
        ReDim bytes(.ActualSize)
[COLOR="Green"]        'and call the GetChunk method to extract that array from the field[/COLOR]
        bytes = .GetChunk(.ActualSize)
    End With
 

MarkK

bit cruncher
Local time
Today, 02:42
Joined
Mar 17, 2004
Messages
8,178
An ADO.Field has those same methods.
 

billmeye

Access Aficionado
Local time
Today, 05:42
Joined
Feb 20, 2010
Messages
542
I'm a novice, but you should be able to create a field in the table on your SQL server as data type Blob or LongBlob and store the file directly as an OLE Object on your form.
 

willknapp

Registered User.
Local time
Today, 05:42
Joined
Aug 16, 2012
Messages
93
Here's how you can get the byte array out of a DAO.Field . . .
Code:
[COLOR="Green"]    'declare byte array[/COLOR]
    Dim bytes() As Byte
[COLOR="Green"]    'with previously opened recordset having a OLE Object field called "Blob"[/COLOR]
    With rst.Fields("Blob")
[COLOR="Green"]        'we resize the array[/COLOR]
        ReDim bytes(.ActualSize)
[COLOR="Green"]        'and call the GetChunk method to extract that array from the field[/COLOR]
        bytes = .GetChunk(.ActualSize)
    End With
OK - I get this, but how do I store the OLE object in the first place? Is there any way to do it in VBA using a file path and file name?
 

MarkK

bit cruncher
Local time
Today, 02:42
Joined
Mar 17, 2004
Messages
8,178
I thought you said the requirement was to not store it in the file system, you wanted to store it in a table in a database. Obviously saving the file in the file system and storing the path in the database is easier. In the latter case you don't need AppendChunk or GetChunk.
 

willknapp

Registered User.
Local time
Today, 05:42
Joined
Aug 16, 2012
Messages
93
I thought you said the requirement was to not store it in the file system.

That's true - I realize my response was kind of ambiguous. Sorry about that

I solved the problem, though. I used the ADODB.Stream object. It also has LoadFromFile and SaveToFile methods, but is more compatible with the varbinary data type. I'll post the code when I get back to the office on Monday.

Thanks for the help everyone!
 

GBalcom

Much to learn!
Local time
Today, 02:42
Joined
Jun 7, 2012
Messages
459
That's true - I realize my response was kind of ambiguous. Sorry about that

I solved the problem, though. I used the ADODB.Stream object. It also has LoadFromFile and SaveToFile methods, but is more compatible with the varbinary data type. I'll post the code when I get back to the office on Monday.

Thanks for the help everyone!


Whatever happened to that code?!? I could use the help :)
 

patilbhush

New member
Local time
Today, 02:42
Joined
Apr 29, 2019
Messages
2
Hello
Can you share the code I am trying the same thing to get the attachment into SQl from access.

Thank you
 

Minty

AWF VIP
Local time
Today, 09:42
Joined
Jul 26, 2013
Messages
10,355
It's a 5 year old thread, you might get some joy in a response but don't hold your breath.
 

Users who are viewing this thread

Top Bottom