WklGatewayClient: VBA class for the data service

WklGatewayClient lets you call the commands of the data service's (Wkl.Data.Gateway) administration interface from VBA: issue keys, set up the second factor, test connections, and more. It also serves as a template for any VBA application that talks to the service over HTTP.

An Access application reads and edits lists (data sources, rules, people, keys) as linked tables through the service's ODBC driver. Anything that returns a secret or affects more than a single row is a command, and that's what this class is for. The entity sets and commands themselves are described in HTTP: administration (reference) (data service documentation).

Microsoft Access from Microsoft 365, 32-bit and 64-bit, in an ACCDB, an ACCDE, and the Access Runtime. The administration app calls its commands through this class.

1. In the VBA editor, choose File > Import File and import WklGatewayClient.cls (a class module, found in the repository under source/classes/) and, if you want the examples, WklGatewayClientExamples.bas (under client/).
2. Choose Tools > References and select “WKL Office JSON 1.1” (“WKL Access JSON 1.0” up to release 1.0.3).
3. Compile the project (Debug > Compile).

The files use the format the VBA editor exports and imports. Access's LoadFromText expects a different format for a class module (without VERSION 1.0 CLASS … END and without VB_Name); given the export format, it creates a standard module instead. client\tests\Run-AccessTests.ps1 shows how to convert between the two.

Use one instance for the application's entire session, because the instance holds the second-factor session.

Private m_Client As WklGatewayClient
 
Public Function Gateway() As WklGatewayClient
    If m_Client Is Nothing Then
        Set m_Client = New WklGatewayClient
        m_Client.BaseUrl = "https://data.example.com"
        m_Client.SignIn = gwPersonalKey          ' Key from Windows Credential Manager
    End If
    Set Gateway = m_Client
End Function
 
Sub Example()
    Dim answer As JDoc
    On Error GoTo ErrorHandler
 
    Set answer = Gateway().IssueKey("Jane Doe", Label:="Laptop", Delivery:="link")
    Debug.Print answer.GetString("$.link")
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation       ' Error numbers: see section "Errors"
    Resume FinalHandler
End Sub

WklGatewayClientExamples.bas contains every command once, with error handling by error number.

Every sign-in (personal key, service key, password, and Windows account) is sent only over https or to the local computer; the class refuses an http address of another computer with gwErrConfiguration before sending anything. StoreKey saves the key under the same target the ODBC driver uses for an administration connection to the same address (Wkl.Data.OData.Odbc:(persönlicher Schlüssel)@<BaseUrl>/admin/v1/odata/), so one saved key serves both the linked tables and the class. The key is never stored in the database file.

If the service requires a second factor (a 401 response with Wkl-Totp), the class asks for the code, exchanges it for a session, and repeats the request. It gets the code from these sources, in this order:

1. The TotpCode property (used once), for code that prompts for the code itself.
2. The CodeRequired(Message, Code, Cancel) event of an instance declared WithEvents.
3. An InputBox (which also works in the Runtime); turn it off with AskWithInputBox = False.

If the code is wrong, the class asks again, showing the service's message, up to three times. OpenSession starts the session explicitly, and ForgetSession discards it. The ODBC driver keeps its own session. To make one code work for both, the application passes SessionToken to the driver with the SESSION= keyword of a connection it opens itself (this is what the administration app does). Without that handover, the driver and the class each ask for a code once.

If the connection drops without a response, the class retries the request after 1 second and again after 3 seconds. Every write request carries an Idempotency-Key, and the service answers a retry from memory instead of writing a second time. The service remembers the key for 10 minutes and only in memory, so if it restarts between the dropped connection and the retry, a request can arrive twice (service behavior as described in HTTP: administration (reference), section “Many operations at once” (data service documentation)). A request that times out (TimeoutSeconds, 60 by default) isn't retried: the service might still carry it out, and the class has no way of knowing. In that case, read the data back to check whether the change went through.

• It sends keys, passwords, and the Windows sign-in only over HTTPS or to the local computer; otherwise, it refuses with gwErrConfiguration. WinHTTP validates the server certificate against the Windows certificate store, and an invalid certificate makes the request fail; the class never turns that check off.
• It keeps Key and Password only in its instance's memory. A key is saved only when you call StoreKey, and then only in the user's Windows Credential Manager.
• A service key (gwServiceKey) is never stored in code or in the database. The class reads it at run time from the environment variable named in KeyVariable. Anyone who can read that variable (any process running as that user) has the key.

Every error is raised with a fixed number (WklGatewayError) and a German description, which ends with the service's message when there is one.

• The driver and the class keep separate second-factor sessions unless the application hands the session over with SESSION=.
• Each response is read in full within the time limit. For large amounts of data, use linked tables (through the ODBC driver), not this class.