• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

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

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?
0
Gary Samuels
Asked:
Gary Samuels
  • 2
  • 2
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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:

http://msdn.microsoft.com/en-us/library/azure/dd135733.aspx

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

                  ' Set the correct URL
                  'strPostURL = "https://test.authorize.net/gateway/transact.dll"
110               strPostURL = "https://secure.authorize.net/gateway/transact.dll"
                  'strPostURL = "https://developer.authorize.net/tools/paramdump/index.php"

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: http://developer.authorize.net
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:

https://myaccount.blob.core.windows.net/?comp=list

 and see what you get back as a response.

Jim.
0
 
Gary SamuelsPlant ManagerAuthor Commented:
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."
    Else
            strDatabasePath = "//192.168.11.4/RetailOrderImages/"
            strImagePath = strDatabasePath & strImagePath
        .Visible = True
        .Picture = strImagePath
        strResult = "Image found and displayed."
    End If
End With
    
Exit_DisplayImage:
    DisplayImage = strResult
    Exit Function

Err_DisplayImage:
    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 = "//192.168.11.4/RetailOrderImages/"

I think need a way to change the local path to a URL:
strDatabasePath = "StorageName.blob.core.windows.net/ContainerName/


How could I user your example to do this?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

Jim.
0
 
Gary SamuelsPlant ManagerAuthor Commented:
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., http://ptmfg.blob.core.windows.net/testimages/062314962.jpg 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 = "http://ptmfg.blob.core.windows.net/testimages/062314962.jpg"
ImageContainer.picture = strImagePath
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now