How to: Build an Access application from its text sources

Keep an Access application with WKL controls as text in source control and build the ACCDB and ACCDE from it with one command, so that every developer and every build machine produces the same application.

New projects: the WKL Office SDK builds, exports and checks Access applications from sources with more steps than this build tool (status and sync with the open Access, roundtrip, MSBuild, an Access add-in), and takes access-app.json over with wkl-office migrate access-app.json -o <folder> (documentation of the WKL Office SDK, „How to: Migrate a project of the build tool“). This build tool keeps working for existing projects.

Requires WKL Forms Object Library 1.38 or later (the build tool ships with the release); the house theme Riviera in the package 1.44 or later; -Normalize and finish 1.45.1 or later.

• The repository is the source. Modules and class modules as the VBA editor exports them, forms, reports, queries and macros as SaveAsText writes them, tables as SQL, an Office theme and ribbons as files. The ACCDB and ACCDE are build artifacts and not kept.
• A project file access-app.json names the application, its references and its source folders (reference).
• One command builds: tools\build\Build-AccessApp.ps1 -Project access-app.json. It runs Access hidden, in the order Access needs (the theme before the forms), and stops with the step that failed.
• WKL controls in forms come with the form's text when the form was edited in Access and saved with SaveAsText. A form a script creates gets a rectangle with Tag wkl:<Kind> where the control goes; the build replaces it by the released control block of that kind.

A repository with this layout:

my-app/
├─ access-app.json
└─ src/
   ├─ tables/01-customer.sql
   ├─ modules/basApp.bas, clsCustomer.cls
   ├─ forms/frmShell.form.txt, frmCustomers.form.txt
   ├─ ribbons/AppRibbon.xml
   └─ theme/          (the parts of a .thmx package: [Content_Types].xml, theme/theme/theme1.xml ...)

The project file:

{
  "name": "Customers",
  "references": [ { "library": "WklForms", "major": 2, "minor": 0 } ],
  "tables": "src/tables",
  "modules": "src/modules",
  "theme": { "folder": "src/theme", "name": "Riviera" },
  "ribbons": "src/ribbons",
  "forms": "src/forms",
  "properties": { "AppTitle": "Customers", "StartUpForm": "frmShell", "CustomRibbonID": "AppRibbon" }
}

Build it (from any PowerShell; the script restarts itself in the bitness of Office):

& '<release>\tools\build\Build-AccessApp.ps1' -Project .\my-app\access-app.json

The output folder my-app\out then holds Customers.accdb and Customers.accde.

The house theme „Riviera“ ships with the release package under themes\riviera. To use it instead of a copy in your project, point the theme at the package (<package> stands for the folder of the release package):

"theme": { "folder": "<package>/themes/riviera", "name": "Riviera" }

Keep theme1.xml free of comments before its root element if you make a theme of your own: Access drops such a theme without a message.

To bring changes made in Access back into the repository, export the development database into the source folders of the same project file. Export-AccessApp.ps1 lies next to the build script (<project> stands for the folder of your project):

.\tools\build\Export-AccessApp.ps1 -Project <project>\access-app.json -Database <project>\dev\Customers.accdb
.\tools\build\Export-AccessApp.ps1 -Project <project>\access-app.json -Only frmCustomers,basApp

It writes modules as the VBA editor exports them, forms, reports, queries and macros as SaveAsText writes them (without the Checksum line, which changes with every save), and the ribbons from USysRibbons. Tables, the theme and the database properties are not written back: they are defined in the sources. Objects that exist only in a folder are listed, not deleted. Close the database in your own Access first; the script opens it in a hidden Access of its own. With -Normalize it leaves out of form and report texts the lines Access writes anew on every save without a change - the GUID, NameMap and RecSrcDt blocks and NoSaveCTIWhenDisabled - so a diff shows only real changes; the build creates those values again.

Steps the tool does not know - groups of the navigation pane, hidden objects - go into a function of your own that the build runs after the properties, named in the project file:

"finish": "FinishBuild"

Public Function FinishBuild() As String
    On Error GoTo Failed
    CurrentDb.Properties("StartUpShowDBWindow") = False   ' any step the tool does not know
    FinishBuild = "OK"
    Exit Function
Failed:
    FinishBuild = "Fehler " & Err.Number & ": " & Err.Description
End Function

Any text other than "" or "OK" stops the build with that text.

• Access is required to build. The build uses Access's own LoadFromText and SysCmd 603; the Access Runtime cannot build. The machine needs the WKL release registered in the bitness of Office, like a development PC (How to: Install the released package).
• The ACCDE step repeats itself. Access returns now and then from making the ACCDE without a file and without an error; the script tries up to three times and checks the file.
• No dialogs. Access runs hidden; a module that does not compile stops the build with the step and the hint to compile in the VBA editor. Only the Access instance the script started is ended.
• Trusted location only while building. The output folder is a trusted location for the current user during the build and removed afterwards. To open the built application from there, trust the folder yourself: <release>\tools\deploy\Set-AccessTrustedLocation.ps1 -Path <output folder> (per user, no administrator rights). Without it Access opens the database with active content disabled: no VBA, no start form code, and the WKL controls do not load.
• Exports change some lines unless normalized. Access writes new values into a form's text on every save even without a change of the form (GUIDs in binary blocks, NameMap, RecSrcDt, NoSaveCTIWhenDisabled); export with -Normalize to keep them out of the repository.
• Pictures keep the path of the inserting machine. The Picture property of an image control inserted from a file holds that file's absolute path in the first export, as information only (the embedded picture is in the form text); a build from the text keeps only the file name, so the next export shows that line once as changed.
• The start form runs when the built database is opened. StartUpForm takes effect when a database opens; a script that opens the built database afterwards for its own steps starts the application, and an open form cannot be hidden or changed in design (error 2564). Put such steps into the finish function, which runs before the database is opened again; or set StartUpForm in your own script last.
• The tool does not cover linked tables (use WklLinkManager.EnsureLinks at start), local table data beyond INSERT statements, and relationships.

• Reference: access-app.json
• How to: Put WKL controls into forms by code - the control blocks
• Build an administration application: tutorial of the WKL Administration Shell Template
• How to: Deploy an application with WKL controls to user PCs