MS Access VBA - Image Upload to Google Drive API Results in Wrong File Format

Other Questions Google drive
1

I’m working with MS Access and trying to upload customer images to Google Drive through their API using VBA. The upload process works but there’s a formatting issue that’s driving me crazy.

When I upload the image file, it appears in my Google Drive but can’t be opened as a normal image. To actually see the photo, I have to download it first and change the file extension from jpg to txt. Then I can open it in Notepad and see it’s just a Base64 string. The only way I can view the actual image is by putting this Base64 string into an HTML file like this:

<!DOCTYPE html>
<html>
<head></head>
<body>
    <img src="data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQH/2wBDAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQH/wAARCAABAAEDASIAAhEBAxEB/8QAFQABAQAAAAAAAAAAAAAAAAAAAAv/xAAUEAEAAAAAAAAAAAAAAAAAAAAA/8QAFQEBAQAAAAAAAAAAAAAAAAAAAAX/xAAUEQEAAAAAAAAAAAAAAAAAAAAA/9oADAMBAAIRAxEAPwA/AD/2Q==" alt="Photo">
</body>
</html>

Here’s my VBA code that handles the upload:

Option Compare Database

Sub TransferImageToGoogleDrive()
    Dim photoPath As String
    Dim fileData() As Byte
    Dim encodedImage As String
    Dim requestBoundary As String
    Dim httpRequest As Object
    Dim authToken As String
    
    authToken = "ya29.a0Ad52N3_EtFDYr_3lTO-i1P0sNbqgUXzvp..........."
    
    photoPath = Forms!CustomerForm!ImagePath.Value
    
    Open photoPath For Binary As #1
    ReDim fileData(LOF(1) - 1)
    Get #1, , fileData
    Close #1
    
    encodedImage = ConvertToBase64(fileData)
    
    requestBoundary = "---------------------------" & Format(Now, "hhmmss") & "xyz"
    
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    
    Dim targetFolder As String
    targetFolder = "1EptP5DEg_m2DE1N67sQ........"
    
    httpRequest.Open "POST", "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart", False
    httpRequest.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & requestBoundary
    httpRequest.setRequestHeader "Content-Length", Len(payloadData)
    httpRequest.setRequestHeader "Authorization", "Bearer " & authToken
    
    Dim payloadData As String
    payloadData = "--" & requestBoundary & vbCrLf
    payloadData = payloadData & "Content-Type: application/json; charset=UTF-8" & vbCrLf & vbCrLf
    payloadData = payloadData & "{""name"": ""customer_photo.jpg"", ""parents"": [""" & targetFolder & """]}" & vbCrLf & vbCrLf
    payloadData = payloadData & "--" & requestBoundary & vbCrLf
    payloadData = payloadData & "Content-Type: image/jpeg" & vbCrLf & vbCrLf
    payloadData = payloadData & encodedImage & vbCrLf
    payloadData = payloadData & "--" & requestBoundary & "--"
    
    httpRequest.Send payloadData

    If httpRequest.status = 200 Then
        MsgBox "Upload completed successfully!"
    Else
        MsgBox "Upload failed: " & httpRequest.StatusText
    End If
End Sub

Function ConvertToBase64(byteArray() As Byte) As String
    Dim xmlDoc As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Dim xmlElement As Object

    Set xmlElement = xmlDoc.createElement("encode")
    xmlElement.DataType = "bin.base64"
    xmlElement.nodeTypedValue = byteArray
    ConvertToBase64 = xmlElement.Text

    Set xmlElement = Nothing
    Set xmlDoc = Nothing
End Function

What am I doing wrong here? I want the images to upload properly so they can be viewed directly in Google Drive without all this extra work.

2

The problem is your multipart request structure. You’re sending Base64 encoded data in the body, but Google Drive wants raw binary data for the file content. VBA can’t properly mix binary data with string concatenation. Try the simple upload method instead - add uploadType=media to your URL and send the raw fileData directly as the request body. Ditch the multipart boundary setup completely and just send the binary data with Content-Type set to image/jpeg. This fixed the same issue I had with Office automation uploading to cloud services.

3

Your problem is mixing binary data with string concatenation in the multipart request. When you use the & operator to combine your base64 image with boundary strings, VBA converts everything to text instead of keeping the binary format. Google Drive gets a text file with base64 data rather than actual image bytes. I hit this same issue building a document management system with cloud storage APIs. Skip multipart uploads and use the simple upload method instead. Change your URL to include uploadType=media, ditch all the boundary stuff, and send the raw fileData array directly as the request body. Set the file name and parent folder with query parameters instead of JSON metadata. This fixes the string concatenation problem and ensures Google Drive gets proper binary image data.

4

u r on the right track, but ur uploading base64 when u should send raw binary data. google drive wants the image data directly - skip base64 conversion and send fileData as-is. that’ll fix ur format issue!