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?

--

Phil, London

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

On Saturday, 2 July 2022 at 14:53:24 UTC+1, Philip Herlihy wrote:
> 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?
>
>
> --
>
> Phil, London

In article <ea5bbad3-e707-4ad4-b5b8-0872c693b821n@googlegroups.com>, Keith
Tizzard wrote...
>
> 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
>
>
> On Saturday, 2 July 2022 at 14:53:24 UTC+1, Philip Herlihy wrote:
> > 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?
> >
....
> >
> >
> >
> > 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.)

--

Phil, London

On Tuesday, July 5, 2022 at 6:01:22 AM UTC-5, Philip Herlihy wrote:
> In article <ea5bbad3-e707-4ad4...@googlegroups.com>, Keith
> Tizzard wrote...
> >
> > 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
> >
> >
> > On Saturday, 2 July 2022 at 14:53:24 UTC+1, Philip Herlihy wrote:
> > > 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?
> > >
> ...
> > >
> > >
> > >
> > > 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.)
>
> --
>
> 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.

In article <d804cb13-1227-4d3f-9013-59a13ae929bcn@googlegroups.com>, Ron Paii
wrote...
>
> On Tuesday, July 5, 2022 at 6:01:22 AM UTC-5, Philip Herlihy wrote:
> > In article <ea5bbad3-e707-4ad4...@googlegroups.com>, Keith
> > Tizzard wrote...
> > >
> > > 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
> > >
> > >
> > > On Saturday, 2 July 2022 at 14:53:24 UTC+1, Philip Herlihy wrote:
> > > > 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?
> > > >
> > ...
> > > >
> > > >
> > > >
> > > > 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.)
> >
> > --
> >
> > 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.

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

--

Phil, London

In article <MPG.3d2f7b759499ca19899ff@news.eternal-september.org>, Philip
Herlihy wrote...
>
> In article <d804cb13-1227-4d3f-9013-59a13ae929bcn@googlegroups.com>, Ron Paii
> wrote...
> >
> > On Tuesday, July 5, 2022 at 6:01:22 AM UTC-5, Philip Herlihy wrote:
> > > In article <ea5bbad3-e707-4ad4...@googlegroups.com>, Keith
> > > Tizzard wrote...
> > > >
> > > > 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
> > > >
> > > >
> > > > On Saturday, 2 July 2022 at 14:53:24 UTC+1, Philip Herlihy wrote:
> > > > > 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?
> > > > >
> > > ...
> > > > >
> > > > >
> > > > >
> > > > > 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.)
> > >
> > > --
> > >
> > > 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.
>
> 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

--

Phil, London

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

In article <190139d6-ea63-4c49-aa00-8320a276a2c5n@googlegroups.com>, Cindy
Krist wrote...
>
> 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.

--

Phil, London