Uploading file from Access to SQL Server with DAO

I have an Access 2016 database being used as the frontend for a SQL Server 2008 Express instance. The WATER_FILES table exists in the SQL Server database and includes a column Binary_File of type varbinary(MAX).

I'm attempting to write a VBA sub routine that will upload a file into that column. The following code causes an error

Object Required

at runtime. This code is based on this incomplete page: https://msdn.microsoft.com/en-us/vba/access-vba/articles/work-with-attachments-in-dao#

What am I missing?

Dim dbsGMEC As DAO.Database
Dim rstWater_Files As DAO.Recordset

Set dbsGMEC = CurrentDb
Set rstWater_Files = dbsGMEC.OpenRecordset("dbo_WATER_FILES", dbOpenDynaset, dbSeeChanges)

'   Unclear if a single record should be opened
'    Dim strSQL As String
'    strSQL = "SELECT * FROM dbo_WATER_FILES WHERE OBJECTID = '2'"
'    Set rstWater_Files = dbsGMEC.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

' Activate edit mode.
rstWater_Files.Edit

' Instantiate the child recordset.
Dim rstFiles As DAO.Recordset

' THIS LINE GIVES THE ERROR "Runtime Error '424': Object Required"
Set rstFiles = rstWater_Files.Fields("Binary_File").Value

' Add a new attachment.
rstFiles.AddNew
rstFiles.Fields("FileData").LoadFromFile "C:\test.jpg"
rstFiles.Update

' Update the parent record
rstWater_Files.Update

The Set keyword is used for setting a variable to an object reference. Your .Value isn't an object, it's Null. Hence the Object Required error.

The code you've found is used for Access attachment fields. Varbinary(Max), however, is not an attachment field, but maps to an OLE object in Access/DAO. That means you need to set the value to a bytearray containing the file data, instead of using a nested recordset to manage attachments.

There are many ways to load a file into a bytearray. I prefer the following code, that uses an ADODB.Stream object.

Dim dbsGMEC As DAO.Database
Dim rstWater_Files As DAO.Recordset

Set dbsGMEC = CurrentDb
Set rstWater_Files = dbsGMEC.OpenRecordset("dbo_WATER_FILES", dbOpenDynaset, dbSeeChanges)
rstWater_Files.Edit
Dim strm As Object
Set strm = CreateObject("ADODB.Stream")
strm.Type = 1 'adTypeBinary
strm.Open
strm.LoadFromFile "C:\test.jpg"
rstWater_Files.Fields("Binary_File").Value = strm.Read
strm.Close
rstWater_Files.Update

To store it back to a file:

With CreateObject("ADODB.Stream")
    .Type = 1 'adTypeBinary
    .Open
    .Write rstWater_Files.Fields("Binary_File").Value
    .SaveToFile "C:\testcopy.jpg", 2 'adSaveCreateOverWrite
    .Close
End With

If you really don't like ADODB, and even the thought of an ADODB.Stream disgusts you, you can also use VBA itself to read a file into a bytearray:

Dim dbsGMEC As DAO.Database
Dim rstWater_Files As DAO.Recordset

Set dbsGMEC = CurrentDb
Set rstWater_Files = dbsGMEC.OpenRecordset("dbo_WATER_FILES", dbOpenDynaset, dbSeeChanges)
rstWater_Files.Edit
Dim byteArr() As Byte
Dim fileInt As Integer: fileInt = FreeFile
Open "C:\test.jpg" For Binary Access Read As #fileInt
ReDim arr(0 To LOF(fileInt) - 1)
Get #fileInt, , byteArr
Close #fileInt
rstWater_Files.Fields("Binary_File").Value = byteArr
rstWater_Files.Update

This last code will cap the max file size of 2,147,483,647 bytes (the max size of a Long). However, this is also the max size of an Access database, so you'll likely run into trouble before that. This code also doesn't use chunking, so it might use more memory than required.