Problem in Upload image from MS Access to Google Drive using API

Question

In my MS Access database I want to upload my customer profile photo to my Google Drive using HTTPS Post request with Google Drive API in VBA. I successfully uploaded photo in my Google Drive but the format is not supported. For uploading photo using https post request I need to convert the image into Base64 string. After uploading I can't see or open the photo until I download the photo and rename the file extension from "jpg" to "txt". After renaming the file I can open the file in Notepad and see the Base64 string. If I convert the Base64 string then I can see the photo. How can I convert Base64 image? I use HTML img tag like this:

<!DOCTYPE html>
<head>
</head>
    <body>
       <img src="data:image/jpg;base64,/9j/4QEcRXhpZgAATU0AKgA(This is the Base64 text................" alt=""> 
    </body>
</html>

This way I can see the photo. My VBA code for uploading image to Google Drive is:

Option Compare Database

Sub UploadFileToGoogleDrive71()
    Dim imageFile As String
    Dim imageBytes() As Byte
    Dim base64String As String
    Dim boundary As String
    Dim request As Object
    Dim accessToken As String
    
    ' Your access token
    accessToken = "ya29.a0Ad52N3_EtFDYr_3lTO-i1P0sNbqgUXzvp..........."
    
    ' Path to your image file
    imageFile = Forms!PatientFormF2!PatientPhotoPath.Value
    
    ' Read the image file into a byte array
    Open imageFile For Binary As #1
    ReDim imageBytes(LOF(1) - 1)
    Get #1, , imageBytes
    Close #1
    
    ' Encode the byte array as a Base64 string
    base64String = EncodeBase64(imageBytes)
    
    ' Construct the boundary for the multipart/form-data request
    boundary = "---------------------------" & Format(Now, "hhmmss") & "abcd"
    
    ' Create the HTTP request object
    Set request = CreateObject("MSXML2.XMLHTTP")
    
    Dim folderId As String
    ' Set the folder ID where you want to upload the photos
    folderId = "1EptP5DEg_m2DE1N67sQ........"
    
    ' Set up the request
    request.Open "POST", "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart", False
    request.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
    request.setRequestHeader "Content-Length", Len(postData) ' Set the Content-Length header
    request.setRequestHeader "Authorization", "Bearer " & accessToken ' Set the Authorization header
    
    ' Construct the request payload
    Dim requestData As String
    requestData = "--" & boundary & vbCrLf
    requestData = requestData & "Content-Type: application/json; charset=UTF-8" & vbCrLf & vbCrLf
    requestData = requestData & "{""name"": ""uploaded_image.jpg"", ""parents"": [""" & folderId & """]}" & vbCrLf & vbCrLf
    requestData = requestData & "--" & boundary & vbCrLf
    requestData = requestData & "Content-Type: image/jpeg" & vbCrLf & vbCrLf
    requestData = requestData & base64String & vbCrLf
    requestData = requestData & "--" & boundary & "--"
    
    ' Send the request
    request.Send requestData

    ' Check the response
    If request.status = 200 Then
        MsgBox "File uploaded successfully!"
    Else
        MsgBox "Error uploading file: " & request.StatusText
    End If
End Sub

Function EncodeBase64(data() As Byte) As String
    Dim objXML As Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    Dim objNode As Object

    ' Convert byte array to base64 string
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = data
    EncodeBase64 = objNode.Text

    Set objNode = Nothing
    Set objXML = Nothing
End Function

I want to upload image from my MS Access database using Google Drive API and able to see the photo directly in the Google Drive and also able to see the photo by downloading them.

Review stackoverflow.com/q/60890492/7296893. When using multipart/form-data you should not use base64 encoding but directly use the image bytes. — Erik A
– Erik A, Commented May 1, 2024 at 11:45
I review the code but i don't understand it. is that code will work in ms access or i remove the Base64 encoding only — Fakir Rifat Hasan
– Fakir Rifat Hasan, Commented May 1, 2024 at 12:17

Fakir Rifat Hasan · Accepted Answer · 2024-05-01 17:40:05Z

I Solve the problem

Sub UploadFileToGoogleDrive71()
    Dim imageFile As String
    Dim imageBytes() As Byte
    Dim base64String As String
    Dim boundary As String
    Dim request As Object
    Dim accessToken As String
    
    ' Your access token
    accessToken = "ya29.a0AXooCgtBXWyRhS............."
    
    ' Path to your image file
    imageFile = Forms!PatientFormF!PatientPhotoPath.value
    
    ' Read the image file into a byte array
    Open imageFile For Binary As #1
    ReDim imageBytes(LOF(1) - 1)
    Get #1, , imageBytes
    Close #1
    
    ' Encode the byte array as a Base64 string
    base64String = EncodeBase64(imageBytes)
    
    ' Construct the boundary for the multipart/form-data request
    boundary = "---------------------------" & Format(Now, "hhmmss") & "abcd"
    
    ' Create the HTTP request object
    Set request = CreateObject("MSXML2.XMLHTTP")
    
    Dim folderId As String
    ' Set the folder ID where you want to upload the photos
    folderId = "1EptP5DEg_m2DE............"
    
    ' Set up the request
    request.Open "POST", "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart", False
    request.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
    request.setRequestHeader "Content-Length", Len(postData) ' Set the Content-Length header
    request.setRequestHeader "Authorization", "Bearer " & accessToken ' Set the Authorization header
    
    ' Construct the request payload
    Dim requestData As String
    requestData = "--" & boundary & vbCrLf
    requestData = requestData & "Content-Type: application/json; charset=UTF-8" & vbCrLf & vbCrLf
    requestData = requestData & "{""name"": ""uploaded_image.jpg"", ""parents"": [""" & folderId & """]}" & vbCrLf & vbCrLf
    requestData = requestData & "--" & boundary & vbCrLf
    requestData = requestData & "Content-Type: image/jpeg" & vbCrLf
    requestData = requestData & "Content-Transfer-Encoding: base64" & vbCrLf & vbCrLf ' Add Content-Transfer-Encoding header
    requestData = requestData & base64String & vbCrLf
    requestData = requestData & "--" & boundary & "--"
    
    ' Send the request
    request.send requestData

    ' Check the response
    If request.status = 200 Then
        MsgBox "File uploaded successfully!"
    Else
        MsgBox "Error uploading file: " & request.StatusText
    End If
End Sub

Function EncodeBase64(data() As Byte) As String
    Dim objXML As Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    Dim objNode As Object

    ' Convert byte array to base64 string
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = data
    EncodeBase64 = objNode.Text

    Set objNode = Nothing
    Set objXML = Nothing
End Function

You should also explain what the solution was to help other readers, not just paste the code.

Collectives™ on Stack Overflow

Problem in Upload image from MS Access to Google Drive using API

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related