64-Bit Access
By Richard Rost
5 years ago
Make Databases Safe for 32-Bit and 64-Bit Office
In this video, I will show you how to take your existing 32-bit Access databases and upgrade their VBA code to run under 64-bit Office using the PtrSafe attribute.
Ethan from Hastings, England (a Platinum Member) asks: I just upgraded from 32-bit to 64-bit Office, and my Access database which I have been using for years is now telling me "the code in this project must be updated for use in 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute." What do I do?
Members
Members will learn a few more things about migrating from 32-bit to 64-bit including the new VBA7 compiler pre-directive for maintaining multiple versions of your database. We'll also talk about the new data types like LongLong and LongPtr, and when you would need to use them, and we'll discuss issues that come up with maintaining multiple versions of your database for 32-bit and 64-bit users, with possible different versions of Access, and with ACCDE files.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
 Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, microsoft office, 32-bit, 32 bit, 64-bit, 64-bit, declare, ptrsafe, longlong, longptr, Win64, VBA7, sleep function, kernel32
Subscribe to 64-Bit Access
Get notifications when this page is updated
Intro In this video, I will show you how to update your Microsoft Access database to ensure it works properly on both 32-bit and 64-bit versions of Access. We will look at common problems that occur when upgrading from 32-bit to 64-bit, especially with VBA code and Windows API calls, and I will demonstrate how adding the PtrSafe attribute can resolve most errors. I will also discuss compatibility concerns with ActiveX controls and third-party objects, and offer tips for maintaining consistency across different versions of Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I am going to show you how to make sure your Microsoft Access database will run properly on both 32-bit and 64-bit versions of Access. This one is for the programmers. So if you are not a VBA programmer, go watch my Intro to VBA video and become one. I'll put a link down below.
For all of the rest of you who have had to upgrade 32-bit databases to 64-bit and you get that compile error message that you see there on the screen, this video is for you.
Today's question comes from Ethan in Hastings, England, one of my platinum members. Ethan says, I just upgraded my 32-bit to 64-bit Office and my Access database which I have been using for years is now telling me the code in this project must be updated for use in 64-bit systems. Please review and update the declare statements and then mark them with the pointer safe attribute. What do I do?
Well Ethan, you are not alone. I have been getting asked this question at least once or twice a month ever since the 64-bit version of Access came out in, I believe, 2010.
At first, I honestly did not recommend that people upgrade to Office 64-bit unless they absolutely needed it. The major benefit with 64-bit was with Excel because some people have really, really large spreadsheets and Excel gives you a lot more flexibility that way. Although my personal opinion is if you have a huge gigantic spreadsheet, that data probably should be in Access anyway, but that is a whole different topic.
But 32-bit was still the default installation until I believe 2013 or 2016, so at first, it was not a big deal. If you wanted 64-bit, you had to manually switch the setting when you set up Office and you had to install 64-bit on purpose.
Now 64-bit is the default installation, so people who are upgrading from, say, 2013 to 2016 or 2019, they are getting 64-bit installed whether they want it or not and their databases are not working. Yes, you can uninstall it and go back to 32-bit if you want to, but most people do not want to do that.
And no, you cannot run both versions of Access or Office in general on the same computer. You have to have two different computers to run the different versions.
Some of the other problems with 64-bit are that it may not be compatible with ActiveX controls or third-party objects. I usually avoid those anyway. I do not like using things that are not built into Access, but a lot of people use them. So if you have a database that is using ActiveX controls, like the file dialog picker, the color picker, those kinds of things, they may not work. Microsoft has upgraded a lot of them, but there are a lot of third-party objects, especially people who are running databases that were built in 2000 and that company is no longer around. If you upgrade Access, you are out of luck.
Plus, and this is something I do not understand why Microsoft did this, 64-bit Access still has the two gigabyte file limit. I do not know why they did not upgrade that.
The major reason why most people's databases will not work is because you have to update Windows API calls in VBA to work specifically with 64-bit Access. Now if you do not know what a Windows API call is, if you have not done any programming with Windows API calls, then this video probably is not for you anyway, unless someone else built your database and you are going to have to figure out what they did.
But basically, a Windows API call is a special bit of programming where you can have Access go out to Windows and use one of its features that are built into the operating system, like browsing for a file, for example, with a file dialog. You can use the Windows file picker so you do not have to go and make your own.
There is a sleep function I am going to show you in just a minute where you can have Access pause. That is built into Windows.
Windows application programming interface is how you can get Access to use other code that is built into Windows or other applications. There are APIs for Excel, Outlook. We use the Windows API and the Outlook API, for example, to send email through Outlook.
Basically, programming languages are not languages for their programming, extensions if you will, to use other programs.
Now if you compile your database and it runs fine, most Access databases, if you are not using Windows API calls or ActiveX controls, will run just fine on both 32 and 64-bit Office. So if you are not getting this error message, do not worry about it. You can stop watching this video now.
But if you are learning VBA and you, like me, like to find new bits of code all the time in books and online and like to incorporate them into your database, if you get this error message about the 64-bit thing, then continue watching, I'll show you what it means.
Now I do have two different computers running 32 and 64-bit Access. I kind of have to because I teach this stuff and I have to be able to make both types of databases.
We are going to start off on my system here that is running 32-bit. Like a lot of you, I have older databases that were built under 32-bit. Let's open it up.
This is my TechHelp free template. It is a free download from my website. You can go grab a copy if you want to.
Now most of you have 32 and 64-bit versions and never noticed a difference, because if you have the ACCDB file, the full version of this file, which is what I give away on my website, then it really does not matter if you are using 32-bit or 64-bit because I do not have any code in this database that is specific to one version or another. I do not have any Windows API calls at all in here.
They normally would be down here in a module. So if in your office or wherever you are distributing your database to, or if it is just you, if you do not have any Windows API calls or third-party controls or ActiveX objects, you are probably just fine. Your database will run fine on every machine in your office, whether they have 32 or 64-bit.
But let's put some Windows API calls in here. So let's go to create and then module. The VBA code window opens up.
If you have never done any VBA programming, you are probably in the wrong class. Go watch my Intro to VBA class if you have not already.
I am going to copy and paste in here a real simple API declaration. Control V, I pasted it. It is public declare sub sleep and it is from library kernel32.dll. Basically, it puts the computer to sleep for a certain number of milliseconds. This is handy if you are executing code and you want to put a pause in there. You want to count from one to 10 and go one, two, three. There are reasons why.
In my email seminar, for example, I have it so that you can put a little pause between sending emails because if you are using Gmail or your internet provider has a maximum cap on how many emails you can send in a given amount of time, like within an hour, you can put a two-minute delay.
That is what the sleep function is good for. Let me show you how it works. So now that we have this, I am going to save this. It is going to be a module, give the module a name, just my global modules, for example, that is fine.
Now you will find that guy sits right down here. You have to declare those in the global module.
Now on my main menu, I have my little Hello World box right here. If you have not watched my video where I build this template, the blank TechHelp free template, go watch that, it explains how this thing works.
In the code in here, if I click on it, all it does is status Hello World. But I am going to status, let us status, like one to 10. So I am going to dim x as an integer, for x equals one to 10, next. In here, I am going to say status and then, count and x. Save that.
Now if I open that back up again, if I click the button, it just goes biometially, right? There is no delay in there. Let us put a delay.
Normally Access VBA does not have a command like that, but we can now use sleep. So I can say sleep and then how many milliseconds? Let us go 500 milliseconds, half a second. Save it. Come back out here, click, and you can see it counting up now, every half second. So that is just an example of one type of API. There are hundreds of them, so many of them.
Now, let us say I am going to share this in my office. So I am going to close the database down and I am going to send this over to Joe, my coworker, but he has 64-bit Access on his computer. So let us see what happens there.
Here I am over on my 64-bit machine. I am going to open this up. The database loads just fine. Let us click Hello World. And there we go. Compile error. There is the error message. The code in this project must be updated for use on 64-bit systems. Hit OK.
Nine times out of ten, you can get away with just putting one extra word in that API call. Here is the compiler: public declare. Right after that word declare, put "PtrSafe." That is it. And that will work most of the time, not all the time, but most of the time.
Certainly, most of the code that you will get from me, if you are one of my students, and you have one of my older databases that you might have downloaded from my website a while back, pretty much everything that I have released, this is all you need to do. It will not work with everything you will find out there. I am not guaranteeing that. It is not the cure-all, but it will fix 90 percent of the problems.
Now let us give it a compile. Debug, Compile. You should always do this before distributing your database. Everything is fine, no error messages. Close it and then click go, and it runs and it works.
Now, what did that do? Maybe nothing. Functionally, internally, PtrSafe does not do a thing. All it does is tell Access that, "Hey, I have checked this function, and I am certifying as the programmer that it is safe for 64-bit systems." That is it. That is all we did.
The reason why they make you do that is because if you are not careful, especially if you are using pointers, which is a more advanced programming thing, and I have not even covered in my developer classes yet, but if you are using some data types and you mix and match them between 32 and 64-bit, you are not supposed to, you can cause problems. Access can hang, you can corrupt the memory. There are all kinds of crazy things that will happen. But generally, this is fine.
Pretty much, you have to go through your code everywhere you see the word declare. Just put PtrSafe after it.
The good news is, even if I go back to my original 32-bit database, let us open that back up again. Even if I go back here, if I take this code and I put PtrSafe here, it should still compile. In the newer versions of Access, 32-bit Access will run just fine if you put that PtrSafe in there, and I can still run the code.
So if you have got the same version of Access, like 2016, I have got a 365 subscription, so mine is roughly equivalent to 2019, then you can put that in your code and still use the same database on 32 and 64-bit machines.
However, problems will arise if you have got different versions of Access in your office, like someone is on 2013, someone is on 2016, and you have got different bit versions too. So there are a lot of little things that you still have to deal with.
I personally strongly suggest, if you have got people using the same database, everyone should have the same version of Access, and everyone should have the same bit version too. So have everyone using 365 subscription with 64-bit, or Access 2016, 32-bit. Just make sure everybody on your platform is the same and you should not run into these problems.
Now, there are some other issues you might have to take into consideration. One of them is using pointers, and I will discuss that and a bunch of other stuff in the extended cut for the members.
In the extended cut, we will learn a few more things about migrating from 32-bit to 64-bit, including the new VBA7 compiler pre-directive for maintaining multiple versions of your database. We will also talk about the new data types like LongLong and LongPtr, and when you would need to use them.
We will discuss issues that come up with maintaining multiple versions of your database for 32-bit and 64-bit users, with possible different versions of Access, and how that changes the way you have to distribute ACCDE files. All of that is coming up in the extended cut for members. Silver members and up get access to all of my extended cut videos.
How do you become a member? Click the join button below the video. After you click the join button, you will see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more.
Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use.
Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP and lots more.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted.
Click on the show more link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons and lots more.
YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list.
Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1, and it is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.Quiz Q1. What is the main issue when running a Microsoft Access database with Windows API calls written for 32-bit on a 64-bit version of Access?
A. The API calls may not function and cause compile errors.
B. The forms will not display properly.
C. The tables will not open at all.
D. The queries will take twice as long to run.
Q2. What is the "PtrSafe" keyword used for in VBA API declarations?
A. It tells Access the function is safe to use on 64-bit systems.
B. It makes the code faster.
C. It converts 32-bit code automatically to 64-bit.
D. It helps Access find missing objects.
Q3. After updating an API declaration for 64-bit Access, where should the PtrSafe keyword be added?
A. Immediately after the word 'Declare' in the statement.
B. At the end of the declaration line.
C. Before the word 'Public.'
D. As a comment above the declaration.
Q4. Which of the following is NOT a common problem encountered when upgrading from 32-bit to 64-bit Access?
A. Incompatible ActiveX controls or third-party objects.
B. Windows API calls needing updates.
C. Two gigabyte file size limit still present.
D. Built-in macros do not work at all.
Q5. What is a Windows API call commonly used for in an Access database?
A. Extending Access functionality to interact with Windows features like file dialogs
B. Changing the Access user interface color scheme
C. Creating new tables automatically
D. Importing data from external databases
Q6. If you add the PtrSafe keyword to your 32-bit Access VBA code, what will happen when you run the database in a newer version of 32-bit Access?
A. It should still compile and run fine.
B. The database will crash.
C. The database will refuse to open.
D. It will delete your tables.
Q7. What is the recommended way to prevent compatibility issues in a shared Access database environment?
A. Ensure all users have the same version and bitness of Access.
B. Only use built-in macros and never use forms.
C. Store databases on USB drives only.
D. Use Access Runtime instead of the full version.
Q8. What does the Sleep function from the Windows API allow you to do in VBA code?
A. Pause the execution of code for a specified amount of time
B. Shut down the computer automatically
C. Lock the user out of the database
D. Reset Access settings to default
Q9. What will usually happen if you do not update API declarations for 64-bit Access, and you try to use 32-bit code in 64-bit Access?
A. You will receive a compile error message.
B. The database will run, but very slowly.
C. Access will automatically fix the code.
D. The code will be ignored without errors.
Q10. What is a potential issue of using different Access versions and bitness in one office?
A. Some databases may not run, leading to compatibility problems.
B. It speeds up database queries.
C. It increases database security.
D. Users can share databases more easily.
Answers: 1-A; 2-A; 3-A; 4-D; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on ensuring your Microsoft Access database works properly on both 32-bit and 64-bit versions of Access. This lesson is specifically aimed at programmers. If you are not already familiar with VBA programming, I recommend starting with my Intro to VBA class before continuing here.
Many developers encounter problems when moving from 32-bit to 64-bit Access, especially after upgrading Office and then seeing compile errors about code compatibility. I regularly get asked about this by students who have trouble running older databases after an upgrade. While I did not initially encourage moving to 64-bit Office for most users, since the primary benefits were for large Excel spreadsheets, it has now become the default setup for new Office installations. This has caught a lot of Access users off guard, who often find their applications no longer work after an upgrade and, understandably, do not want to reinstall the 32-bit version just to keep their databases running.
It is also important to realize that you cannot have both 32-bit and 64-bit versions of Office installed on the same PC. Instead, you would need separate computers for each version. Additionally, the transition to 64-bit can cause extra trouble if your database uses ActiveX controls or third-party objects. Many of these legacy controls are not supported or updated, leaving your applications at risk if they rely on discontinued components.
One common question is why Access in 64-bit mode still has the two gigabyte file size limit, a restriction Microsoft decided to keep even in the newer architecture.
The main compatibility issue with moving from 32-bit to 64-bit Access for most developers comes down to updating Windows API calls in VBA. If you have never used Windows API calls, this might not affect you—unless you inherited a database with complex code. For anyone unfamiliar, API calls allow your Access application to use functionality built into Windows, such as browsing for a file or pausing execution with a sleep function.
Regular Access databases without such API calls or special controls typically run seamlessly in both 32-bit and 64-bit environments. If you have never received compatibility error messages after switching Office versions, you likely do not need to worry about this.
If, however, you like to enhance your databases by integrating snippets of code from books or online resources, you might start to get errors after moving to 64-bit. For demonstration, I keep two systems side by side—one running 32-bit and one on 64-bit Access—to illustrate these differences for my students.
Consider a simple example: I use a sleep function, declared with a Windows API call, to pause code execution for a set period. This is helpful in scenarios like slowing down a process to avoid spamming emails where providers limit sending rates. In practice, this API declaration works fine in 32-bit mode.
Problems arise if you share your database with someone using 64-bit Access. After opening and attempting to use the same feature, a compile error appears stating that the code needs to be updated for 64-bit compatibility.
The solution in most cases is straightforward. For your API declarations, you simply add the keyword "PtrSafe" immediately after "Declare." In almost every case, this is enough to make your database compatible with both 32-bit and 64-bit Access. This change essentially tells Access that you—the developer—certify the code as suitable for 64-bit systems. While there are some more advanced nuances, this step will resolve the vast majority of errors.
You should always test your code using the Debug and Compile option in the VBA editor before sharing your database. Notably, the newer 32-bit versions of Access can also handle the PtrSafe keyword, so you can safely use the same codebase for both bit versions as long as you are running relatively recent Access releases (such as 2016 or Office 365).
It's important for teams sharing databases to standardize their environments. Having everyone on the same version and bitness of Access (for example, Office 365 subscription at 64-bit) will prevent many of these headaches.
For those interested in deeper details, today's Extended Cut covers several key migration issues. We discuss the newer VBA7 compiler pre-directive for maintaining databases across multiple Access versions, explore the new LongLong and LongPtr data types and explain when you need them, and address the challenges of managing and distributing databases for both 32-bit and 64-bit users, along with the impacts on ACCDE files. All of this is exclusive to my Silver members and above.
You can find information on joining and accessing these member-only resources on my website. Becoming a Silver member grants access to all extended cut lessons and live video sessions. Gold membership adds downloadable sample databases and access to my code vault, while Platinum members get all previous perks plus my complete course library covering Access, Word, Excel, Visual Basic, ASP, and more.
Rest assured, I will continue making these free TechHelp videos, so keep watching and stay subscribed to my YouTube channel for updates. I also encourage you to read the video description for more resources, links to additional lessons, and free materials. Since YouTube no longer sends email notifications for new videos, you can join my mailing list to receive updates directly via email.
If you are new to Access, do not miss my free Access Level 1 course, which covers all the fundamentals of building an Access database. It's over three hours long and available on both my website and YouTube channel. If you enjoy Level 1, you can move on to Level 2 for just one dollar, a perk also free for all channel members.
If you would like your own question answered in an upcoming video, visit my TechHelp page for submission tools.
A complete, step-by-step video tutorial on everything I discussed here is available at my website at the link below.
Live long and prosper, my friends.Topic List Understanding 32-bit vs 64-bit versions of Access
Common issues upgrading Access databases to 64-bit
Differences in default Office installation bitness
Problems with ActiveX controls and third-party objects in 64-bit
The two gigabyte file size limit in Access
Introduction to Windows API calls in VBA
Example of a Windows API call: Sleep function
Adding a Windows API declaration to a VBA module
Demonstrating the Sleep API in an Access form
Understanding and fixing the "code must be updated for 64-bit" error
Using the PtrSafe keyword for 64-bit compatibility
Testing code with PtrSafe in both 32 and 64-bit Access
Best practices for maintaining database compatibility across bitness
Advising consistent Access versions and bitness in an organization |
