Upload/Download files to Azure storage container using MS Access VBA

Posted on 2014-08-12
Last Modified: 2015-07-17
I've created storage containers on Azure and I have an Access 2013 database running on an Azure Windows 8 Virtual Machine. How do I use Access VBA to save and load images to the Azure storage containers?
Question by:garysamuels
    LVL 56

    Accepted Solution

    OK since no one has bit at this, I'm going to offer a couple of things after digging around.

    First, I want to point out that I have not done this myself, but I think I can steer you in the right direction at the very least.

    You have two paths:

    1. Write an external DLL in Visual Studio to make .Net calls, and call that from Access.

    2. Use the REST API for the Azure Storage Containers.   This is the method I would use.   Here's the API reference:

      In Access, you will set a reference to the Microsoft XML ver 6.0, then execute code like this:

                      ' Set the correct URL
                      'strPostURL = ""
    110               strPostURL = ""
                      'strPostURL = ""

    120               strPostSting = ""
    125        strPostSting = strPostSting & "x_login=" & URLEncode(strAPILogin) & "&"
    126       strPostSting = strPostSting & "x_tran_key=" & URLEncode(strTransactionKey) & "&"
                      'For debugging.
                      'strPostSting = strPostSting & "x_test_request=" & URLEncode("TRUE") & "&"
    130               strPostSting = strPostSting & "x_version=" & URLEncode("3.1") & "&"
    140               strPostSting = strPostSting & "x_delim_data=" & URLEncode("TRUE") & "&"
    150               strPostSting = strPostSting & "x_delim_char=" & URLEncode("|") & "&"
    160               strPostSting = strPostSting & "x_relay_response=" & URLEncode("FALSE") & "&"
    170               strPostSting = strPostSting & "x_email_customer=" & URLEncode("FALSE") & "&"

    180               strPostSting = strPostSting & "x_type=" & URLEncode("PRIOR_AUTH_CAPTURE") & "&"
    190               strPostSting = strPostSting & "x_trans_id=" & URLEncode(rs!CCTransactionID) & "&"

                      ' Additional fields can be added here as outlined in the AIM integration
                      ' guide at:
    200               strPostSting = left(strPostSting, Len(strPostSting) - 1)

                      ' We use xmlHTTP to submit the input values and record the response
                      Dim objRequest As New MSXML2.XMLHTTP
    210               objRequest.Open "POST", strPostURL, False
    220               objRequest.Send strPostSting
    230               strPostResponse = objRequest.responseText
                      'Debug.Print strPostResponse
    240               Set objRequest = Nothing

     Which is basically just building up a string, sending that to Azure, and then reacting to the response.

     I would start off with testing something simple, like ListContainers, which looks like this:

     and see what you get back as a response.


    Author Comment

    I've been working with this for a couple of days and I just don't have enough experience to be able to follow it. Let me add some detail as to what I'm try to accomplish.

    Currently I have a local file server where I have stored images. In a form I have an onload procedure that calls this function:

    Public Function DisplayImage(ctlImageControl As control, strImagePath As Variant) As String
    On Error GoTo Err_DisplayImage
    Dim strResult As String
    Dim strDatabasePath As String
    Dim intSlashLocation As Integer
    With ctlImageControl
        If IsNull(strImagePath) Then
            .Visible = False
            strResult = "No image name specified."
                strDatabasePath = "//"
                strImagePath = strDatabasePath & strImagePath
            .Visible = True
            .Picture = strImagePath
            strResult = "Image found and displayed."
        End If
    End With
        DisplayImage = strResult
        Exit Function
        Select Case Err.Number
            Case 2220       ' Can't find the picture.
                ctlImageControl.Visible = False
                strResult = "Can't find image in the specified name."
                Resume Exit_DisplayImage:
            Case Else       ' Some other error.
                MsgBox Err.Number & " " & Err.Description
                strResult = "An error occurred displaying image."
                Resume Exit_DisplayImage:
        End Select
    End Function

    Open in new window

    As you can see the image path does not change so it's hard coded to a local destination:
     strDatabasePath = "//"

    I think need a way to change the local path to a URL:
    strDatabasePath = "

    How could I user your example to do this?
    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Currently I have a local file server where I have stored images. In a form I have an onload procedure that calls this function:>>

     Sorry to take a bit, but I wanted to review in detail.

    The Azure BLOB service seems like the way to go, but unlike what your doing now, it won't appear to you like a disk drive, nor can you directly access it like you could as in a database.

     If you want to continue to use the Blob service for this, your first going to need to store the BLOB data in a file.   Then you'll use your current procedure pretty much as is from that point forward (point the image control to the file).

     The other way to do this would be similar, which would be to use the Database Service (SQL Server) in Azure and store the data in a BLOB field within the data.


    Author Comment

    I used Azure Storage Explorer to upload my public image files into a Blob container. If I place the URI of a blob file into a web browser, e.g., the image will download or will be displayed in the browser depending on which browser you're using. But, if I use the same URI in the Access VBA code it doesn't work. e.g.,

    dim strImagePath as String
    strImagePath = ""
    ImageContainer.picture = strImagePath

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Moving your enterprise fax infrastructure from in-house fax machines and servers to the cloud makes sense — from both an efficiency and productivity standpoint. But does migrating to a cloud fax solution mean you will no longer be able to send or re…
    Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now