32 or 64 bit? Why change from 32 to 64bit ?

[amorosik]

I'm reading some posts related to the conversion of the code (the definition of the used api) from 32bit to 64bit
Now let's assume that we are using exclusively 64bit operating systems
And suppose you have both Access 32bit and 64bit version
What exactly are the pros and cons between two similar Access procedures (I mean code+runtime as installed on a production workstation), the first working with Access 64bit, the second with Access 32bit?

 

[CJ_London]

There is no real performance benefit for 64bit access over 32bit Access. 64bit OS is pretty standard these days and has no impact on which bitness of Access is used (except you can't run 64bit access on a 32bit OS).

Reasons for using 64bit Access - because users needed the improved performance/capacity for 64bit Excel, or just chose the default for office.

Assuming your runtime app is .accde then the version of access you use has to be the same bitness.

 

[Minty]

The only advantage I know of is (from Microsoft);

You’re working with the Large Number data type in Access, and while this data type is supported by 32-bit Access, you may see unexpected results when executing code or expressions that use native 32-bit VBA libraries. 64-bit VBA provides the LongLong data type which fully supports large numbers.

I think I have read or dreamt somewhere that they were planning on making the 64-bit version capable of storing larger tables, but I can't find any actual proof.

Obviously, 64-bit Office does have some significant advantages in the case of Excel, and is now (I think) the default install if you don't specify from Microsoft.

 

[amorosik]

There is no real performance benefit for 64bit access over 32bit Access. 64bit OS is pretty standard these days and has no impact on which bitness of Access is used (except you can't run 64bit access on a 32bit OS).

Reasons for using 64bit Access - because users needed the improved performance/capacity for 64bit Excel, or just chose the default for office.

Assuming your runtime app is .accde then the version of access you use has to be the same bitness.

1-pro because users needed the improved performance/capacity for 64bit Excel

 

[amorosik]

The only advantage I know of is (from Microsoft);

You’re working with the Large Number data type in Access, and while this data type is supported by 32-bit Access, you may see unexpected results when executing code or expressions that use native 32-bit VBA libraries. 64-bit VBA provides the LongLong data type which fully supports large numbers.

I think I have read or dreamt somewhere that they were planning on making the 64-bit version capable of storing larger tables, but I can't find any actual proof.

Obviously, 64-bit Office does have some significant advantages in the case of Excel, and is now (I think) the default install if you don't specify from Microsoft.

1-pro because users needed the improved performance/capacity for 64bit Excel
2-pro 64-bit VBA provides the LongLong data type which fully supports large number

 

[sonic8]

What exactly are the pros and cons between two similar Access procedures (I mean code+runtime as installed on a production workstation), the first working with Access 64bit, the second with Access 32bit?

Access 32bit may run out of memory pretty quickly if you open multiple complex forms (i.e. forms with many sub forms).
This will usually not happen with the 64bit edition of Access.
If you cannot use 64bit, you can mitigate out-of-memory issues by applying the LAA-Patch to the 32bit edition.

Other than @CJ_London stated above, I claim there is a performance benefit with the 64bit edition of Access, particularly with complex forms. It is not huge, but it is noticeable without applying technical performance measurements.

Big con to 64bit is that many ActiveX controls and libraries are not available for 64bit.

 

[amorosik]

Access 32bit may run out of memory pretty quickly if you open multiple complex forms (i.e. forms with many sub forms).
This will usually not happen with the 64bit edition of Access.
If you cannot use 64bit, you can mitigate out-of-memory issues by applying the LAA-Patch to the 32bit edition.

Other than @CJ_London stated above, I claim there is a performance benefit with the 64bit edition of Access, particularly with complex forms. It is not huge, but it is noticeable without applying technical performance measurements.

Big con to 64bit is that many ActiveX controls and libraries are not available for 64bit.

1-pro because users needed the improved performance/capacity for 64bit Excel
2-pro 64-bit VBA provides the LongLong data type which fully supports large number
3-pro Access 32bit may run out of memory pretty quickly if you open multiple complex forms

1-con Big con to 64bit is that many ActiveX controls and libraries are not available for 64bit

 

[CJ_London]

Not sure I agree with 3, never happened to me with complex forms - but I guess may depend on how many 'multiple' is. Pretty sure that is a memory constraint rather than a bitness constraint. You may hit the maximum number of open tables (2048) but again, not a factor with bitness.

Also not sure about the 'many' in many ActiveX controls and libraries are not available for 64bit claim. It was true some years ago, but less so today. I'd accept 'some' but suspect most of those that aren't available have either been superseded with built in alternatives or very rarely required.

I would add another 'pro' - not tested but pretty sure 64bit access will link to a 32bit excel file but 32bit Access won't link to a 64bit excel file if there are over 1m rows or perhaps those after 1m will be ignored.

 

[sonic8]

Not sure I agree with 3, never happened to me with complex forms - but I guess may depend on how many 'multiple' is. Pretty sure that is a memory constraint rather than a bitness constraint. You may hit the maximum number of open tables (2048) but again, not a factor with bitness.

'multiple' depends on the complexity of the form and its sub forms. It may start with 5 or 6 for a form with 10+ sub forms.
The bitness directly defines the maximum of addressable memory. Therefore every memory issue is also a bitness issue as long as the memory constraint is not the physical memory.

Also not sure about the 'many' in many ActiveX controls and libraries are not available for 64bit claim. It was true some years ago, but less so today.

How many ActiveX Controls were created with VB6? Hundreds of thousands? Millions? None of these are available for 64bit. I think, that qualifies as "many", without even thinking about controls created with other tools.

 

[amorosik]

1-pro because users needed the improved performance/capacity for 64bit Excel
2-pro 64-bit VBA provides the LongLong data type which fully supports large number
3-pro Access 32bit may run out of memory pretty quickly if you open multiple complex forms
4-pro 64bit access will link to a 32bit excel file but 32bit Access won't link to a 64bit excel file if there are over 1m rows

1-con Big con to 64bit is that many ActiveX controls and libraries are not available for 64bit

 

[The_Doc_Man]

In general, Access has been the "red-headed stepchild" of Office. When Word and Excel were updated to have 64-bit address versions (specifically, that used 64-bit addresses internally), Access apparently was not, probably because every previous database would have tables built using 32-bit address pointers. I suspect (emphasize SUSPECT) that the number of address references within Access that would need to be updated became either (a) daunting or (b) potentially too expensive a task. Specifically, interconversion would become a problem, since a 32-bit database could probably be up-converted to 64-bit with a process not unlike a Compact & Repair - but going back down from 64 to 32 would be impossible once you started actually using the larger address space. Heck, right now if you have a native Access DB converted from Ac2003 to 2016, all you need is a couple of advanced controls or features (e.g. multi-value fields) and you can't Save As (go back to) the old format. With a spreadsheet created for 64-bit Excel but that didn't really NEED to be handled in 64-bit (i.e. small sheet), a conversion process would be relatively easy. All cross-row or cross-column references are by NAME, not by address.

We know from various reports in this and other forums, and from Microsoft's own advertising, that 64-bit Excel can handle million-row spreadsheets. Supposedly, Word has gained the ability to work on very large documents. I have heard less about PowerPoint and Outlook, though I would guess that a 64-bit PowerPoint would handle more slides. I've got NO IDEA what larger addresses would mean for Outlook.

There is a disadvantage here that, for many product libraries that work just fine with 32-bit Access, some of those libraries have not been converted and probably will not work very well with 64-bit environments without some extra work. Many libraries for non-Office products, for example, were originally designed for 32-bit environments, and it is the call sequences that have to be managed to accommodate larger addresses.

I will offer an OPINION (clearly stated as such) that converting JET to 64-bit might be possible, but it would then be a cheaper form of competition with SQL Server. MS doesn't want to compete with itself.

 

[isladogs]

Agree with @sonic8 regarding performance improvements with memory intensive tasks in 64-bit Access.
Using Large Address Awareness in 32-bit has a similar effect.

Attached is a PDF of some tests I did a couple of years which show the benefits of using LAA.
These measure the time to load various JSON files into memory on 2 different workstations running 32-bit Access.

For small files, the benefit is negligible.
However, for large files, the load time is faster and you are far less likely to get out of memory errors

 

[amorosik]

1-pro because users needed the improved performance/capacity for 64bit Excel
2-pro 64-bit VBA provides the LongLong data type which fully supports large number
3-pro Access 32bit may run out of memory pretty quickly if you open multiple complex forms
4-pro 64bit access will link to a 32bit excel file but 32bit Access won't link to a 64bit excel file if there are over 1m rows

1-con Big con to 64bit is that many ActiveX controls and libraries are not available for 64bit
2-con for many product libraries that work just fine with 32-bit Access, some of those libraries have not been converted and probably will not work very well with 64-bit environments without some extra work

 

[JohnPapa]

Although this is a fairly old thread maybe someone will respond.
The biggest problem with 32bit Access that I am facing is that my .accde FE and .accdb BE will not install on a pc which has Office 64bit.
Can someone confirm that this is the case?

If the above is correct, then in a new project which I am starting right now, I need to have both a 32bit and 64bit version and install the one or the other depending on whether Office 32bit or 64bit is installed. My application does not need Access to run, since it uses the Runtime.

Edit: I have confirmed in practice and in theory that I cannot have 32bit coexist with 64bit.

 

[isladogs]

Although it is frequently stated that you cannot have both 32-bit and 64-bit Office on the same workstation, that isn't completely true.
In certain situations, both bitnesses can co-exist. See the mixed bitness section of my article
Installing Multiple Office Versions (isladogs.co.uk)
Having said that, having mixed bitness is not something I recommend anyone should do in practice.

As you already know, ACCDE files will only work on the same bitness in which they are created. So you will need to create both versions of the ACCDE unless all your clients use the same bitness

 

[JohnPapa]

Do I need to create 32bit and 64bit for my BE. I think not, since it does not run.

So I would need to have both Access 32bit and 64bit and create a 32bit .accde and a 64bit .accde? I would then distribute both and depending on what bitness Office is installed, for the procedure to install the correct .accde.

I have verified that I cannot install a 32bit .accde on a pc with 64bit Office. I always work from the Runtime and if the pc does not have Office installed and I install my 32bit .accde with the Runtime, is it possible subsequently for the user to install Office 64bit?

 

[isladogs]

Do I need to create 32bit and 64bit for my BE. I think not, since it does not run.

Not true. The BE is run from the FE but if it only contains tables and no code there is absolutely no benefit from making it an ACCDE.

I have verified that I cannot install a 32bit .accde on a pc with 64bit Office. I always work from the Runtime and if the pc does not have Office installed and I install my 32bit .accde with the Runtime, is it possible subsequently for the user to install Office 64bit?

Well, of course the user can subsequently install 64-bit Office but they won't be able to open your 32-bit ACCDE which is probably what you meant.

 

[sonic8]

The BE is run from the FE but if it only contains tables and no code there is absolutely no benefit from making it an ACCDE.

Just out of curiosity: Would it maybe even work if we had just one tables-only ACCDE that is used by 32- as well as 64bit frontends as a backend file?
(Yes, I know it makes little sense to do so!)

 

[isladogs]

Just out of curiosity: Would it maybe even work if we had just one tables-only ACCDE that is used by 32- as well as 64bit frontends as a backend file?
(Yes, I know it makes little sense to do so!)

OK I bit....

Surprisingly (to me) the answer is YES.
Even if its tables-only & no code, you cannot directly open a 64-bit ACCDE BE file in 32-bit Access. (or vice versa)
However, using a two-step approach, you can successfully link an ACCDB or ACCDE FE to an ACCDE file created in the 'wrong bitness' EVEN if it contains code

Whether or not the BE does contain code, I got the standard bitness error when I tried creating new links.
However, I got the link to work by first linking to a BE of the correct bitness, then altering the link to the wrong bitness using the LTM

Not sure whether that has any practical use other than possibly as a very weak/obscure security measure

 

[Josef P.]

[accde backend]
Just tested with a 32 bit accde backend: (But to use an accde file as a backend is quite strange. )
Linking the tables into a 64 bit frontend via the built-in dialog (New Data Source -> From Database -> Access) does not work.
Also with the "Linked Table Manager" you cannot select the tables.

With VBA code it is no problem.