How to: Use the Access add-in

See what is not in step, export, import, commit and open a terminal without leaving Access.

A developer works in Access, but the steps between Access and the repository run on the command line; the add-in puts them on a ribbon tab of the database the developer has open.

wkl-office addin install registers the add-in for the current user. With the next start of Access, the ribbon shows the tab WKL SDK:

Large buttons are the three entry points (project, Build and reopen, Designers); the other commands are small and stacked, as in the compact ribbons of current Office.

The buttons work on the project of the open database: the nearest of the folder of the database and the three folders above it that holds exactly one .acproj gives the project; a folder with several .acproj is skipped, and the search goes on upwards. Without one, the project menu reads “No project” and the commands do nothing.

The button Properties in the group Project opens the dialog Project Properties: the one place for the build configurations and delivery profiles of the project, like the property pages of Visual Studio. The tree on the left lists them; the right side shows the properties of the entry selected.

• Build configurations (Debug, Release and those of the project file): whether an ACCDE is made, the mode of the application model and the connection profile. An empty value takes the one of the project. They are stored in the project file, which keeps everything else as it is (project file).
• Delivery profiles: the build configuration a profile is built in (the mode it delivers is shown beside it), whether it is meant for the Access runtime, its delivery folder, the file name of its starter and whether it delivers one ACCDB for 32-bit and 64-bit Office. Set up in Bootstrapper Manager opens the starter's project file of the profile; the starter writes that file at the first delivery of the profile. A profile is stored as delivery\<name>.profile.json (delivery-settings.json).
• Delivery: the version and the release packages all profiles share (delivery\delivery-settings.json).

The toolbar above the tree, with New, Copy, Rename and Delete (each named in its tooltip), works on the kind selected; in the tree, F2 renames and Del deletes. Renaming a profile renames its starter project; deleting one asks whether to delete the starter project too. A build configuration that a profile uses cannot be deleted. OK and Apply write the changes; while a profile's configuration sets no mode, the dialog says so and both stay disabled. A project without a delivery gets one with Set up delivery: delivery-settings.json with version 1.0.0 and the newest release packages below WKL_PACKAGES, and the profile Production, built in Release (which gets the mode Production when it has none). A delivery of format 1 is written as format 2 when it is changed here. The dialog opens only while no operation runs, at the configuration chosen in the ribbon.

The page of a build configuration also shows whether its output folder (build\<configuration>) is a trusted location of Access, where Access opens databases with their code enabled. Add as trusted location enters exactly that folder, without subfolders, in the Trust Center of your Windows user; Remove takes away an entry the SDK made. When another trusted location covers the folder (for example a parent folder with its subfolders), the page names it and offers nothing. The page Configurations offers Add all output folders as trusted locations. Both act at once, not with OK, because they change no file of the project. The SDK enters a folder only when you ask for it (here or with the check box in New Project from Database) and never changes trusted locations that others added. A network folder is entered only when Access allows trusted locations on the network.

The drop-down Configuration in the group Build chooses the build configuration of the project, as the configuration box of Visual Studio does. It lists Debug, Release and the configurations of the project file. The configuration chosen decides:

• what Build and reopen, Rebuild and reopen, and Validate build. After building, Build and reopen opens the database of that configuration: with Release chosen while the Debug database is open, it builds build\Release\<name>.accdb and opens it in place of the Debug database.
• which delivery profiles Deliver and Starter list: those built in that configuration. The entry All profiles of configuration delivers exactly these; wkl-office deliver without --profile still delivers every profile of the project. A delivery of format 1 names a mode instead of a configuration for each target, so there both menus list every target.

Status, Sync, the export and import of changes, Changes and Commit work on the database open in Access, whichever configuration is chosen. The choice is kept per Windows user and project in the list of recent projects: it is still chosen after Access restarts, and switching to the project opens the database of that configuration. Opening another database of the project yourself does not change it. When the project no longer has the configuration chosen (it was renamed or deleted in Project Properties), the configuration of the open database applies.

The project menu lists the projects used last (up to ten, newest first, the open one marked “(open)”) with their folder and configuration, and Open project… to choose an .acproj file (opened in the configuration chosen for it, or Debug for a project not used before). Choosing a project closes the open database and opens the project's database build\<configuration>\<name>.accdb, as File > Recent does in Access, but without its startup code: as with `open`, the start form and AutoExec do not run and the startup properties do not apply to this start; AutoExec and the startup properties are back as soon as the database is open. When you open the database the usual way in Access (a double-click on the .accdb, File › Open), its startup code runs as before. When the project has no database for the configuration yet, it is built first, in a hidden Access, like Build and reopen. The switch is refused while a form or report is open, because closing the database would close it without the question Access asks; close it and choose again. The list is kept per Windows user under HKEY_CURRENT_USER\Software\The White Knight Labs\Access Framework\Sdk\RecentProjects; a project whose file no longer exists is left out.

When the database open in Access belongs to no project, the project menu offers New project from this database… above Open project…. It opens the dialog New Project from Database:

• Application name is suggested from the file name of the database: umlauts are written out (ä as ae), other accents dropped, and every character a VBA identifier does not allow becomes an underscore. The name becomes the file name of the built database and the name of its VBA project.
• Project folder lies below the folder for new projects of the SDK settings, otherwise beside the database. It must be empty or not exist yet. A note below it says when the Debug output folder of the new project is not a trusted location of Access: Access would open the database built there with its code disabled. The check box Add the Debug folder as a trusted location is then checked, and Create enters the folder.
• Neutral language and Access bitness start with the settings for new projects.
• Include data lists the local tables that hold rows, all checked, as Access imports tables with definition and data. A checked table gets the table policy SeedData, so its rows are written to the sources and every build loads them into the database again; an unchecked table keeps only its structure. Uncheck tables whose rows are the working data of the users, such as orders or bookings.

Create writes the project folder as `new` does and exports the open database into it as Export all does; the files written are listed under Output. The database itself is not changed. The new project is the open one at once, so the commands of the tab work without reopening, until the database is closed. Build and reopen then builds the database from the sources in the build folder and opens it; from there on you work in the built database, as in every project. Cancel creates nothing.

The add-in also loads into the VBA editor of the same Access. When the editor opens (Alt+F11), it shows the work area in the window WKL SDK and adds the toolbar WKL SDK:

• The window has the same tabs as the work area in Access (Project, Output, Problems, Changes, Terminal). Dock it like the Immediate window; the editor keeps its position for the next start.
• The toolbar has the commands of the ribbon tab in the same groups. The project, Designers, Deliver and Starter are menus with the entries of the ribbon; Configuration is a menu whose caption is the name of the configuration chosen; in the menu, that configuration is shown pressed. When you switch to another project or configuration, or add a profile, the entries update within a second. Workspace shows and hides the window in the editor, also after you closed it.
• Both work on the project of the database open in Access and share one queue with the ribbon: an operation started in the editor shows in both work areas, and only one operation runs at a time, wherever it was started.
• A command from the toolbar or from the window of the editor asks its question over the editor. The terminal, the change overview and the reports open in the window of the editor, which comes back for them if you closed it. The editor has no status bar, so it shows even a result that needs nothing from you in the tab Output.
• Build and reopen in the editor builds as it does from the ribbon (see below). Once the database is open again, Access shows the editor again, with the code window and the line you last worked in.
• Saving in the editor (Ctrl+S, the Save button, or Access saving a module) exports what changed in Access into the sources, like Export changes; the result is in the tab Output. Objects changed in Access and in the sources wait for a decision in Changes, as with Export changes. To switch it off for a project, clear Export changes to the sources when saving in the VBA editor in the tab Project; the choice is kept per Windows user and project.
• Every save in the editor also checks the code (see Checking the code below); the findings are in the tab Problems, together with those of the export.
• The tab Changes marks a module edited in the editor and not saved yet as Changed in the VBA editor, not saved. A module edited after the last check is added with the step Save in the VBA editor (Ctrl+S); a double-click opens its code.
• Excel, Word and Outlook load the add-ins of the VBA editor as well. There the add-in shows neither the window nor the toolbar, because its commands need an Access database.

wkl-office addin install
wkl-office addin status

Then start Access and open the database built from the project (build\Debug\<name>.accdb).

The change overview shows the same table as wkl-office changes: each object or file with Access ↔ source, the version control state and the next step, and the branch against its remote. Its first line states when the check ran. The overview checks again when it opens, after each of its commands, after every operation of the add-in (for example the export on save), when Access becomes the active application again, and every two minutes while it is open. A check older than five minutes is marked as possibly out of date; a failed check reads NOT CURRENT and names what failed.

From the diff to the code. In the diff of a module, class module, form or report, Go to code (or a double-click on a line) opens the code window of the VBA editor at that line, also from the work area in Access. The line is located by its procedure, so it is found even when the code in the editor has changed since; a line outside procedures is found by its text.

Checking the code. Check code reads the VBA code of the open database from the VBA editor, saved or not, and checks it with the same rules that wkl-office check and the build apply to the sources; the check also runs by itself after every save in the editor. The tab Problems lists each finding with its object and line; a double-click opens the code window at that line.

An error keeps the project from compiling; without the check, Access shows it only as a dialog and without a line. “Variable not defined” looks only at names that are assigned a value, and not at form and report modules: a name that is only read may be a function or constant of a library, and in a form module a control or a property of the form. In an error handler, calls of procedures of the project, Debug.Print, Err, MsgBox and Set … = Nothing count as safe. The command also reports when the project is not compiled: any other compile error shows only with Debug › Compile in the VBA editor. Causes and fixes: VBA code does not compile.

Build and reopen. The database open in Access is the build result of its project, so a build replaces it. The button therefore checks first and refuses, naming the objects, while Access holds a change that is not in the sources (next step Export or Decide) or something is unsaved; nothing is closed then. Otherwise it asks, closes the database, builds in a hidden Access of its own (this Access stays usable) and opens the new build, or the previous database when the build failed. In Settings (page Behavior) you can switch the question off and have the code checked first: an error the check finds then stops the build before the database is closed and is listed in the tab Problems.

When export on save is on for the project (see In the VBA editor), the build first does what saving in the editor would do: it saves the code in the VBA editor and exports the objects changed in Access to the sources. It then refuses only when an object was changed on both sides or the export fails.

Where results appear. As Access reports its own, a result that needs nothing from you appears in the status bar of Access (for example “Sync: no changes”) for ten seconds and is kept in the tab Output, without opening a window. What needs you - a refusal, a failure, errors - opens the work area on the tab Problems (when there are errors) or Output.

The work area is a task pane of Access, docked at the bottom by default (like the output, problems and terminal panel of Visual Studio Code), with the tabs Project, Output, Problems, Changes and Terminal; it can be dragged to any side, and the place chosen is kept. Each tab carries its commands in a bar at its top, as symbols with a tool tip (screen readers read the name of each); when the work area is narrower than the bar, the commands that do not fit move into the overflow button (») at its right end. Ribbon and work area use the symbols of Visual Studio Code (Codicons, Third-party notices) and the colors of its panel: light or dark after the Office theme (File › Account › Office Theme), the system colors in high contrast. The theme is read when Access starts: after changing it, restart Access.

• Project has the commands Changes, Status, Sync, Export changes, Import changes, Export all, Build and reopen, Rebuild and reopen, Validate, Check code, Cancel, Take back and Terminal in its bar (the same commands as the ribbon, in the same groups), and below it the project file and the database open in Access, the configuration chosen and the operations (below); this part scrolls when the work area is low. When another database is opened, the tab follows within two seconds.
• Output collects the reports of the commands, each under a line with its time, also those made while the work area was hidden; it can be filtered (only lines containing a text), copied and saved. Only an Access that hands the add-in no task pane shows the reports in a window of their own (with Copy and Save; Esc closes it).
• Problems lists the errors and warnings of the last command that reported any, with severity, phase, object, line in the code, file and cause; the tab's title carries their number. Enter or a double click opens the place: a finding in VBA code in the code window at its line, a form, report or query in design view, a module in the code window, a source file in Visual Studio Code at its line when code is on the PATH, else with the program Windows associates with it; the same does Open in its bar, Clear empties the list. The next command replaces the list.
• Changes is the change overview described above, in the work area instead of its own window; the ribbon button Changes selects it while the work area is shown.
• Terminal starts a shell in the folder of the project when the tab is shown for the first time and puts the keyboard into it; the other tabs work without it. Its bar holds New shell (ends a running shell and starts another), Kill shell and the folder the shell works in. A shell keeps its folder when another database is opened. The ribbon button Terminal selects the tab while the work area is shown, else it opens a terminal window.

Operations. Status, Sync, the exports and imports, the builds and Validate are operations: they run one at a time, in the order requested, beside Access, which stays usable while one runs. The tab Project shows the running operation (its title, project, configuration and since when) and lists the waiting ones; Take back removes the selected waiting one before it starts. Cancel (tab Project or ribbon) stops the running one between two steps: a sync keeps what it finished, an export reports the cancel without deleting anything, a build keeps the previous database, and the hidden Access of the operation is ended, never the developer's.

Each operation keeps the project, configuration and database it was requested for. One that loads sources into the database open in Access (Import changes, Sync) and finds another database open when its turn comes is not run; the output says so. While another program works on the same project (wkl-office in a terminal, a dotnet build, a second Access), the operation waits and the tab Project names the holder; the command line refuses in the same situation with exit code 10 (The project is busy).

Whether the work area is shown, its width and height, where it is docked and the tab selected are kept per Windows user under HKEY_CURRENT_USER\Software\The White Knight Labs\Access Framework\Sdk\Workspace, outside every project; the work area comes back as it was left when Access starts next. The add-in reads these values once per Access session, when the work area is first shown; a value changed in the registry while Access runs takes effect at the next start of Access. Within a session the work area itself is the arrangement: drag or resize it, and that is what is kept.

The work area opens by itself only where it belongs: in a full Access with the ACCDB of an SDK project open (the project file sits beside it or up to three folders above) or with no database open, and only when you left it open. With any other database (a finished application such as Bootstrapper Manager, a foreign ACCDB, an ACCDE) it stays hidden until you open it yourself with Workspace; when Access returns to a project, it is back. Hiding it this way does not change the setting that is kept, and a work area you closed stays closed, also in the next project. With no database open, the Backstage view of Access covers every task pane; the work area shows again with the next database.

The designers are those of WKL Forms Object Library, the same that the former add-in WklExtensions.accda opened: the buttons are enabled when WKL Forms Object Library is registered for the current user in the bitness of Office; otherwise their screen tip says so. Each designer works on the database open in Access and is modal.

The terminal runs PowerShell 7 when it is installed, otherwise Windows PowerShell, in the folder of the project. It handles colors, cursor keys, the alternate screen of full-screen programs, copy (select with the mouse, Ctrl+C with a selection, or right click with a selection) and paste (Ctrl+V, Shift+Insert, or right click without a selection). Closing the window ends the shell and the programs it started; so does closing Access.

To remove the add-in:

wkl-office addin remove

Every command can be reached from the keyboard: the ribbon tab has the fixed key tips Alt, W K and a letter per command, and in the work area F6 reaches it from Access, Ctrl+Tab switches its tabs and Tab moves into the bar of a tab (Keyboard shortcuts).

• The add-in is registered for the current user only; no administrator is needed. A build never registers it.
• Installing or removing takes effect at the next start of Access.
• The add-in stays out of delivered applications, so their users never see a developer tool: its tab WKL SDK is shown and works only in a full Access with an ACCDB open, or with no database open (to open a recent project). It is hidden in Runtime mode (/runtime or the Access Runtime, which load COM add-ins as well) and with an ACCDE, ACCDR, MDE, MDB or ADP; opening an ACCDB in a full Access shows it again within two seconds. In an Access started by automation the add-in is inactive; its window in the VBA editor then shows no project.
• The terminal needs wkl-office.exe: from the environment variable WKL_OFFICE_CLI, the bin folder of the SDK, or the PATH. Access does not allow a pseudo console in its own process; the terminal therefore runs the shell through wkl-office terminal-host in a process of its own.
• Screen readers read the terminal's screen as the value of the window (MSAA); every other element of the work area has a name and a role (UI Automation).
• Moving Access to another screen keeps the work area usable; on a narrow screen (1080 pixels) all commands of a bar stay visible. Supported display scaling: 100 %.
• The commands refuse while Access shows a dialog or holds something unsaved, as on the command line.

• How to: Commit what was changed in Access
• Command-line reference: addin, changes, diff
• Install the SDK