Retriving Blobs to File into a table

hello All,

I am migrating my access DB into a sql server. One of the big issues i have come across is the storage of screenshots. this is imperative and is used as audit for a challenge process.

I have been able to store the pasted screenshot to file, using a function "BlobToFile".
- In a form a user copy and pastes a screenshot
- Once a user confirms the screenshot, the function (below) pushes the object to the company shared drive and in the background, the file name is stored in another text file [SupportName]

This works fine and so does the the function BlobToFile when is is able to recall the blob from the [SupportName].

The issue is, the BlobToFile and the FiletoBlob seem to only work on the userform and on one record. I would like to recall all the files to a table on any given day (in order to run reports off of it) and then i can go ahead and delete the rows after all the reports have been run (knowing that all of the images are stored on the disk and the [SupportName] stores the file path]

Is there any way i am able to bulk import all the necessary images i require? I appreciate this is difficult over the net but that is the crux of the matter, being able to import all the images into a table, in access, as an OLE object, to run reports.


Here are the functions:

---------------------------------------------------------

Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long
On Error GoTo BlobToFileError
Dim nFileNum As Integer
Dim abytData() As Byte
BlobToFile = 0
nFileNum = FreeFile
Open strFile For Binary Access Write As nFileNum
abytData = Field
Put #nFileNum, , abytData
BlobToFile = LOF(nFileNum)

BlobToFileExit:
If nFileNum > 0 Then Close nFileNum
Exit Function

BlobToFileError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
"Error writing file in BlobToFile"
BlobToFile = 0
Resume BlobToFileExit

End Function


-------------------------------------------------------

Public Sub FileToBlob(strFile As String, ByRef Field As Object)
On Error GoTo FileToBlobError

If Len(Dir(strFile)) > 0 Then
Dim nFileNum As Integer
Dim byteData() As Byte

nFileNum = FreeFile()
Open strFile For Binary Access Read As nFileNum
If LOF(nFileNum) > 0 Then
ReDim byteData(1 To LOF(nFileNum))
Get #nFileNum, , byteData
Field = byteData
End If
Else
MsgBox "Error: File not found", vbCritical, _
"Error reading file in FileToBlob"
End If

FileToBlobExit:
If nFileNum > 0 Then Close nFileNum
Exit Sub

FileToBlobError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
"Error reading file in FileToBlob"
Resume FileToBlobExit

End Sub


------------------------------------------



Public Function FileToBlobFunct(strFile As String)
On Error GoTo FileToBlobFunctError

If Len(Dir(strFile)) > 0 Then
Dim nFileNum As Integer
Dim byteData() As Byte

nFileNum = FreeFile()
Open strFile For Binary Access Read As nFileNum
If LOF(nFileNum) > 0 Then
ReDim byteData(1 To LOF(nFileNum))
Get #nFileNum, , byteData
FileToBlobFunct = byteData
End If
Else
MsgBox "Error: File not found", vbCritical, _
"Error reading file in FileToBlob"
End If

FileToBlobFunctExit:
If nFileNum > 0 Then Close nFileNum
Exit Function

FileToBlobFunctError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
"Error reading file in FileToBlob"
Resume FileToBlobFunctExit
End Function