Creating ACCDE from ACCDB via GitHub or Azure DevOps Workflow – Any Experience?

[Josef P.]

Does anyone have experience in automating the creation of an accde via GitHub/Azure devops workflows?

A workflow to create an accdb from the source files created with msaccess-vcs already works.

My plan:

1. install Office 64 bit
2. create accdb from the source
3. (optional) run tests (64 bit)
4. create accde (64 bit)
5. change bit version of Office .. only possible with: uninstall + install?
6. (optional) run tests (32 bit)
7. create accde (32 bit)

1+2: done
3/6: still open, but easy to implement, as I can start this via an Access add-in.
4/7: open, but is only one line of code.

5: What is the best way to change the bit version?
Do I have to start an extra workflow for each bit version to create the accde, or can I possibly change the bit version in a workflow?

Note: in my local network I use 2 virtual machines with 32 and 64 bit Office preinstalled. But they are not available via GitHub/Azure devops.

How would you implement this?

 

[Josef P.]

Note:
Result of my experiments: a self-hosted runner currently seems to be the best option.
1x runner with installed Office 64 bit
1x runner with installed Office 32 bit

This eliminates the long installation time and I feel more comfortable with the license because I can use my own license and not start an unlicensed Office.

This results in this workflow(s):

Workflow 1 - (on runner with 64 bit Office)

1. create accdb from the source
2. (optional) run tests
3. create accde for 64 bit

Workflow 2 - (on runner with 32 bit Office)

1. create accdb from the source
2. (optional) run tests
3. create accde for 32 bit

 

[isladogs]

What do you mean by a 'self-hosted runner'?

 

[Josef P.]

About self-hosted runners - GitHub Docs

You can host your own runners and customize the environment used to run jobs in your GitHub Actions workflows.

docs.github.com

This allows you to use your own server / VM as a runner for the GitHub workflow.

 

[Josef P.]

Then I'll continue talking to myself and announce a few interim results.

Build scripts are ready: https://github.com/AccessCodeLib/msaccess-vcs-build
Note: The Powershell scripts can also be executed locally without a YAML file for a workflow.

Example GitHub workflow:

ACLibDeclarationDictionaryAddIn/.github/workflows/build-for-release.yml at main · AccessCodeLib/ACLibDeclarationDictionaryAddIn

This Add-In lists all VBA declarations (variables, function names, constants, etc.) and ensures consistent letter case - AccessCodeLib/ACLibDeclarationDictionaryAddIn

github.com

=> result: https://github.com/AccessCodeLib/ACLibDeclarationDictionaryAddIn/releases/tag/v0.4.2
The ZIP file and the ACCDA file for this add-in have already been automatically created.

On the subject of 32/64 bit accde: I simply run 2 workflows with different runners in response to a release. I then have one zip file each for 32 and 64 bit accde in the release.


The next step is a script for setting the database properties (AllowBypassKey, ...) etc.

If anyone has any questions, I'll be happy to answer them ... otherwise I'll end this monologue

 

[sonic8]

@Josef P. , thank you for your work and the posts here.

I understand your scripts are customized for GitHub Workflows.
Did you also evaluate how this approach can be transferred to Azure Pipelines?

 

[Josef P.]

Yes, I plan to do that, as I use Azure devops for my customer projects. For Azure devops I also need the 2 runners for the 32- and 64-bit accde files.
Powershell can also be used in Azure pipelines. That's why I use this as the main control and only call the script via the GitHub action.
The Powershell scripts should be usable 1:1. These can also be executed directly without a workflow file.
The workflow (YAML) file probably needs to be adapted somewhat. (The concept is very similar.)

I started with GitHub because I wanted to make it open source there.

When setting the database properties etc., I am still undecided as to whether I should use an Access add-in or implement it directly with Powershell.
My current plan is to store the properties to be set in an xml and json file in the repostitory and then set them in the created accdb/accde.
I will also add a feature to call a procedure from the created application or another access add-in to set special configurations.
The add-in variant would have the advantage that I could also use a form to configure the settings.
Note: I currently use a procedure in each of my applications that I call via a VB script.

 

[sonic8]

An Access add-in requires more configuration/installation and is prone to problems when run unattended.

I would rather use a script to directly set all required properties.
You could still use an add-in to create the XML configuration file, but the build process itself should run without an additional add-in.

 

[AHeyne]

@Josef P. : I have had this topic on my radar for a long time and am very interested in it. However, I haven't yet found the time to familiarize myself with it.
But I am very grateful for your monologue

 

[Josef P.]

Here's a little puzzle I didn't solve immediately.

Task: Execute SysCmd 603 (acSysCmdCompile) in Powershell.

Code:

param(
    [string]$SourceFile
)

$accdbPath = $SourceFile

#append current path if SourceFile is not full path:
if (-not ([System.IO.Path]::IsPathRooted($accdbPath))) {
    $accdbPath = Join-Path -Path (Get-Location) -ChildPath $accdbPath.TrimStart('\','/','.')
}

# accde file = accdb file with accde extension:
$accdePath = [System.IO.Path]::ChangeExtension($accdbPath, "accde")

# create Access.Application instance
$access = New-Object -ComObject Access.Application

# call SysCmd(603, accdbPath, accdePath) .. use invoke (late binding)
$accessType = $access.GetType()
$result = $accessType.InvokeMember(
    "SysCmd",
    "InvokeMethod",
    $null,
    $access,
    @(603, $accdbPath, $accdePath)
)

Write-Host "Result: $result"

Call:
.\compile-accdb.ps1 "Test.accdb"

Result (error):

[Exception calling "InvokeMember" with "5" argument(s): "You made an illegal function call."
At C:\...\compile-accdb.ps1:....
+ $result = $accessType.InvokeMember(
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : COMException

Spoiler: Solution

Fix type of accdbPath to string.

[string]$accdbPath = $SourceFile

 

[sonic8]

Fix type of accdbPath to string.

I had the very same problem in the past and wrote on it here: Compiling an Access project with VB-Script

 

[Josef P.]

Concept for setting properties:
Powershell script: https://github.com/AccessCodeLib/msaccess-vcs-build/blob/main/scripts/Prepare-Application.ps1
Config file (json): https://github.com/AccessCodeLib/ms...main/examples/Prepare-Application-Config.json

Note: json is very easy to use in powershell.
$config = Get-Content -Path $ConfigFile | ConvertFrom-Json

I like Powershell more and more.
Especially in combination with Visual Studio Code and Copilot.

 

[Josef P.]

Did you also evaluate how this approach can be transferred to Azure Pipelines?

Done.

Skripts repository (for GitHub and Azure devops):

GitHub - AccessCodeLib/msaccess-vcs-build: CI/CD - Build accdb/accde from source (msaccess-vcs exports)

CI/CD - Build accdb/accde from source (msaccess-vcs exports) - AccessCodeLib/msaccess-vcs-build

github.com

Examples of usage:
Github workflow: https://github.com/AccessCodeLib/Bu...n/.github/workflows/Build-self-hosted-O64.yml
Azure devops pipeline: https://github.com/AccessCodeLib/BuildAccdeExample/blob/main/.azure-devops/azure-pipelines.yml

Requirements for (self-hosted) runners:

• Trusted location for work directories of GitHub Runner or Azure Agent (required to compile accdb)
• Github runner: Powershell 7
• don't start as service - UI is required to import the CommandBars. (I don't know why yet.)

/edit: Azure devops pipeline run 2 jobs - build 32 + 64 bit accde

 

[Josef P.]

If anyone wants to test the Powershell scripts: attached is a zip file with the scripts and the sources of a test application.

The call of the build script looks like this, for example:

.\Build.ps1 -SourceDir "source" -Compile $true -AppConfigFile ".\Application-Config.json"

 

[Josef P.]

"Access DevOps/CI" is running

Example: Feature request or fix bug =>
(Work process is reduced to the essentials)

Earlier practice:

1. create branch
2. if possible: write tests for it
3. implement feature/bugfix - TDD (run specific tests locally)
4. export (msaccess-vcs) +
5. run all unit tests (call by msaccess-vcs: runafterexport) .. but only with the bit version that I use for development.
6. check in
7. create accde for 32 and 64 bit (on 2 VM!)
8. set application settings in each accde (scripted inside accde)
9. start and check accde files
10. merge feature/bugfix into the main
11. build accdb from source
12. run unit tests to test merge result
13. (optional) create a release from the main if required (create accde again manually and append it to the release tag)

My target routine

1. create branch
2. if possible: write tests for it
3. implement feature/bugfix - TDD (run specific tests locally)
4. export (msaccess-vcs)
5. check in => workflow starts automatically (no time spent by me) <--- new with GitHub workflow or Azure pipeline
   • Run all tests again in each bit version
   • Generates accde
   • Sets the application settings
6. check result of workflow (start and check accde files, read test log)
7. merge feature/bugfix branch into the main => repeat step 6 by workflow/pipeline
8. check result of workflow to check merge result
9. (optional) create a release from the main if required (workflow run again)

Example output: https://github.com/AccessCodeLib/BuildAccdeExample/actions/runs/15506821696

Detail output from Build.ps1 if tests failed:

Set trusted location: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample
-----
Install msaccess-vcs
Download url: https://api.github.com/repos/josef-poetzl/msaccess-vcs-addin/releases/latest
zip file downloaded from https://github.com/josef-poetzl/msaccess-vcs-addin/releases/download/v4.1.2-jp.3/Version_Control_v4.1.2-jp.3.zip to msaccess-vcs.zip
msaccess-vcs installed: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\MSAccessVCS\Version Control.accda
-----
Build accdb - TargetDir: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32
Add-in path: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\MSAccessVCS\Version Control
Current path: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample
Source: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\source
TargetDir: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32
Start msaccess-vcs build .. completed
Close Access ... completed
Built: Test.accdb (D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\Test.accdb)
Copy accdb to D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32
Build file: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32\Test.accdb
-----
compile accdb
accdb: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32\Test.accdb
accde: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32\Test.accde
accde successfully created.
-----
Run procedures from config file: deployment/Application-Config.json
Config D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32\Test.accde
Running procedure 'TestProcedure'
Running procedure 'TestProcedure2' with parameters: text, 123
Setting property 'AppTitle' of type 'Text' to 'Test App for Build Workflow'
Property 'AppTitle' does not exist. Creating it.
Setting property 'AllowBypassKey' of type 'Boolean' to 'False'
Property 'AllowBypassKey' does not exist. Creating it.
Setting property 'AllowSpecialKeys' of type 'Boolean' to 'False'
Setting property 'StartUpShowDBWindow' of type 'Boolean' to 'False'
Setting property 'StartUpForm' of type 'Text' to 'frmStart'
Property 'StartUpForm' does not exist. Creating it.
Setting property 'CustomRibbonID' of type 'Text' to 'MainRibbon'
Property 'CustomRibbonID' does not exist. Creating it.
Setting property 'ShowDocumentTabs' of type 'Boolean' to 'False'
-----
Run AccUnit tests
Add-in path: C:\Users\DevOpsRunner\AppData\Roaming\Microsoft\AddIns\AccUnitLoader.accda
File to test: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\Test.accdb
Run Tests ... completed
Tests success: False
Test result:
----------------------------------------
Tests: 159
     Passed:  158
     Failed:  1
     Error:   0
     Ignored: 0
----------------------------------------
1 / 159 failed
----------------------------------------
Close Access ... completed
Test log file copied to: D:\GitHubRunner\W11-O32-BuildAccdeExample\_work\BuildAccdeExample\BuildAccdeExample\bin32\Test.accdb.AccUnit.log
Tests failed
   3.1.4    SqlToolsTests.TextToSqlText_DefaultValueIfNullArg_ReturnsSqlText.4    Failed    actual is less then expected (Expected: "'abc"defgXX'" but was: "'abc"defg'")
Error: Process completed with exit code 1.