Access 64bits database running on 32bits clients possible

[boerbende]

Dear ACCESS experts,

I am still developing in ACCESS 2010, 32 bits (sorry, but we are not a very rich company )
My "development server" (an old HP proliant, RAID 1, retired already 5 years ago as fileserver) is still servicing me, but is showing some red LED's which indicates me the disks might stop in the very near future. For spare parts I was directed to Ebay, so IT has installed for me a new development server: VMWare. Windows 2012, 64 bits, including OFFICE 2010 64 bits.

Developing on Windows Server and deploying on Windows 8 clients was never a problem, but the first database I opened in Office 2010 64 bits gave me already missing or broken reference to files.
I am convinced I can get all the Access databases running without errors under Office 2010 64 bits, but 99 of the 100 users has still installed the 32bit MS office 2010.
I had shown my doubts, but my IT asks me to "try". Instead of trying, I assume there might be some experience in this forum knowing if this will going to work or not

Many thanks

Ben

 

[Oreynolds]

I have been developing our DB for 20 years plus and never had a single issue with it in general terms until by chance our IT dept provided 2 new PC's both running 64BIT Office which then caused random DB crashes we had never seen. Not realising the cause of the issue at the time we spent a few weeks experimenting with resolutions, altering code, removing recent upgrades etc etc and after resorting to trial and error we identified all the issues were related to when the 64 bit machines were in use. We downgraded them to 32bit and normality was restored instantly!

I posted on here regarding the issues and the general consensus was stay away from 64 bit! Good luck!

 

[isladogs]

ACCDB files will work on both 32-bit and 64-bit provided that:

• API declarations are suitably modified using PtrSafe and LongPtr
• You aren't using certain reference libraries that aren't available for 64-bit e.g. Flexgrid and the built in Treeview
• Any ActiveX controls have been updated to run in 64-bit

However, if you have ACCDE files, these will only work in the bitness they were created. In other words, you will need two different versions of the ACCDE file(s)

 

[The_Doc_Man]

There are two viewpoints here. Colin (Isladogs) is absolutely right that you can have a mixed bag (with certain restrictions such as .ACCDE files). However, a one-time fix to remove Office 64-bit and replace it with Office 32-bit is also viable.

The question will be whether anyone needs a 64-bit Excel. Neither Word nor PowerPoint are likely to have issues with bitness because their files rarely get big enough to require 64-bit. Only Excel actually uses 64-bit gainfully based on size issues. If nobody else needs 64-bit Office, then from a cost/benefit standpoint you do better to remove Office 64-bit and install Office 32-bit. Explain to your IT guys that it is a labor-cost issue and that their imagined savings in labor is offset by you having multiple users to maintain, and YOUR cost of business went up because of them. And if you bring that up with YOUR boss, you might find some backing.

In the final analysis, though, it IS up to you and not me. I'm just telling you how I would approach it but my motives are not your motives.

 

[linxpatrick]

I have a couple applications that I've been preparing for 64 bit Office but still run on 32 bit Office.

The difference between coding a 32 and 64 bit Access application has to do with some of the data sizes and calling Windows APIs. When declaring Windows APIs you have to check to see if you are using VBA7 and WIN64, which are compiler variables. If so, API declarations have to include the PtrSafe clause. To make this work, create two sets of declarations like

#If VBA7 AND WIN64 Then
64 bit declares go here.
#else
32 bit declares go here.
#End If

64 bit declarations will look like Public Declare PtrSafe Function ().

The other part of this is knowing when to use Long, LongLong and LongPtr variables. Information is kind of sketchy on where to use them.

 

[arnelgp]

then develop your Apps in A2010 x32, which is very much compatible both in x64 and x32.

 

[zeroaccess]

We don't have control over the Office install at work, so it's 2019 x86-64 bit.

Declaration was modified with PtrSafe and a new .accde was generated and we were good to go. But as mentioned, the 32-bit clients need an .accde that was created in 32-bit Access.

 

[boerbende]

Dears,

Thank you all for your valuable input.
I will (for now) not take the risk to end up with different versions.

IT installed a VMWare and made a decision to install 64bits which forces work to another.
According to my opinion one should use a development version closest to the one in use by the users which IT knew was 32 bits.

Ben

 

[isladogs]

@linxpatrick
Just for info, your conditional compilation construct is more complex than it needs to be.
The Win64 part is unnecessary as VBA7 declarations work in both 32-bit & 64-bit.

VBA7 was of course introduced with Office 2010. So the following example will work both for users of Office versions prior to 2010 AND for all those running later versions whether 32-bit or 64-bit

'###############################################
#If VBA7 Then 'A2010 or later
    Declare PtrSafe Function ShowWindow Lib "user32" _
        (ByVal hWnd As LongPtr, ByVal nCmdShow As Long) As Long
        
    Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
        (ByVal hWnd As LongPtr, ByVal nIndex As Long) As Long
            
    Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
        (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

#Else  'A2007 or earlier
    Declare Function ShowWindow Lib "user32" _
        (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
        
    Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, ByVal nIndex As Long) As Long
            
    Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If
'###############################################

If all your users are running A2010 or later,, the conditional compilation itself is unnecessary. The above code can be reduced to the following and it will work for all users:

 Declare PtrSafe Function ShowWindow Lib "user32" _
        (ByVal hWnd As LongPtr, ByVal nCmdShow As Long) As Long
        
 Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
        (ByVal hWnd As LongPtr, ByVal nIndex As Long) As Long
            
 Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
        (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

The other part of this is knowing when to use Long, LongLong and LongPtr variables.

The simple answer is you need to use LongPtr for handles and pointers. Long for everything else
LongLong isn't needed. It was designed for use in 64-bit VBA7 only but LongPtr does exactly the same job for both 32-bit & 64-bit

Hope that helps

 

[isladogs]

Dears,

Thank you all for your valuable input.
I will (for now) not take the risk to end up with different versions.

IT installed a VMWare and made a decision to install 64bits which forces work to another.
According to my opinion one should use a development version closest to the one in use by the users which IT knew was 32 bits.

Ben

Hi Ben
I can understand you wishing to revert to 323-bit for all users. It will make life a lot simpler.
For certain power users of Excel, 64-bit is worthwhile as I think was stated earlier by @The_Doc_Man
Using 64-bit provides almost no benefit for Access other than better use of memory
Even that issue will be dealt with for A365 users later this year when support for large address awareness is added to 32-bit Access (slated for July 2021)
NOTE: Phillip Stiefel has code that allows LAA support now. If anyone is interested, I can provide a link

Nevertheless, the trend is increasingly towards 64-bit installations and indeed that is now the default for 365, whether we like it or not.
So at some point, all developers will have to face up to managing 64-bit issues.
If possible choose a time that suits you rather than it be forced upon you.

In the meantime, if you read my previous reply to @linxpatrick, you will see that this really isn't as difficult as many people believe.
Many articles provide more complicated solutions than required.
Indeed many of my older apps were based on such advice and used conditional compilation like this

#If Win64 Then
....
#ElseIf VBA7 Then
....
#Else
....
#End If

Over a period of time, as I update each of my apps, I am removing such constructs as being totally unnecessary

 

[gemma-the-husky]

Just one point. If you are using accde's then I don't think you can use a conditional declaration within the code, and have it work under both 32bit and 64bit. An.accdb will run successfully in that environment, but you need to build 2 accde's one built for each bitness environment. Therefore you need a way of installing both versions of access. (or compile the code for the 64bit accde on one of the 64 bit machines).

 

[gemma-the-husky]

@isladogs

As MS is now installing 64bit by default, whereas it used to install 32bit by default, it wouldn't surprise me if at some point in the future 32bit access becomes unsupported.

 

[isladogs]

Hi Dave
In response to your last two posts
1. ACCDEs will only work in the bitness they were created in. See post #3
2. I agree that at some point MS will probably stop releasing 32-bit versions of Windows and Office/ Access just as they stopped releasing 16-bit software around 20 years ago. However, older 32-bit versions will still be in use and able to create 32-bit ACCDEs and ACCDB files will continue to work in both bitnesses.

At some point in the future, 128-bit applications may become available and eventually the default...but I doubt that's something that needs concern me.

 

[boerbende]

Hi again everybody

Thank you very much for the tips.

A little more background might explain my despair. Somebody recognizes this maybe?

I am responsible for developing, maintaining and administrating a production MES system: 20 frontends linked to 31 backend databases in SQL server. Databases follow the whole product flow, from incoming materials, product recipes, production details, P&T tests, all measurement data, packaging traceability and generates at the end automatically our product certificates for the customers.
Several years ago I was using paper administrations to investigate production issues, now I find the root cause of most of quality complaints behind my computer in a fraction of the time.

Investigation of quality complaints? Indeed, I am supposed to be the Quality Engineer.

Even though IT states I am maintaining a production critical system, and even though I am the only one knowing the system (yes, single point of failure), IT asked me about the 64bits: “could you please try it”

I recently found out that SQL users were made “administrator”. Reason? Because “then it works”. I solved the real root causes and am now reorganizing the security of our server to prevent the server from being messed up + had to write a Non Conformity about giving users access to confidential information. With computers running on public places (canteen) I can do serious damage to our production. Most production computers have written down passwords because the login procedure is difficult yet “required”. That login procedure should make it safe as well is apparently less important. I had taken several pictures of teamviewer logins public screens and even tried to access one from home: it was scary to experience how simple hackers can get powerful access. And even a loss of more than 10 Gigabit of information of a very expensive repair due to a server reinstall (no backup) was still not enough to consider at least an investigation about how this could have happened. But let's keep smiling, even if we have to answer the same solution over and over again.

So, maintaining a MES system beside my job has made me quite desperate when I receive questions like “could you please try it”. Hence my escape to the professionals in this forum. I think the most of you don’t realize the relieve I find here

If someone had given me the magic want, maybe I would still have tried it yesterday evening. But today I decided to ask IT for two things: a virtualized copy of my old Proliant with 32bits Office and a roll out plan for 64 bits so we (read “me”) can investigate the impact at front.
I am convinced I will manage the upgrade to 64 bits, but I just need a little more time to prevent “happy little accidents “

Thanks again

Ben

 

[Pat Hartman]

As Colin already mentioned, except that .accde's have to be created by a version of Access that matches the bitness of the version that you are running on, there really shouldn't be an issue.

If you are using ANY API's you'll have a problem and Colin posted simplified code to help with that. If you need a more concrete example of code I used, I can post it or if there is a non-API solution, go with that. Same for Active-X controls. Great as they are, they become a sea anchor after several years.

The one thing that is important when developing Access apps is to develop in the lowest common denominator. If you have people running Win 7 and A2010, then that is the environment you need to develop in. MS has been very good about making new releases of Windows and Office compatible with older versions but the converse cannot be true and that is quite logical if you just sit down and think about it. There is no way the developers of A2010 could envision the changes made to A2019 nine years in the future. So, Access is designed to "promote" references but it cannot "demote" them much as we would like that capability.

 

[zeroaccess]

If you use Modern Charts from Access 2019 your database cannot be opened in an earlier version.

 

[isladogs]

Not quite true.
A database containing a modern chart in a form or report can still be opened in e.g. A2010 but the chart isn't displayed.

However, adding large number datatype (bigint) support will indeed prevent the database being opened in versions prior to 2016

 

[zeroaccess]

Not quite true.
A database containing a modern chart in a form or report can still be opened in e.g. A2010 but the chart isn't displayed.

Then Access is lying to me every time I save my .accde

 

[isladogs]

Again not quite true
ACCDEs cannot be opened in earlier versions of Access whether they contain 'new' features or not.
If you try to do so, you will get a message like this:

That was an A365 ACCDE with no new features being opened in A2010

 

[zeroaccess]

The error it gives is specifically mentioning modern charts.