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?
Phil, London
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
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?
...
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 syncPhil, 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
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?
...
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 filePhil, 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
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...
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
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 297 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 22:09:35 |
| Calls: | 6,667 |
| Calls today: | 1 |
| Files: | 12,216 |
| Messages: | 5,337,347 |