Solved upload file to blob field in mysql BE (1 Viewer)

zazar

New member
Local time
Today, 15:07
Joined
Jul 8, 2020
Messages
14
Hi everyone,
I create a simple database wich works great with few users. Recently we needed to move the back-end to MySql and just after this they aks me to allow users to upload file to the database. Moving the db to MySql weren't so difficult (i read a lot of thread). But now in the table "report" I created a blob field (mediumblob field actually, since each file wouldn't exceed 5Mo) and I have a form with select and upload button, and another one to download back the file later, the only thing that missing is the code behind those 2 last buttons. I read many and many of post, as i did to build the app, but didn't find the solution. (I'm on it since sunday, trying many thing out).
I found some code to select and upload file into local table but nothing help me to upload file to MySql back-end and download it back when there is need.

I already have this :
Private sub btnSelect_Click ()
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)
With f
With .Filters
.Clear
.Add "", "*.pdf", 1
End With
.InitialFileName = ""
.AllowMultiSelect = False
If f.Show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder: " & strFolder & vbCrLf & _
"File: " & strFile
Me.filepath.Value = strFolder & strFile
Next
End If
Set f = Nothing
End With
End Sub


uploadaccess.png

Now i need this :

Private sub btnUpload_Click ()
'here the code to upload it
End sub


and :

Private sub btnDownload_Click ()
'here the code to download the file from MySql to local disk (the targer folder could be like "C:\downloads")
End Sub


I post this here and not in a MySql space because I think all the code will be on the access side.
Already thanks to everyone who can help me with this.
 

cheekybuddha

AWF VIP
Local time
Today, 14:07
Joined
Jul 21, 2014
Messages
557
Hi,

Are you using linked tables?

I dug these old functions I wrote out of my code library:
Code:
'***************************************************************************************
' Function  : fDownloadFile
' DateTime  : 01-08-2005 16:26
' Author    : dm
' Purpose   :
'***************************************************************************************
'
Public Function fDownloadFile(strFileNamePath As String, _
                              objFld As Object) As Boolean
On Error GoTo Err_fDownloadFile

  Dim blRet As Boolean
  Dim bBuffer() As Byte
  Dim iFileNo  As Integer

  iFileNo = FreeFile
  Open strFileNamePath For Binary As iFileNo
  ReDim bBuffer(objFld.ActualSize)
  bBuffer = objFld.GetChunk(objFld.ActualSize)
  Put iFileNo, , bBuffer()
  Close iFileNo
  blRet = True
  
Exit_fDownloadFile:
  fDownloadFile = blRet
  Exit Function

Err_fDownloadFile:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: fDownloadFile" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basLoadFile", , "Error: " & Err.Number
  End Select
  Resume Exit_fDownloadFile

End Function

'***************************************************************************************
' Function  : fLoadFileToTable
' DateTime  : 01-08-2005 16:26
' Author    : dm
' Purpose   :
'***************************************************************************************
'
Public Function fLoadFileToTable(strFileNamePath As String, _
                                 objFldToFill As Object) As Boolean
On Error GoTo Err_fLoadFileToTable

  Dim blRet As Boolean
  Dim bBuffer() As Byte
  Dim iFileNo  As Integer

  iFileNo = FreeFile
  Open strFileNamePath For Binary As iFileNo
  ReDim bBuffer(LOF(iFileNo))
  Get iFileNo, , bBuffer
  objFldToFill.AppendChunk bBuffer
  Close iFileNo
  blRet = True
  
Exit_fLoadFileToTable:
  fLoadFileToTable = blRet
  Exit Function

Err_fLoadFileToTable:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: fLoadFileToTable" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basLoadFile", , "Error: " & Err.Number
  End Select
  Resume Exit_fLoadFileToTable

End Function

IIRC(!), You can pass either a DAO or ADODB Recordset.Field object, so if you are using linked tables you can open a recordset based on your table and pass the appropriate path and field object as you iterate through.

hth,

d
 

cheekybuddha

AWF VIP
Local time
Today, 14:07
Joined
Jul 21, 2014
Messages
557
You can also use ADODB.Stream too.

See here or here
 

zazar

New member
Local time
Today, 15:07
Joined
Jul 8, 2020
Messages
14
Yes i'm using linking table and thank you, hope I get through this issue with your code and links.
 

zazar

New member
Local time
Today, 15:07
Joined
Jul 8, 2020
Messages
14
Hi guys, I got it worked.
cheekybuddha I followed yours links and from one of those pages to another one, I found the code below :

'Function: BlobToFile - Extracts the data in a binary field to a disk file.
'Parameter: strFile - Full path and filename of the destination file.
'Parameter: Field - The field containing the blob.
'Return: The length of the data extracted.
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


'Function: FileToBlob - Loads a file into a binary field.
'Parameter: strFile - Full path and filename of the source file.
'Parameter: Field - The binary field into which the file is to be loaded.
Public Function 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 Function

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

End Function


I successfully integrated it to my project.

Thanks you all for the help.
 

freuzo

Member
Local time
Today, 15:07
Joined
Apr 14, 2020
Messages
39
Hi,
I was trying to do something like that. Your code helped me a lot.
Thanks.
 

Users who are viewing this thread

Top Bottom