Converting 32-bit to 64-bit

[Philip Herlihy]

I figure the time is right to move to 64-bit versions of most things, including
my new Office 365 installation.

I have some (crucial!) databases built years ago in Access 2013, which use
linked tables, so it's the "application" part I'm wary of wrecking. I can find
guidance on updating the VBA, but what about forms and reports?

My database has the following References:
Visual Basic For Applications
Microsoft Access 15.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Windows Common Controls 6.0 (SP6)

Are there equivalents in 64-bit Office 365?

[Keith Tizzard]

I am not able to provide a full answer although I have progressively updated databases from early versions of Access to Office 365 over a number of years.

One feature that you should be aware of is the use of 32 bit library functions. You will need to add the keyword 'PtrSafe' e.g.

Private Declare PtrSafe Function RegOpenKey Lib "advapi32" Alias "RegOpenKeyA" _
(ByVal hKey As Long, ByVal lpValueName As String, phkResult As Long) As Long

Look at the article:

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ptrsafe-keyword

Good luck

Post by Philip Herlihy
I figure the time is right to move to 64-bit versions of most things, including
my new Office 365 installation.
I have some (crucial!) databases built years ago in Access 2013, which use
linked tables, so it's the "application" part I'm wary of wrecking. I can find
guidance on updating the VBA, but what about forms and reports?
Visual Basic For Applications
Microsoft Access 15.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Windows Common Controls 6.0 (SP6)
Are there equivalents in 64-bit Office 365?
--
Phil, London

[Philip Herlihy]

In article <ea5bbad3-e707-4ad4-b5b8-***@googlegroups.com>, Keith
Tizzard wrote...

Post by Keith Tizzard
I am not able to provide a full answer although I have progressively updated databases from early versions of Access to Office 365 over a number of years.
One feature that you should be aware of is the use of 32 bit library functions. You will need to add the keyword 'PtrSafe' e.g.
Private Declare PtrSafe Function RegOpenKey Lib "advapi32" Alias "RegOpenKeyA" _
(ByVal hKey As Long, ByVal lpValueName As String, phkResult As Long) As Long
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ptrsafe-keyword
Good luck

Post by Philip Herlihy
I figure the time is right to move to 64-bit versions of most things, including
my new Office 365 installation.
I have some (crucial!) databases built years ago in Access 2013, which use
linked tables, so it's the "application" part I'm wary of wrecking. I can find
guidance on updating the VBA, but what about forms and reports?

...

Post by Keith Tizzard

Post by Philip Herlihy
Phil, London

Thanks, Keith. I was just returning here to report on progress. I did find
references to the PtrSafe qualifier in three YouTube videos on the subject
(though the other aspects I was concerned about weren't mentioned).

On a machine with 32-bit Office I copied files to a "Test" folder, and re-
linked them. I installed Office 365 (64-bit) on a new machine, and shared
copies of the files via OneDrive**. Everything just worked on the new machine
(to my surprise). Now, I'm not using any external controls or add-ins, so
there are no "Declare" statements in my code. 64-bit Access seems to have
everything needed for my code out-of-the-box. I still have some testing to do
before I start using it for live data, but it looks good - and has been much
less trouble than expected!

It remains to be seen whether I can work with the same data on machines with
different 'bitness' versions of Access installed, given the database was
developed on a 32-bit machine.

**OneDrive has an annoying habit of creating the local (synchronised) folder
with different %USERNAME%s on each machine despite using an identical Microsoft
Account. On my desktop the %ONEDRIVE% path is C:\Users\xyz_000, giving a
OneDrive path of C:\Users\xyz_000\OneDrive, while on the new machine it's C:
\Users\xyz and c:\Users\xyz\OneDrive respectively. So the linking (via Linked
Table Manager)done on one machine isn't valid for the other. I solved this
with a Junction Point (mklink /J xyz xyz_000) which creates an equivalent path
to the files on the new machine. (Note that OneDrive synchronisation is WAY
too slow to use for sharing a database between users.)

[Ron Paii]

Post by Philip Herlihy
Tizzard wrote...

Post by Keith Tizzard
I am not able to provide a full answer although I have progressively updated databases from early versions of Access to Office 365 over a number of years.
One feature that you should be aware of is the use of 32 bit library functions. You will need to add the keyword 'PtrSafe' e.g.
Private Declare PtrSafe Function RegOpenKey Lib "advapi32" Alias "RegOpenKeyA" _
(ByVal hKey As Long, ByVal lpValueName As String, phkResult As Long) As Long
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ptrsafe-keyword
Good luck

Post by Philip Herlihy
I figure the time is right to move to 64-bit versions of most things, including
my new Office 365 installation.
I have some (crucial!) databases built years ago in Access 2013, which use
linked tables, so it's the "application" part I'm wary of wrecking. I can find
guidance on updating the VBA, but what about forms and reports?

...

Post by Keith Tizzard

Post by Philip Herlihy
Phil, London

Thanks, Keith. I was just returning here to report on progress. I did find
references to the PtrSafe qualifier in three YouTube videos on the subject
(though the other aspects I was concerned about weren't mentioned).
On a machine with 32-bit Office I copied files to a "Test" folder, and re-
linked them. I installed Office 365 (64-bit) on a new machine, and shared
copies of the files via OneDrive**. Everything just worked on the new machine
(to my surprise). Now, I'm not using any external controls or add-ins, so
there are no "Declare" statements in my code. 64-bit Access seems to have
everything needed for my code out-of-the-box. I still have some testing to do
before I start using it for live data, but it looks good - and has been much
less trouble than expected!
It remains to be seen whether I can work with the same data on machines with
different 'bitness' versions of Access installed, given the database was
developed on a 32-bit machine.
**OneDrive has an annoying habit of creating the local (synchronised) folder
with different %USERNAME%s on each machine despite using an identical Microsoft
Account. On my desktop the %ONEDRIVE% path is C:\Users\xyz_000, giving a
\Users\xyz and c:\Users\xyz\OneDrive respectively. So the linking (via Linked
Table Manager)done on one machine isn't valid for the other. I solved this
with a Junction Point (mklink /J xyz xyz_000) which creates an equivalent path
to the files on the new machine. (Note that OneDrive synchronisation is WAY
too slow to use for sharing a database between users.)
--
Phil, London

Access normally has no issues between 32bit and 64bit. The problems come from 3rd party controls including Microsoft's standard controls like the tree control. You have already found the issue with PtrSafe. Other Office programs like Excel also may have issues with custom controls in a 64bit install. The backend database should only be stored on a Microsoft share, not on something like OneDrive; it's file sync is incompatible with a shared database file.

Search "Access database on OneDrive"; one of the 1st hits will be from Microsoft.

IMO, unless you are dealing with very large Word or Excel there is very little reason to use 64bit office.

[Philip Herlihy]

In article <d804cb13-1227-4d3f-9013-***@googlegroups.com>, Ron Paii
wrote...

Post by Ron Paii

Post by Philip Herlihy
Tizzard wrote...

Post by Keith Tizzard
I am not able to provide a full answer although I have progressively updated databases from early versions of Access to Office 365 over a number of years.
One feature that you should be aware of is the use of 32 bit library functions. You will need to add the keyword 'PtrSafe' e.g.
Private Declare PtrSafe Function RegOpenKey Lib "advapi32" Alias "RegOpenKeyA" _
(ByVal hKey As Long, ByVal lpValueName As String, phkResult As Long) As Long
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ptrsafe-keyword
Good luck

Post by Philip Herlihy
I figure the time is right to move to 64-bit versions of most things, including
my new Office 365 installation.
I have some (crucial!) databases built years ago in Access 2013, which use
linked tables, so it's the "application" part I'm wary of wrecking. I can find
guidance on updating the VBA, but what about forms and reports?

...

Post by Keith Tizzard

Post by Philip Herlihy
Phil, London

Thanks, Keith. I was just returning here to report on progress. I did find
references to the PtrSafe qualifier in three YouTube videos on the subject
(though the other aspects I was concerned about weren't mentioned).
On a machine with 32-bit Office I copied files to a "Test" folder, and re-
linked them. I installed Office 365 (64-bit) on a new machine, and shared
copies of the files via OneDrive**. Everything just worked on the new machine
(to my surprise). Now, I'm not using any external controls or add-ins, so
there are no "Declare" statements in my code. 64-bit Access seems to have
everything needed for my code out-of-the-box. I still have some testing to do
before I start using it for live data, but it looks good - and has been much
less trouble than expected!
It remains to be seen whether I can work with the same data on machines with
different 'bitness' versions of Access installed, given the database was
developed on a 32-bit machine.
**OneDrive has an annoying habit of creating the local (synchronised) folder
with different %USERNAME%s on each machine despite using an identical Microsoft
Account. On my desktop the %ONEDRIVE% path is C:\Users\xyz_000, giving a
\Users\xyz and c:\Users\xyz\OneDrive respectively. So the linking (via Linked
Table Manager)done on one machine isn't valid for the other. I solved this
with a Junction Point (mklink /J xyz xyz_000) which creates an equivalent path
to the files on the new machine. (Note that OneDrive synchronisation is WAY
too slow to use for sharing a database between users.)
--
Phil, London

Access normally has no issues between 32bit and 64bit. The problems come from 3rd party controls including Microsoft's standard controls like the tree control. You have already found the issue with PtrSafe. Other Office programs like Excel also may have issues with custom controls in a 64bit install. The backend database should only be stored on a Microsoft share, not on something like OneDrive; it's file sync

is incompatible with a shared database file.

Post by Ron Paii
Search "Access database on OneDrive"; one of the 1st hits will be from Microsoft.
IMO, unless you are dealing with very large Word or Excel there is very little reason to use 64bit office.

Thanks for this; it has certainly been easier than I'd anticipated - I haven't
even needed to use PrtSafe in my code.

You're right to flag the potential problem using Access linking tables via
OneDrive, and I did think it only responsible to include a comment about that
in my post. However, in my situation the database is used only by me, and at
the time of use the linked databases are both in the same local folder. That
folder is synchronised via OneDrive, so when I move to another machine
(typically tablet and desktop) I ensure synchronisation is complete before
launching. That's a far cry from the situation where concurrent users are
linked to the same tables.

I did wonder about whether there would be any point in installing the 64-bit
versions, and did an online search. Microsoft's advice used to be against the
64-bit version unless you knew you needed it (and most don't). But that's
changed, and the "default" installation is now the 64-bit. Actually, it's hard
to know whether a spreadsheet (likeliest thing to hit the 32-bit limit, I
figure) is "large" or not. Generally, I like to go with the flow, and the
trend is clearly towards 64-bit applications.

Hmm. I have one file I've been adding rows to every few days for well over a
decade. So I Googled "excel 32 bit limit" and found the maximum file size is 2
GB. How big is my file? 466KB. The memory limit is 4GB (of course). My
file's working set is under 84KB. So I've a few months grace yet, then...

[Philip Herlihy]

In article <***@news.eternal-september.org>, Philip
Herlihy wrote...

Post by Philip Herlihy
wrote...

Post by Ron Paii

Post by Philip Herlihy
Tizzard wrote...

Post by Keith Tizzard
I am not able to provide a full answer although I have progressively updated databases from early versions of Access to Office 365 over a number of years.
One feature that you should be aware of is the use of 32 bit library functions. You will need to add the keyword 'PtrSafe' e.g.
Private Declare PtrSafe Function RegOpenKey Lib "advapi32" Alias "RegOpenKeyA" _
(ByVal hKey As Long, ByVal lpValueName As String, phkResult As Long) As Long
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ptrsafe-keyword
Good luck

Post by Philip Herlihy
I figure the time is right to move to 64-bit versions of most things, including
my new Office 365 installation.
I have some (crucial!) databases built years ago in Access 2013, which use
linked tables, so it's the "application" part I'm wary of wrecking. I can find
guidance on updating the VBA, but what about forms and reports?

...

Post by Keith Tizzard

Post by Philip Herlihy
Phil, London

Thanks, Keith. I was just returning here to report on progress. I did find
references to the PtrSafe qualifier in three YouTube videos on the subject
(though the other aspects I was concerned about weren't mentioned).
On a machine with 32-bit Office I copied files to a "Test" folder, and re-
linked them. I installed Office 365 (64-bit) on a new machine, and shared
copies of the files via OneDrive**. Everything just worked on the new machine
(to my surprise). Now, I'm not using any external controls or add-ins, so
there are no "Declare" statements in my code. 64-bit Access seems to have
everything needed for my code out-of-the-box. I still have some testing to do
before I start using it for live data, but it looks good - and has been much
less trouble than expected!
It remains to be seen whether I can work with the same data on machines with
different 'bitness' versions of Access installed, given the database was
developed on a 32-bit machine.
**OneDrive has an annoying habit of creating the local (synchronised) folder
with different %USERNAME%s on each machine despite using an identical Microsoft
Account. On my desktop the %ONEDRIVE% path is C:\Users\xyz_000, giving a
\Users\xyz and c:\Users\xyz\OneDrive respectively. So the linking (via Linked
Table Manager)done on one machine isn't valid for the other. I solved this
with a Junction Point (mklink /J xyz xyz_000) which creates an equivalent path
to the files on the new machine. (Note that OneDrive synchronisation is WAY
too slow to use for sharing a database between users.)
--
Phil, London

Access normally has no issues between 32bit and 64bit. The problems come from 3rd party controls including Microsoft's standard controls like the tree control. You have already found the issue with PtrSafe. Other Office programs like Excel also may have issues with custom controls in a 64bit install. The backend database should only be stored on a Microsoft share, not on something like OneDrive; it's file

sync

Post by Philip Herlihy
is incompatible with a shared database file.

Post by Ron Paii
Search "Access database on OneDrive"; one of the 1st hits will be from Microsoft.
IMO, unless you are dealing with very large Word or Excel there is very little reason to use 64bit office.

Thanks for this; it has certainly been easier than I'd anticipated - I haven't
even needed to use PrtSafe in my code.
You're right to flag the potential problem using Access linking tables via
OneDrive, and I did think it only responsible to include a comment about that
in my post. However, in my situation the database is used only by me, and at
the time of use the linked databases are both in the same local folder. That
folder is synchronised via OneDrive, so when I move to another machine
(typically tablet and desktop) I ensure synchronisation is complete before
launching. That's a far cry from the situation where concurrent users are
linked to the same tables.
I did wonder about whether there would be any point in installing the 64-bit
versions, and did an online search. Microsoft's advice used to be against the
64-bit version unless you knew you needed it (and most don't). But that's
changed, and the "default" installation is now the 64-bit. Actually, it's hard
to know whether a spreadsheet (likeliest thing to hit the 32-bit limit, I
figure) is "large" or not. Generally, I like to go with the flow, and the
trend is clearly towards 64-bit applications.
Hmm. I have one file I've been adding rows to every few days for well over a
decade. So I Googled "excel 32 bit limit" and found the maximum file size is 2
GB. How big is my file? 466KB. The memory limit is 4GB (of course). My
file's working set is under 84KB. So I've a few months grace yet, then...

For those interested in the topic, here is Microsoft's current advice:
https://bit.ly/3bQd9dC

[Cindy Krist]

What if your code determines whether you have 32- or 64-bit (https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/64-bit-visual-basic-for-applications-overview )?

#If VBA7 Then
Declare PtrSafe Function...
#Else
Declare Function...
#EndIf

[Philip Herlihy]

In article <190139d6-ea63-4c49-aa00-***@googlegroups.com>, Cindy
Krist wrote...

Post by Cindy Krist
What if your code determines whether you have 32- or 64-bit (https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/64-bit-visual-basic-for-applications-overview )?
#If VBA7 Then
Declare PtrSafe Function...
#Else
Declare Function...
#EndIf

Yes, I've seen that - thanks. My own code doesn't bring in any external
components, so doesn't need the Declare statements. If it did, that is
certainly the recommended approach, I understand.