Source layout and formats

<name>.acproj
source/
  queries/<name>.query.txt   modules/<name>.bas        classes/<name>.cls
  forms/<name>.form.txt      reports/<name>.report.txt macros/<name>.macro.txt
                             reports/<name>.print.json
  tables/<name>.table.json   data/<name>.jsonl         relations/relations.json
                             data/<name>.files/<key>/...
  references.json            application/properties.json
  themes/<name>/...          resources/images/<name>.png
  ribbons/<name>.xml         ribbons/ribbon.json       navigation/navpane.json
  application/app-model.json (+ its icon)
  texts/<lang>.json          texts/terms.<lang>.json
build/<configuration>/       (results, not sources)
obj/wkl-state.json           (change tracking, per workstation, not sources)

By default, every part of an Access application has one place below the source root, the same for export, build, checks and change tracking. Source items in the project file can put a kind of source somewhere else (Project file: Source items). Every source file is UTF-8. Object names with characters that file names cannot hold are encoded (%2F …).

tables/<table>.table.json is one object; any other key is refused:

{
  "version": 1,
  "name": "tblCustomer",
  "fields": [
    { "name": "Id", "type": "Long", "autoNumber": true },
    { "name": "Name", "type": "Text", "size": 80, "required": true, "allowZeroLength": true },
    { "name": "Credit", "type": "Decimal", "precision": 12, "scale": 2 },
    { "name": "Active", "type": "Boolean", "properties": { "DisplayControl": "106" } }
  ],
  "indexes": [
    { "name": "PrimaryKey", "fields": [ "Id" ], "primary": true },
    { "name": "ByName", "fields": [ "Name", { "name": "Id", "descending": true } ], "unique": true }
  ]
}

An index is name, fields (a field name for ascending order, or { "name": ..., "descending": true }), and the flags primary, unique (left out for a primary key, which is unique anyway) and ignoreNulls, each false by default.

Each entry of fields is an object with these keys; a key at its default is left out.

Lookup properties have no keys of their own; they go under properties with the name Access uses and the value as text. A Yes/No field that Access shows as a check box in table and query datasheets:

{ "name": "Assigned", "type": "Boolean", "properties": { "DisplayControl": "106" } }

DisplayControl takes the number of the Access control type (106 check box, 109 text box, 110 list box, 111 combo box). A key displayControl beside type is refused as unknown.

The migrations set allowZeroLength to true for every text field of the tables they carry over (DATEC SDK, build tool of WKL Forms Object Library), because the old builds made fields that accept empty texts.

One JSON object per record, keys in the order of the table schema, records ordered by the primary key. Every field of the schema must appear in every record; a missing field is an error. Each value carries its type without depending on the culture of the machine:

Non-ASCII characters are written as they are (UTF-8), not as escapes. Long binary data (OLE object) and attachments are separate files, one folder per record named after its primary key: data/<table>.files/<key>/<field>.bin and data/<table>.files/<key>/<field>/<file name>. An attachment file holds exactly the bytes of the attached file. A multi-value field is an array of its values, sorted, because Access keeps them as a set. A table without a primary key uses the position of the record (row000001), which moves when records are inserted before it. A data file cannot name a file outside the data folder.

references.json is an array in the priority order of the VBA project; each entry has exactly name, guid (in braces, upper case), major and minor (the version the application needs):

[ { "name": "WklForms", "guid": "{A37D549E-FCD0-48EC-8B14-C7323B747E4F}", "major": 2, "minor": 0 } ]

The build adds each reference with this version. Access records the highest version of the type library with the same major that is registered on the build machine: with WKL Forms Object Library 2.3 registered, "minor": 0 gives a reference to 2.3 in the ACCDB and the ACCDE. export keeps the minor of the sources as long as the database records the same major and a minor that is not lower; otherwise it writes the version of the database. So references.json keeps naming what the application needs, while the built database needs the version that was registered on the build machine.

application/properties.json is an array ordered by name; each entry has name, value (always a string) and, for any type but text, type with the name of the DAO property type (Boolean, Byte, Integer, Long, …):

[ { "name": "AppTitle", "value": "Customers" }, { "name": "AllowBypassKey", "value": "False", "type": "Boolean" } ]

relations/relations.json is an object with version (1) and relations, ordered by name; each relationship has name, table (the primary table), foreignTable, fields (pairs of field and foreignField) and attributes, the names of the DAO relation flags that are set (Unique, DontEnforce, Inherited, UpdateCascade, DeleteCascade, Left, Right); an empty list is an enforced relationship without cascade.

In all three files any other key is refused.

The texts an application shows through WklTexts of WKL Forms Object Library (1.67 or later) - messages, captions, questions - are one file per language in texts/. neutral.json holds them in the language the application is written in; at run time a key without a text in the language of the session shows the neutral text, then the English one. <code>.json holds a translation, for any language code of two or three letters (de.json, fr.json, pl.json). Either neutral.json or en.json is required as soon as there is any text file; the first that exists is the reference of the checks. Each file is one flat JSON object of key to text, keys sorted ordinally, UTF-8 without byte order mark:

{
  "orders.count": "{0} orders",
  "orders.title": "Orders"
}

• Keys are lower case and dotted by form or topic (orders.title) and are compared without regard to case; a key twice in one file stops the build. Keys starting with wkl. are the standard texts of the components and cannot be defined by the application.
• Placeholders {0}, {1:N2} are filled from the arguments of WklTexts.Text.
• The build writes the texts into the generated table USysWklTexts (ID, TextKey, Language, TextValue) of the ACCDB and the ACCDE; the export never writes the table back. A table USysWklTexts in tables/ beside text files stops the build.
• The neutral language (optional): the project property NeutralLanguage (project file) names the language of neutral.json, for example sl. That language then needs no file of its own, and keys missing in it are not reported.
• Checks (warnings, the build goes on): keys of the reference missing in another language, keys without a neutral or English text, translations with other placeholders than the reference, keys the VBA code asks for without a neutral or English text, translations marked for review, and text files of a language that lie outside texts/ (the build does not read them; only files named neutral or after an ISO 639-1 code that hold one object of texts count, and folders whose name starts with a dot are not searched).
• Keys the code asks for are found in modules, class modules, forms and reports in two forms: .Text("a.b") on any object (WklTexts or a helper of the application such as Tx.Text("a.b", n)), and Text("a.b") or Texts("a.b") (the default member), in any case. Only a literal key with at least one dot counts; a key built at run time (texts.Text(prefix & ".title")) is not checked. Comments are skipped: an apostrophe outside a string, Rem, and comment lines continued with _.
• Terminology (optional): texts/terms.<lang>.json maps a preferred term to variants that should not be used, { "podatkovna zbirka": [ "zbirka podatkov" ] }; a text that holds a variant is a warning.
• Review marks: texts/review.<code>.json maps a key to the neutral (or English) text its translation was made from, when that text has changed since. texts import writes and removes the marks; each one is a warning until a translation of the current text comes back.
• For translators, wkl-office texts export --language <code> writes the translation package of one language, and texts import merges any number of returned packages at once (command line).

SaveAsText writes the printer of a report as binary blocks (PrtMip, PrtDevMode, PrtDevNames and their W forms). They hold name, driver and private driver data of the printer of the machine that saved the report and differ on every machine; Access reads a shortened block with garbage in its device and form name, so they cannot be cleaned. The export leaves them out of the report's text and writes the page settings next to it:

{
  "useDefaultPrinter": true,
  "orientation": 2,
  "paperSize": 11,
  "leftMargin": 1440,
  "topMargin": 1440,
  "rightMargin": 1440,
  "bottomMargin": 1440,
  "itemsAcross": 2,
  "columnSpacing": 360,
  "rowSpacing": 0,
  "defaultSize": true,
  "itemSizeWidth": 0,
  "itemSizeHeight": 0,
  "itemLayout": 1953,
  "dataOnly": false
}

The values are those of the Printer object of Access: margins, spacing and item size in twips (1440 per inch); orientation 1 portrait, 2 landscape; paperSize as AcPrintPaperSize (9 A4, 11 A5, 1 Letter); itemLayout 1953 across then down, 1954 down then across. The build sets them through the report's Printer object. The name of a specific printer is not kept, because it belongs to a machine. With "useDefaultPrinter": true the report prints on whatever printer is the default; with false the build stores it with a fixed printer, the default printer of the machine that builds it. A report without the file is built with the defaults of Access. check reports invalid JSON as an error and a .print.json without its report as a warning.

1. check (sources and machine); nothing is created when it finds an error.
2. A new database under its final name in build/<configuration>/.building/, moved to build/<configuration>/ only when every step succeeded.
3. References in the order of references.json, database properties with their types, tables with indexes, the data of SeedData and FullData tables, the application texts (USysWklTexts), relationships.
4. The theme (the database is opened again, because Access gives a theme only to forms created afterwards), shared images, ribbons written by hand, the ribbon model with its callback module.
5. Queries, standard modules, class modules, forms (placeholders replaced), reports (page settings applied), macros.
6. Context menu assignments, the navigation pane model, the startup properties, the application model, the links of the configuration's connection profile, the finishing function.
7. Compile and check: the VBA project compiles, no reference is broken, every table and object of the sources is in the database.
8. The ACCDE when the configuration makes one, with the startup properties of AppMode.

The models are applied by WKL Forms Object Library itself (its Save and Apply methods) through a helper module that lives in the database only during the build.

• Tables: the schema of every local table (not system, temporary or linked tables; linked tables are not warned about), and the data of tables with the policy SeedData or FullData; Ignore leaves the table out. Datasheet state of the user (column widths and order, sort, filter, fonts) is left out. A table or field property that is neither on the list of source properties nor known as user state is not exported: the export reports it as a warning (“The property is not a source yet and was not exported”), and a database built from the sources does not have it. The same holds for a database property of a DAO type that properties.json cannot hold. These warnings do not fail export, build or roundtrip; treat each as a loss to be reported.
• Passwords (PWD, Password) in the parameters of the Linked Tables Manager's connection rows are removed, each with a warning.
• References: a reference keeps the version references.json already asks for when the database binds the same major version and a version not older. Access binds a reference to the version registered on the machine; taking its number would raise the requirement with every update of the build machine.
• Database properties: engine values, user state of the navigation pane, defaults of every new database, values owned by the theme or set by a model are left out.
• Theme: the theme the database names (not “Office”, which every database has).
• Models: the model files are the one source; the tables that hold them (USysWklApp, USysWklNavPane, USysWklRibbon, USysRibbons) are not exported as tables, nor is USysWklTexts, which the build writes from the text files. USysWklLayout (the arrangement each user chose) and the generated module basWklRibbon are no sources either.

A file is rewritten only when its content changed. Files of the managed folders whose object is gone are deleted and listed; other files (a README beside the forms) stay. After an error nothing is deleted.

The first export after migrate rewrites forms and modules once as Access writes them: VBA spells every identifier the same way throughout the project, and Access adds form properties such as PublishOption. From then on, build followed by export writes no file.

Values that look like noise but are state of the object stay: LayoutCached* (Access does not write them again after loading) and PublishOption (Access writes the same value again).

OleData of an ActiveX control (the control's saved properties) is an OLE storage and is kept byte for byte except for its two time stamps, which the export sets to 0 (see the table). A form re-created from its text therefore exports to the same text. Access loads such text without errors, keeps the control's properties, and writes the zeros back unchanged on the next export.

Besides the values above, which the export removes on purpose, these parts of an Access application never reach the sources. A database built from the sources does not have them, and roundtrip cannot notice their absence.

• Do not open a built database with a start form through OpenCurrentDatabase from a script to look at it: the start form runs, even with AutomationSecurity set to disable macros. Read it with DBEngine.OpenDatabase.
• A multi-value text field has no size in the sources: DAO reports 4 (the width of its key), and its values hold 255 characters whatever size it was created with.

• Project folder – which file goes where and what is versioned
• Project file reference
• Command-line reference