Solved Managing updates for different bitness (1 Viewer)

[KitaYama]

We used to have several accdb FEs with a sql server BE.
I don't have the exact count, but something between 50 to 70 PCs, less or more.
When we had a change in the database, we opened a version up form and inserted the changes as new records.
After update event of the form issued a new version No based on a certain rule and copied the new FE to a shared folder.
Each PC had a small accdb used as a starter. On launch, the starter copied the FE from the shared folder (if there was a new version) and launched the new version.
The launched FE, checked the starter version, closed it, and if necessary, copied the new version of the starter from the shared folder.

The only manual step for a new version for us was adding a new record to the versions table (via its form)


Since the FE has changed to accde, there are more steps that should be done manually.
The nightmare is saving the database as accde in two bitness.
And since a PC can not have two different bitness of Office, we have to use memory sticks, search a PC with 32 bit office that's available and not used and save the FE as 32 bit accde, bring it back and copy it to shared folder.

Now my questions:
How do you issue new versions of accde files?
Is there any way to automate saving accde in two different bitness?
Is there any way to save a accdb as accde in vba?

Any kind of advice or any kind of info that you think may be a hint is much appreciated.

 

[sonic8]

Is there any way to automate saving accde in two different bitness?

You need to have separate Access installations on separate computers, one for each bitness. - You know that already.
If you use two VMs instead of separate physical computers, you can create an automatic build process for 32bit and 64bit on the same physical computer.

Is there any way to save a accdb as accde in vba?

See this text: Compiling an AccDE with VB-Script - The same approach works with VBA, of course.

 

[isladogs]

1. I distribute both in one .exe file using installer software. The installer script determines which bitness to install and ignores the other.
2. No as each ACCDE bitness needs to be created on a machine running that bitness.
3. See Create ACCDB/ACCDE Files In Code (isladogs.co.uk)

Edit : Too slow @sonic8 answered whilst i was typing

 

[KitaYama]

@sonic8 Thanks for the idea of virtual machine. I think we have a PC with a VM VirtualBox installed.
I'm also checking your link.
Thanks for the help.

@isladogs To the best of my knowledge, I think installing files using exe files needs to follow a wizard that at this point I'm trying to stay away of it.
Mostly because :
1- Most of our PCs are clients of a Windows domain that have not the right to install apps.
2- Using starter doesn't need to click next button several times. It does all the job in one click.
3- We have a lot of PC illiterate workers in shop floor who can't even follow a wizard. We also have short time foreigner staff that can't read our language.
Thanks for the link. I was sure you have something on this subject too. But couldn't find it.

Million thanks.

 

[narsi]

We used to have several accdb FEs with a sql server BE.
I don't have the exact count, but something between 50 to 70 PCs, less or more.
When we had a change in the database, we opened a version up form and inserted the changes as new records.
After update event of the form issued a new version No based on a certain rule and copied the new FE to a shared folder.
Each PC had a small accdb used as a starter. On launch, the starter copied the FE from the shared folder (if there was a new version) and launched the new version.
The launched FE, checked the starter version, closed it, and if necessary, copied the new version of the starter from the shared folder.

The only manual step for a new version for us was adding a new record to the versions table (via its form)


Since the FE has changed to accde, there are more steps that should be done manually.
The nightmare is saving the database as accde in two bitness.
And since a PC can not have two different bitness of Office, we have to use memory sticks, search a PC with 32 bit office that's available and not used and save the FE as 32 bit accde, bring it back and copy it to shared folder.

How do you issue new versions of accde files?
Is there any way to automate saving accde in two different bitness?
Is there any way to save a accdb as accde in vba?

Any kind of advice or any kind of info that you think may be a hint is much appreciated.

 

[KitaYama]

Just the heads up.

I ended up adding a small database with only one form and a button.
Clicking the button copies the FE to a temp folder, changes the startup properties to false, hides the linked tables, saves it as accde, copies the result to a shared folder accessible to other PCs and deletes the temp database.
Now I'm back again to one click (though it's from another DB).

I wasn't able to follow @sonic8's suggestion for virtual machine. Recent PCs haven't OS DVD, so I couldn't use VirtualBox, and even if I could, as far as I remember, the last time I used VirtualBox, I had to validate the license of the installed OS, after one month of trial version. At this point, we don't have extra OS license for this purpose.
I also have never used Microsoft VM machine.
For now, I use remote desktop to access a PC and run the above db to create 32 bit version of the FE.

@isladogs I really appreciate your help too. Just as a notification, there are a lot of missing parts in second MakeACCDE sub in part 3 of your article. The sub doesn't compile, there are missing definitions of variables and app.SysCmd 504, 16483 fails, no matter what.
I hadn't time to check and correct it. Maybe later I'll try again. Just in case you want to have a look.

Again many thanks for all given advices and solutions.

 

[Cotswold]

I don't really know about this, so I'm just asking the question.
Is it not possible to have 64bit Office and a 32bit Access Runtime on the same PC?

However, on one PC I have a copy of Access2021 64bit and also a copy of Access2010 and they both work just fine.

 

[isladogs]

@KitaYama
The code in that article was extensively checked before publication.
Nevertheless, I've just re-checked the code in A365 v2306 64-bit using the example database supplied and with a couple of modules already exported as text files.
It compiles and works perfectly for me. I can't replicate your comments

Make sure you take into account the four conditions marked in bold at the top of that section

However, I do need to update the article with some additional comments about class modules.
If you can send me a PM with precise details about the problems you had, I will investigate at the same time.

 

[isladogs]

@Cotswold
This is discussed in my article

Installing Multiple Office Versions

This article explains how you can successfully install more than one version of Office on the same workstation including Access runtime and mixed bitness installations

isladogs.co.uk

 

[KitaYama]

@KitaYama
The code in that article was extensively checked before publication.
Nevertheless, I've just re-checked the code in A365 v2306 64-bit using the example database supplied and with a couple of modules already exported as text files.
It compiles and works perfectly for me. I can't replicate your comments

Make sure you take into account the four conditions marked in bold at the top of that section

View attachment 108935
However, I do need to update the article with some additional comments about class modules.
If you can send me a PM with precise details about the problems you had, I will investigate at the same time.

@isladogs I'm terribly sorry. I didn't checked the database.
Only copied the code in the article.
the following image is what I have.
I'll try the database later.
Thanks again.

I assume after adding a dim for objAccess, the app variable in circle should be changed to objAccess. Because it's not set to anything earlier.

 

[isladogs]

Ah yes. That part of the article is incorrect. Sorry.
The 3 instances of objAccess in that code sinppet should indeed be replaced with app (or vice versa)
I've just amended the article. Thanks for letting me know
The example database is correct.

 

[Cotswold]

@Cotswold
This is discussed in my article

Installing Multiple Office Versions

This article explains how you can successfully install more than one version of Office on the same workstation including Access runtime and mixed bitness installations

isladogs.co.uk

That's what I thought. When I was working I'd always install my Runtime regardless of what was already on the machine. I have to say that I wasn't aware of your advice : "To do this successfully, you should ALWAYS install the earlier 32-bit Office version first "

I'd just whack my Runtime on without a care, so I must have just been lucky! Mind you I didn't know about Access-Programmers until 5 years after I retired, so quite a bit I missed out on.

 

[isladogs]

That's what I thought. When I was working I'd always install my Runtime regardless of what was already on the machine. I have to say that I wasn't aware of your advice : "To do this successfully, you should ALWAYS install the earlier 32-bit Office version first "

I'd just whack my Runtime on without a care, so I must have just been lucky! Mind you I didn't know about Access-Programmers until 5 years after I retired, so quite a bit I missed out on.

I think you only skimmed my article!
The advice on which version to install first depends on whether you are trying to install the same bitness each time or do mixed bitness installations

 

[sonic8]

I wasn't able to follow @sonic8's suggestion for virtual machine. Recent PCs haven't OS DVD, so I couldn't use VirtualBox, and even if I could, as far as I remember, the last time I used VirtualBox, I had to validate the license of the installed OS, after one month of trial version. At this point, we don't have extra OS license for this purpose.
I also have never used Microsoft VM machine.

You are right. Each Windows installation needs a valid license. It doesn't matter whether it is on a virtual or on a physical computer.
You should get a Windows Pro license for ~150 USD. - That should not be a big deal for a business.

Using Microsoft HyperV for virtualization is fairly straigth-forward. If you can handle Virtual Box, you'll also get along with HyperV.

 

[Josef P.]

Tip: so that you do not have to enter the virtual PC separately, you can also start the execution via PowerShell. (For this winrm must run.)

Example:

#begin create accde in virtual machine

# files (accdb/accde/vb script)
$strDBName = "\\pathToAccdb\FileName.accdb"
$strACCDEName = $strDBName -replace ".accdb$", ".${OfficeBitCode}.accde"
$createAccdeVbScript = "C:\Scripts\makeAccde.vbs"

# Virtual machine
$VMName = "VPC01" # Name or IP
$OfficeBitCode = "32"
$Username = "username"
$Password = "strict-secret!"

#create remote session
$SecurePassword = ConvertTo-SecureString $Password -AsPlainText -Force
$Credential = New-Object System.Management.Automation.PSCredential ($Username, $SecurePassword)
$Session = New-PSSession -ComputerName $VMName -Credential $Credential

Invoke-Command -Session $Session -ScriptBlock {

    if (Test-Path -Path $using:strACCDEName) {
        Remove-Item $using:strACCDEName
    }

    # Unfortunately does not run:
    # $AccessApp = New-Object -ComObject Access.Application
    # $AccessApp.SysCmd(603, ($using:strDBName), ($using:strACCDEName))
    # $AccessApp.Quit()
    # [System.Runtime.Interopservices.Marshal]::ReleaseComObject($AccessApp)
    # .. => Error:        
    #     The "Action" argument with the value "603" for "SysCmd" cannot be converted to the type "Microsoft.Office.Interop.Access.AcSysCmdAction":
    #     The value "603" cannot be converted to the type "Microsoft.Office.Interop.Access.AcSysCmdAction" due to invalid enumeration values.
    # =>
    # use VB script:
    $out = C:\Windows\System32\cscript.exe //nologo $using:createAccdeVbScript "$using:strDBName" "$using:strACCDEName"
    Write-Host "return value (0 = success): $out"

    if (Test-Path -Path $using:strACCDEName) {
        Write-Host "accde: success"
    } else {
        Write-Host "accde: failed"
    }

}
Remove-PSSession -Session $Session
#end

Unfortunately, calling SysCmd 603 directly, ... in PowerShell does not work for me, so I call SysCmd 603 via an additional VB script.

Does anyone know the solution for calling SysCmd 603, ... directly in PowerShell?
/edit:
As soon as you ask, you find a solution.
=> https://social.technet.microsoft.co...override-type-check?forum=winserverpowershell

 

[KitaYama]

As soon as you ask, you find a solution.

It's very nice to find the solution and it feels so good.
The problem is do I understand the solution or not.
I'll do some research on this. Maybe it's the time for some study on VB scripts.

At this point, because of VM's OS license problem explained above, I didn't install Virtual Box. I asked for a license from IT, But the reply was:
"Paying for Windows 11 license is the worst decision. Buy a PC. It comes with the OS, You don't need to set up a VM, and the PC may be useful for other jobs too."

I simply denied. I have a triple 27" monitor setup on my desk and there's no space to add a new PC.
I prefer to use the remote desktop with a PC with Office 32 bit.

Million thanks.

 

[theDBguy]

You need to have separate Access installations on separate computers, one for each bitness. - You know that already.
If you use two VMs instead of separate physical computers, you can create an automatic build process for 32bit and 64bit on the same physical compute

Hi. Welcome to AWF!

 

[KitaYama]

If you use two VMs instead of separate physical computers, you can create an automatic build process for 32bit and 64bit on the same physical compute

It was suggested by @sonic8 in #2 and was rejected for the reasons explained in #6.
VM needs a stand alone license for OS and IT prefers to purchase a new PC instead of having two license on one PC.

Any how thanks for the suggestion.

 

[isladogs]

For info, post #17 is an exact copy of the first part of the answer by @sonic8 in post #2.
That seems like a typical spammer’s approach to me

 

[theDBguy]

For info, post #17 is an exact copy of the first part of the answer by @sonic8 in post #2.
That seems like a typical spammer’s approach to me

I agree. Sorry I missed that. Let me clean it up.