Cannot install VBA-Web into Access database

[johnlage]

The current installer doesn't allow you to install VBA-Web into Non-Excel files. We would like to use this in Access and it would be easier to install or update automatically, instead of adding the source files manually to the project.

[timhall]

Hi @janandjohn3 just noticed that Access is in the Office 2016 preview, so I can work on this (I didn't have access to Access previously and had relied on others for checking compatibility).

[timhall]

@janandjohn3 I've added initial Access support to the installer. It turns out automating Access is pretty different from Excel, but I've gotten it working well when testing locally. If you would like to give it a try and reopen this issue if you run into any problems.

[markhern]

Hi, thanks for this fantastic piece of work. I'm also interested in getting VBA-web to work in Access, actually Access 2007. We have a REST interface service that we'd like to access from an Access application. Would you recommend against using VBA-web to do this or do you have any advice on which direction to go? Thank you kindly.

[joyfullservice]

@markhern - I use it extensively in Microsoft Access, and have had really good success with it. I have a couple wrapper functions that I add to handle the authentication and persistence, but the VBA-Web framework makes it a breeze!

[markhern]

@joyfullservice Thanks. If you could direct me to any instructional articles/materials as I take this path, it would be highly appreciated. We'd also like to use JSON in the REST web service, which I assume would be supported. Thanks again.

[Sophist-UK]

http://vba-tools.github.io/VBA-Web/docs/

[Sophist-UK]

JSON is supported - if you need help please email me.

[joyfullservice]

In Access you can actually create library databases, which you can then reference from other Access database applications. When you have a lot of front-end applications that use the same underlying classes for data access or functionality like VBA-Web, you can use this approach to maintain your code in a single location, instead of needing to maintain duplicate copies of the modules in all the databases.

The way I use VBA-Web is to create a library database that includes the VBA-Web classes and modules, then set all the instancing on the class modules to public, as demonstrated in the function below:

'---------------------------------------------------------------------------------------
' Procedure : SetAllClassesPublic
' Author    : Adam Waller
' Date      : 4/9/2015
' Purpose   : Set all class modules to be public, using an undocumented value.
'---------------------------------------------------------------------------------------
'
Public Sub SetAllClassesPublic()
    Const vbext_ct_ClassModule = 2
    Const PUBLIC_CREATABLE As Integer = 5
    Dim cls As VBComponent
    For Each cls In VBE.ActiveVBProject.VBComponents
        If cls.Type = vbext_ct_ClassModule And Left(cls.Name, 1) <> "z" Then
            cls.Properties("Instancing") = PUBLIC_CREATABLE
        End If
    Next cls
End Sub

At that point I can then reference that library database in my database application by adding a reference (Tools->References) to the library database. (Don't forget to show All Files when browsing for the file.)

Now, in my Access Application I can simply create an instance of the class without needing a duplicate copy of the VBA-Web classes in my application.

I use the same principle when it comes to the core functionality of the REST API. Instead of having to code all the overhead for logging in, maintaining a session, showing a login dialog, etc..., I wrap all this up in a nice library database, and just reference that from my application.

With all this overhead work done, it is really easy to create Access applications that interact with a specific system via REST API, and the code ends up being very minimal because it has been organized into referenced libraries rather than trying to maintain all kinds of duplicated code.

Here is an example of what this might look like:

'---------------------------------------------------------------------------------------
' Procedure : ExampleMoveItemToWarehouse
' Author    : Adam Waller
' Date      : 3/30/2016
' Purpose   : Move an item to another warehouse.
'---------------------------------------------------------------------------------------
'
Private Function ExampleMoveItemToWarehouse() As Long

    Dim cRequest As WebRequest
    Dim cResponse As WebResponse
    Dim cData As Dictionary

    ' Use my wrapper function to create a Web Request
    ' with preset authentication details and default settings.
    Set cRequest = SE_API.NewWebRequest
    With cRequest
        .Resource = "/inventorytransactions"
        .Method = HttpPost
        .AddBodyParameter "type", "T"
        .AddBodyParameter "product", "PTS"
        .AddBodyParameter "warehouse", "EVENT"
        .AddBodyParameter "quantity", 1
        .AddBodyParameter "location", "ALL"
        .AddBodyParameter "fromLocation", "ALL"
        .AddBodyParameter "fromWarehouse", "OB"
    End With
    Set cResponse = SE_API.WebClient.Execute(cRequest)

    With cResponse
        If .StatusCode = Created Then
            Set cData = .Data
            ' Return transaction ID.
            ExampleMoveItemToWarehouse = cData("id")
        Else
            SE_API.LogAPIError "Failed to add inventory transaction", .Content & vbCrLf & cRequest.Body, "SE Data", "clsProdWarehouse.ExampleMoveItemToWarehouse"
            Debug.Print PrettyPrintJson(.Content, 2)
        End If
    End With

End Function

As you can see, the code ends up very simple and readable, allowing me to focus on the logic of what I am doing, rather than the overhead of setting default properties and authentication headers. (SE_API is an instance of my wrapper class that handles the overhead details relating to my interaction with our REST API system.)

Many will not need to take this complicated of an approach, but in our case I found the library databases to be an effective way of maintaining the core code used by dozens of Access applications that interact with our core ERP system.

Attached are copies of a couple of these library databases to give you some ideas on how some of these things can be implemented... (Note in particular the clsSession class in SE_API which handles the authentication tokens and login expiration.)

Example.zip

[Sophist-UK]

That is much better specific help for Access than I could provide!!!!

However, I have suggested that to Tim that VBA-Web should include an Excel Add-On .XLA/.XLAM file containing VBA-Web so that you could load the Add-on in Excel once, and then use it as needed in your workbooks. From what @joyfullservice says, perhaps we could add an Access Library which did the same thing.

[markhern]

Thanks everyone so much. I am digesting all this and moving forward with it as I can.

[markhern]

I'm finding now in my organization that in fact we have to support Access 2000 as a 'lowest common denominator'. The Installer file appears to only support ACCDB files, telling at least from the file extension filter in the Browser button. I'll open a new issue around this.