Creating ACCDE from ACCDB via GitHub or Azure DevOps Workflow – Any Experience? (2 Viewers)

[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.)