Quick Queries #94
By Richard Rost
4 hours ago
The Microsoft Access Security Mistake People Still Make
In Quick Queries 94, we will discuss whether you might be making a Microsoft Access security mistake without realizing it, and what Access can and cannot do to protect your data. I will answer questions about opening PDFs from forms, running Access over network IP addresses, issues with AI-generated VBA code, storing pictures and PDF files properly, resolving Office install problems, using Task Scheduler for automation, and whether Access is still a practical tool in today's business environment. We will also cover the importance of moving data to SQL Server for real security rather than relying solely on Access features.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, Access security, open PDF from form, FollowHyperlink, split database, trusted locations, SQL Server backend, picture control, hiding tables, Windows Task Scheduler, storing images, storing PDF files, compact and repair, ACCDE file, OLE objects, VBA examples, network file sharing, navigation pane
Intro In Quick Queries 94, we will discuss whether you might be making a Microsoft Access security mistake without realizing it, and what Access can and cannot do to protect your data. I will answer questions about opening PDFs from forms, running Access over network IP addresses, issues with AI-generated VBA code, storing pictures and PDF files properly, resolving Office install problems, using Task Scheduler for automation, and whether Access is still a practical tool in today's business environment. We will also cover the importance of moving data to SQL Server for real security rather than relying solely on Access features.Transcript Are you making a big Microsoft Access Security mistake without even realizing it?
Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
Quick Queries is where I take all the cool questions people post on my website, YouTube comments, Reddit, email, carrier pigeon, subspace radio, messages in bottles, whatever, and I roll them all into one video. Most of these do not need full hour-long deep dives, but they are still great questions, so I put them all together in a weekly video and I call it Quick Queries, which may or may not have anything to do with actual Access queries. I think today does have one, though.
Today we are going to talk about a Microsoft Access Security mistake that a lot of developers still make. We will talk about what Access can do and what it cannot do.
We have also got other questions about opening PDFs from forms, running Access over network IP addresses, AI writing questionable VBA code, storing pictures and PDFs the right way, old Office installs breaking new runtimes, using Task Scheduler for automation, and whether Access is actually still useful in the real world - spoiler, it is.
All right, let us jump in.
Leading off today, we have Jerome. He liked my picture control video and asks if Access can open a linked PDF from a button on a form where the file path is built from things like the date, supplier, and an ID.
Yes, absolutely. In this case, I probably would not use a picture control because it is a PDF, not an image. What I would do, though, is build a full path in VBA using string concatenation based on the fields in your form, and then use the FollowHyperlink command to open it. Maybe the folder comes from the year and the month, the file name comes from the exception ID, and then Access hands it off to whatever PDF viewer Windows is using. That is a nice clean way to tie your database records to outside documents. This video should get you started. I will put a link down below.
Next up, K says they have disabled the shift key, hidden the ribbon, locked down the startup options, but newer versions of Access are still letting users maximize the window and see the tables in the navigation pane.
This is one of those ongoing battles with Access. You can hide the navigation pane, disable the special keys, lock down the startup options, make it harder for casual users to get around your interface, create an ACCDE file. There are lots of things you can do, and I have multiple videos that teach a lot of these techniques, including my simple security video and my full Security Seminar.
At the end of the day, honestly, if the data lives inside the Access file itself and somebody is determined to get to it and they know what they are doing, they can. That is why for real security, especially in a business environment, I always recommend moving the data to SQL Server and using Access as your front end.
Access security is more about keeping honest users honest than stopping a determined hacker. This video will cover a lot of the options that you can do to lock down your database. My Security Seminar will teach you to make Access as bulletproof as possible. But if you are concerned about data security, you really want to be using SQL Server for your back end.
Regarding last week's Quick Query, Shadow Dragon says, AI-generated code can really be hit or miss. Sometimes it is brilliant, sometimes it completely forgets what it was doing five minutes ago. I have seen that before. You should always double check everything and keep backups.
Yes, absolutely. That has been my experience too. I love using AI for stuff I already know how to do because it can speed things up tremendously, especially for me. I am like a one-man band. I do everything. So I can sometimes say, okay, here are my notes for a video I am going to do today. Help me flesh this out. Come up with things that I might not have thought about, that kind of stuff. I basically already know what I want to cover.
But sometimes, my brain does not always remember every little detail. That is where the AI is good for me, or for things like spell checking and grammar, or that kind of stuff.
Sometimes for code, I use it for code for things that I already know how to do, but it is good to say, oh hey, write this for me real quick. If I need a recordset loop or some repetitive VBA boilerplate, great, I will let the robot do the typing. But if it is something complicated or something I am unfamiliar with, I test the heck out of it before I trust it. AI is like having a really enthusiastic junior developer who occasionally hallucinates, maybe he is out back smoking weed, and he confidently breaks your database. Use it as a tool, not as gospel. And yes, backups, always backups.
Steven says he is going to go check for forms. I still do not have it yet on my main machine. I have only got it on my beta machine, which is the one I used to make the video. So I am still waiting, Access team. Come on, roll that out. People are waiting for it.
There are a few more people that are waiting on it. I got a few people on my website who said they are still waiting for it. So it is coming. They roll it out to a slow group, a small group first, then they make sure there are no major problems, and then they open up, they increase it from 10 percent to maybe 30 percent. So just give it time. Everyone will get it eventually.
All right, next up we have TJ Kason with a serious question. I have got to put my serious IT face on for this one. All right, let us see. No, I cannot do that. I cannot be serious.
He says they put their Access database on a shared folder, but when the other computer is trying to open it using a network path with an IP address, it will not open, and they cannot add it as a trusted location.
If that IP address is a private address on your local area network, it should be a wired network. Access does not like wireless. Then technically you might be able to get it working, but it is really not the preferred setup for Access or Office trusted locations.
You should use a mapped drive or a proper shared folder name instead of a raw IP address. There are some tricks and workarounds you can do to force it, but I do not recommend going down that road unless you really know networking and you really know what you are doing. Try to use a mapped drive or a UNC path, like backslash, backslash, server, backslash, folder name.
If this is a public IP address, do not try to open up Access directly over the actual internet. Do not do that unless you enjoy database corruption and sadness.
Access works over a wired local network with each user running their own local front end copy and on the same network.
If you want some options for other ways you can run your Access database, here is a video on setting up Windows file sharing so you can set up a shared folder for your database. Here is a video on setting up a trusted location to run that database out of. Here is how you split your database into front and back end so that every user gets their own front end copy and your back end goes in your shared folder. If you are trying to run your database over the internet, watch this video. There are some options, some things not to do. Do not use OneDrive or Google Drive or any of those. There are some options here. SQL Server is a great option. Watch this for more information.
Next up, we have Shell Manacer.
He says Access is useful more as a conceptual stepping stone, kind of like learning assembly language, but not necessarily something that is still very useful in the real world.
I will respectfully disagree with that one. Yes, Access is fantastic for learning database fundamentals because the barrier to entry is low and you can build very useful stuff very quickly. Someone just beamed in. But calling it just a first database ignores the fact that there are companies still running Access systems that I built 30 years ago. I am still in touch with a couple of my clients back in Buffalo where I built databases they are still using. I mean, they have been upgraded a few times since then, but yes.
Now, for small and mid-sized businesses especially, Access is still incredibly useful in the wild. Would I build the backend for Amazon in Access? No, not unless I wanted Jeff Bezos showing up at my house with a flamethrower.
But for inventory systems, scheduling, CRM, reporting, quoting systems, line of business apps, Access is still an absolute workhorse when used properly. So it does make a good first database, but that is not to say that it is only a first database and that there is something better unless you are talking about upgrading your backend to SQL Server, but then you are still using Access.
Next up, Jerome again. He says he would like to learn more about the Windows Task Scheduler. Also wants beginner VBA examples for commonly used commands and techniques.
The Windows Task Scheduler is definitely on my list. It is super useful for automating Access databases. You can use it to launch a database or any application, really, at certain times, run backups, process imports, send emails, all kinds of stuff.
Personally, my quote unquote server Access database runs 24/7 on a loop because it is constantly doing background tasks, like I said, sending email, checking my website, those kinds of things.
For beginner VBA, I do have my intro to VBA video. This one is about 20 minutes long. It gets you started. I mention this at the beginning of most of my other videos. I have a bunch of other videos on VBA topics as they come up, things I have not covered elsewhere.
Honestly, if you want to learn VBA, that is what my developer course is for. It starts from the absolute beginner level and builds you up one piece at a time. I am up to like 59 now, so there is tons of stuff if you want to learn. The TechHelp videos are more quick answers to specific topics, but if you really want to learn VBA properly, you just have to put the time in and work through it level by level. It is all there.
Next up, Wilson says they solved their Access install problem by uninstalling older Office versions first. They had both Access 2010 and 2016 installed, removed the older one, and suddenly everything worked.
I used to see this all the time with Microsoft Office installs, especially when you are mixing old licensed versions like 2010, 2013, 2016. This video initially was talking about 32-bit versus 64-bit components, but you can see the same problem happen sometimes with different actual versions too. Office does not like that situation at all.
My general recommendation is if you are installing a newer standalone Office version, uninstall the old one completely first. Do not trust their upgrade procedure. If you have got Access 2016 and you are installing 2024, uninstall 2016 first, especially if you are hopping versions. If you are still flaring around on 2010 or 2007, remove it completely, then install the new version.
Microsoft 365 is a little bit different because it is designed to update incrementally over time, but those older box-licensed versions can very quickly turn into a tangled mess. Be careful with that stuff.
Next up, Zawilia says they love the tutorials, but wish the full courses were cheaper.
I totally understand that. Honestly, I wish I could charge less too, but this is my full-time job. This is what I do for a living. The Microsoft Access community is a lot smaller than things like Excel or Word. I have to price the courses based on what it actually takes to run my business and keep making content. YouTube ad revenue helps, but trust me, it is nowhere near enough to live on.
The free TechHelp videos that I make like this one, the Quick Queries videos, are basically my way of giving away a ton of useful free material. Hopefully a small percentage of you will decide to sign up and become members or pay for my courses. For the people that really want to master Access, that is what supports me being able to do this and to keep making videos. I have got puppies to feed.
I have to price my lessons at what I need to pay my bills too. I do not call my plumber and have them come over for free to fix my toilet. I give away a lot of free stuff. I do my best to keep my courses priced as low as I can, but still make enough to survive and keep doing this. If I had to go out and get a real job, I would not have any time to make these videos. It is a balancing act, but I am with you. I hear what you are saying.
One thing I like to say is that what I am teaching you is a whole lot cheaper than a college education.
Next up we have Ravenbeak. He says that the image subfolder technique is working on a form, but now they want to use the same pictures in a report and wonder if it should work the same way.
This video is about storing your images for your database records in a subfolder under your database folder so that you do not have to hard code, for example, c colon, backslash, database, backslash, whatever. You just use the CurrentProject.Path and then if you move your database folder, the images just move with it.
Yes, in general, the same idea should work with reports too. If you are building the path with CurrentProject.Path and then adding the subfolder and file name, the technique is not form-specific. Reports can use it as well.
If it works in the form but not in the report, the usual suspects are a misspelled field name, a missing slash on the path, a missing quote somewhere, or code running in the wrong event. Access is very particular. One tiny little typo and it is just going to sit there looking like, I do not know.
Without seeing your database or at least some screenshots, it is impossible for me to tell what you did wrong. But yes, to answer your question, the same technique should work fine with a report as well. So double check everything.
Next up, Gregory says that compact is like defrag. That is not a bad analogy for non-technical users. Compact and repair in Access does reclaim unused space. It reorganizes data pages, rebuilds your indexes, and generally cleans things up internally, kind of like how the old school disk defrag reorganized files on a hard drive to make things more efficient. Puts the different pieces of the files all together in contiguous space.
The big difference is that compact does not just rearrange stuff. It actually creates a brand new database file behind the scenes and copies everything into it cleanly, and that is why it can sometimes fix corruption issues too.
So compact is kind of like defrag for your Access database. It is oversimplified, but it is close enough for government work, and it is close enough for Quick Queries.
Next up, we have two Haasun. He asks about storing pictures and PDF files inside an Access database and what the best workaround for handling those files is. This was posted on my Access Beginner 1 class, so I am assuming you are a beginner.
Just real briefly, I have got a whole separate video that explains this in detail.
In a nutshell, for beginners, technically Access can store pictures and PDF files and other things like Word documents inside the database itself, using things like attachments or OLE objects. Honestly, I do not recommend it for most situations because it bloats the database file very quickly, and Access just is not optimized to be a giant file storage system. A big database is a slow database, and you have got a two gigabyte limit, which, if you are storing images and other stuff inside your database, you are going to reach that pretty quickly.
The better workaround, and what I explain in this video here, is to usually keep the files in a folder somewhere, like I recommend a subfolder under your database folder or just some storage location on your network or wherever you have got it. Then you store the file path or the file name in your table. Then you can display the image in your database or you can open the PDF when you need it with the FollowHyperlink that I mentioned earlier. That is a cleaner, faster, and a whole lot easier to maintain long term.
So I hope that answers your question.
So today we learned that Microsoft Access Security starts with the understanding that hiding tables is not the same thing as protecting your data. If you need really serious security, SQL Server is usually the better backend solution.
We also learned how to open PDFs from forms, troubleshoot weird environment problems on client machines, store pictures and documents properly, and why AI-generated VBA code still needs some human babysitting.
Post a comment down below. Let me know how you like today's video and send me your questions for next week's Quick Queries video.
That is going to do it for your TechHelp Quick Queries video for today, brought to you by AccessLearningZone.com. I hope you learned something.
Live long and prosper, my friends. I will see you next time, and enjoy your weekend.Quiz Q1. What is a major security mistake that many Microsoft Access developers still make?
A. Assuming hiding tables or navigation panes is sufficient to secure data
B. Using SQL Server for storing Access data
C. Disabling macros in Access
D. Running Access only on Windows 11
Q2. What is the recommended method to open a linked PDF from a form in Access?
A. Use the FollowHyperlink command in VBA to open the file path built from relevant fields
B. Embed the PDF directly into the form using a picture control
C. Import the PDF file into an OLE field
D. Use the Access built-in PDF viewer control
Q3. What should be done for true security in a business environment using Access?
A. Move data to SQL Server and use Access as a front end
B. Only disable the shift key and hide the navigation pane
C. Protect all forms with a password
D. Store all data in local text files only
Q4. When dealing with AI-generated VBA code, what is the best practice?
A. Double check everything and keep backups before using it
B. Trust the AI output without further review
C. Only use AI for tasks you do not know how to do
D. Never use AI for any kind of VBA coding
Q5. What is the risk of trying to open an Access database across a public IP address?
A. High chance of corruption and data loss
B. It will run faster than on a local network
C. No risks, Access supports this fully
D. Only affects reports and forms, not tables
Q6. What is the preferred way to set up shared folder access for an Access database?
A. Use a mapped drive or UNC path instead of a raw IP address
B. Always use a wireless network for faster setup
C. Store the database on OneDrive or Google Drive
D. Share the database folder via FTP
Q7. Why is Access still considered useful in the real world?
A. It is great for small and mid-sized businesses to build power apps quickly
B. It is only for learning and not practical for any business purpose
C. It can handle massive enterprise backends like Amazon's
D. It is best only for home users and not businesses
Q8. What is the primary problem with storing picture and PDF files inside an Access database?
A. The database will quickly bloat and hit size limits, leading to slowdowns
B. Access encrypts all files, making them irretrievable
C. PDF files cannot be stored in Access at all
D. It provides more security and faster performance
Q9. What is a better alternative to storing documents inside the Access file?
A. Store documents in folders and keep only the file path in the database
B. Always use attachment fields for images and PDFs
C. Compress all files before importing them into Access
D. Upload them to the cloud and reference their download links
Q10. What does the Compact and Repair feature in Access do?
A. Rebuilds the database file by creating a new one, reclaiming space and fixing corruption
B. Only defragments the database indexes
C. Just renames the database file to a new name
D. Deletes all temporary tables and queries from Access
Q11. Why might someone be unable to add a trusted location when opening an Access file via a network IP address?
A. Because Access and Office prefer mapped drives or UNC paths, not raw IP addresses
B. Because Access supports only local folders for trusted locations
C. Because the database is too large
D. Because wireless networks disable trusted location functionality
Q12. If images display on a form using CurrentProject.Path-based folder references but not on a report, what is the likely cause?
A. Typo in code, missing slash, quote, or wrong event usage
B. Reports do not support images from paths
C. Access cannot use CurrentProject.Path in reports
D. Only jpg files work on forms, not reports
Q13. What is the benefit of hiding the navigation pane, disabling shift, and locking down startup options in Access?
A. Makes it harder for casual users to bypass the user interface
B. It guarantees complete database security
C. It prevents corruption errors
D. It makes Access run faster
Q14. What is the main reason older and newer versions of Office installed together can cause problems with Access?
A. Different Office versions can conflict, causing install and runtime errors
B. Only 32-bit Office can run Access databases
C. Windows does not allow more than one Office install
D. 2010 databases are not compatible with any newer version
Q15. What is a good analogy for Compact and Repair in Access for explaining to non-technical users?
A. It is similar to defragmenting a hard drive
B. It is the same as formatting your computer
C. It is like making a backup only
D. It turns your database into a zip file
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-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 In today's Quick Queries video from Access Learning Zone, I will discuss some very common Microsoft Access security mistakes that developers still make without realizing it, along with a variety of questions that came in from the community. These Quick Queries are a roundup of questions posted across my website, YouTube, Reddit, email, and anywhere else people reach me. I gather those that do not need an entire deep-dive session and answer them here, often in rapid-fire fashion. Today we actually have a mix of topics, including working with PDFs in Access, securing your databases, using network paths, troubleshooting Office installs, working with images, and even touching on AI in VBA and automation with Windows Task Scheduler.
I want to begin by pointing out a frequent issue about Access security. People often think that setting up protection in Access is as simple as hiding objects like tables or navigation panes, or disabling certain keys and ribbons. While these methods do help prevent casual snooping, they are not real security. If your data is stored inside the Access file itself and someone understands what they are doing, they could still get to your tables. For truly secure databases, especially in a business environment, I always recommend moving your backend data to SQL Server. Access should really serve as your front end, linked to a more secure backend when security is a real concern.
On the topic of working with files, there was a question about opening a linked PDF from a form. You can absolutely do this. The approach I recommend is building the full file path dynamically in VBA, using fields like date, supplier, or ID. Then you can use the FollowHyperlink command, which tells Windows to open the file in whatever PDF viewer is set as the default. This method works cleanly and efficiently, letting your Access records be tightly linked to external documents.
Another common issue involves users still being able to access the navigation pane and maximize windows, even after you have taken steps to lock down the Access interface. There are various techniques to make your database more secure, including hiding navigation, disabling keys, setting up ACCDE files, and more. I have a number of resources and videos that go into these subjects, including a full Security Seminar. But again, true security requires keeping your data out of the Access file and in a more robust database platform like SQL Server.
We also discussed using AI-generated code. While AI can be a huge help, especially for repetitive or boilerplate code where you already know what you want, it is prone to errors and oversights if you are not vigilant. Always check the code, run thorough tests, and maintain frequent backups. Think of AI as a very eager junior developer: helpful but not to be fully trusted on its own.
Another question dealt with running Access databases over a network using IP addresses. This is generally not supported or recommended. Access is designed to run best over a wired local network using mapped drives or UNC paths, not raw IP addresses. Relying on wireless connections or trying to run via public internet addresses introduces serious risks of corruption and data loss. Each user should have their own front end, running on the same local network, and the shared backend should be accessed using properly configured paths.
When it comes to the real-world use of Access, some have described the program as just a stepping stone for learning, or something that is now obsolete. I disagree. While Access is indeed great as a learning environment for databases, there are thousands of companies still running their businesses on Access applications built years ago. For small and mid-sized businesses, Access is a reliable, efficient solution if it is used properly. You would not run something the scale of Amazon with it, but for custom apps, inventory, CRM, or reporting, Access remains very useful in the field.
Questions also came in about automating tasks with Windows Task Scheduler. Task Scheduler is a powerful way to run Access databases and other applications at set times for things like backups or imports. On automation and coding, if you want to start learning VBA, I have an introductory video plus a full developer course that builds your skills step by step, starting from the very basics and gradually moving to more complex projects.
Another user shared an Office install issue, resolved by removing older versions of Office before installing a new one. This is something I have seen many times. Mixing versions of Office, especially older standalones, can cause all kinds of headaches. It is always best to fully uninstall an old version before putting on a new one, particularly if you are jumping several versions ahead.
Cost of courses was brought up as well. While I do wish I could make all courses cheaper, the Access community is smaller and my courses have to support the time I spend producing them. I provide plenty of free content, but the paid courses are what keep the lights on. Remember that what you learn here is still much more affordable than a typical college course.
A question came up about using image subfolders in reports after doing so successfully with forms. The general technique of storing images in a subfolder relative to your database works just as well in reports as it does with forms. If you run into trouble, check for small issues like missing slashes, typos in field names, or code connected to the wrong event. Access can be picky about these details.
On the subject of Access maintenance, another user compared compact and repair to disk defragmenting. That is actually a pretty good analogy for laymen. Compact and repair not only reclaims unused space, but it also rebuilds your data and indexes, effectively cleaning up your database and sometimes even helping to correct corruption.
One more question asked about storing images and PDF files directly inside your Access database. While it is possible to do this using attachments or OLE objects, I do not recommend it for most situations. Storing large files inside the database will quickly bloat the file, hit the two gig limit, and generally slow things down. The better solution is to keep the files in an external folder and store only the file path or file name in your table. That way, you can easily display images or open PDFs as needed without slowing down your database or risking corruption.
In summary, today we covered key lessons about Access security, best practices for linking external documents, why you should be careful with database paths and Office installs, how to use Task Scheduler and VBA, and smarter ways to handle images and document storage. We also discussed the reliability and continuing value of Access for small businesses, and got into some of the practicalities of working with newer and older Office versions.
For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Opening linked PDFs from a button on a form
Building PDF file paths dynamically in VBA
Using FollowHyperlink to view external PDFs
Hiding the navigation pane and ribbon in Access
Disabling special keys and locking down startup options
Limitations of Access security features
Why SQL Server is preferred for real security
Best practices for Access over a network
Using mapped drives and UNC paths in Access
Splitting Access databases into front end and back end
Troubleshooting Access not opening over network paths
Storing images and PDFs outside the Access database
Referencing subfolders with CurrentProject.Path
Displaying images in forms and reports from subfolders
Using Compact and Repair to reorganize Access data
Handling Office version conflicts during installation
When to uninstall older Office versions
Access as a tool for small to medium business use
Automating Access with Windows Task Scheduler
VBA beginner resources for Access
Storing file paths instead of files in Access
Access database two gigabyte file size limitationArticle Are you making a big Microsoft Access security mistake without even realizing it? Many developers new to Access, and even experienced ones, sometimes overlook the limitations and proper techniques for keeping their database secure. In this article, I will walk you through what Access can and cannot do regarding security, as well as offer some practical solutions and tips for handling related challenges like opening PDF files from a form, using network locations, and storing external files the right way.
Let us start with one of the most common Access security mistakes. Many developers try to hide tables, disable the shift bypass key, hide the navigation pane, or lock down startup options to prevent users from poking around. While these steps can help keep casual users from accidentally editing something they should not, all of these measures only offer what I like to call "honest user" security. If your data lives inside an Access file and a determined user has basic Access skills, they can almost always find a way around your security settings. There is no bulletproof method for securing data in an Access file if someone really wants to get to it.
Because of this, real security in a business environment means moving your data out of the Access file and into a more robust system like SQL Server. You can still use Access as your front end, but having your data in SQL Server lets you control security at the database server level. You can assign user permissions, implement true authentication, and stay much safer from tampering or data theft. In summary, Access security features are mostly there to keep honest users honest, but not to stop a determined hacker. Think of hiding tables and disabling menus as locking the front door, but remember that serious data protection comes from using a separate database server.
Let us look at another practical scenario. Suppose you have a button on an Access form and you want to open a PDF file associated with the current record. The file path for this PDF might be constructed from fields on your form, such as a date, supplier name, and an ID number. You want Access to locate and open the right PDF file whenever the button is clicked.
You can achieve this easily in VBA. I recommend storing your PDFs outside the database, in a structured folder system. For example, maybe you organize your PDFs into folders by year and month, and the file name is something like SupplierName_ID.pdf. In your VBA code, you would build the complete path to the file using the relevant fields from your form. Here is how you might do it:
Dim filePath As String
filePath = "C:\PDFs\" & Year(Me.InvoiceDate) & "\" & Month(Me.InvoiceDate) & "\" & Me.SupplierName & "_" & Me.ID & ".pdf"
Application.FollowHyperlink filePath
In this code, replace the field names with those from your specific situation. The Application.FollowHyperlink method will open the PDF file in your system's default PDF viewer.
It is important not to use an image or picture control for files like PDFs since they are not images. Always use the approach above for non-image external files.
Another question I am often asked relates to network setups for Access databases. Sometimes people try to put their database in a shared folder and have users open it through a network path, sometimes using the server's IP address. Usually, Access works best with mapped drives or proper server names, such as \\server\sharedfolder\mydatabase.accdb. Using an IP address in the path can create problems, especially with trusted locations in Access. Always use UNC paths or mapped drives, and be sure your network is wired, not wireless. Access does not work well over WiFi and is not designed to have the file accessed over the public internet. If you try to open an Access file remotely over the internet, you are almost guaranteed to corrupt your database.
For multi-user environments, each user should have their own front-end copy of the database on their computer, with the shared back-end file stored in a network shared folder. This split design dramatically improves reliability and performance.
If you need to automate certain tasks in Access, such as running a report or importing data at specific times, you can use the Windows Task Scheduler to launch Access and run macros or VBA code automatically. For more advanced automation, you might have a dedicated Access instance running on a desktop or server, continuously performing background tasks like sending emails or importing data. If you are just getting started with programming in Access, I recommend taking the time to learn VBA step by step. Start with basic event programming, then move on to more advanced database manipulation and automation as you get comfortable.
When working with files like images or PDFs, a common beginner mistake is to store these files inside the Access database using attachments or OLE objects. While this is technically supported, it quickly leads to unmanageably large database files, slow performance, and a greater risk of corruption. Access files have a 2GB file size limit, which you will hit quickly if you are storing images or large documents internally. Instead, keep these files stored separately in a folder, and save only the file path or file name in your tables. When you need to display an image or open a document, use CurrentProject.Path to reference the folder dynamically, so moving the database also moves its related files easily.
For example, you might have an Images subfolder in the same location as your database. You store file names in your table and build the full path with:
Me.ImageControl.Picture = CurrentProject.Path & "\Images\" & Me.ImageFileName
This approach works in both forms and reports. If you see issues with images not displaying in reports, double-check the field names, path construction, and event code. Usually, it is a small typo or missing slash.
A quick note on maintaining your Access database: regular use of the Compact and Repair feature is a good habit. Compacting an Access database is a bit like defragmenting a disk. Access creates a new database file during compact, copies your objects and data into it freshly, and discards unused space, rebuilding indexes and helping avoid corruption. Besides efficiency, this process can sometimes fix small corruptions that happen over time, especially if your database is used heavily.
Sometimes people run into issues when upgrading to a newer version of Office or Access but leave older versions installed. You are much better off uninstalling all previous versions before installing a new one. Mixing different versions, or mixing 32-bit and 64-bit installs, is a recipe for trouble. Clean up old installations first, then install the latest version cleanly, and you will avoid many headaches.
Finally, let us circle back to Access's place in the modern world. Some may claim that Access is just a stepping stone to more advanced databases, or is only useful for learning. This simply is not the case. Thousands of businesses continue to rely on Access for their daily operations, especially in small and mid-sized companies. While it is not suitable for giant enterprise systems, Access remains a workhorse for line-of-business apps, reporting, CRM, scheduling, and much more. With proper design and prudent use of technologies like SQL Server for the back end, Access can powerfully serve your business for years.
To sum up, Microsoft Access security depends on understanding its real capabilities. Hiding tables and disabling menus might fool casual users, but serious security requires moving your data to SQL Server and using Access as the front end. Keep your external files in folders, not inside the database. Build dynamic file paths in VBA for opening documents or displaying images. Use mapped drives and local networks, not WiFi or the internet, for sharing databases. Automate with Task Scheduler as needed. Regularly compact your database. Uninstall old Office versions when upgrading. And remember, Access remains a practical and powerful tool in the right hands. If you have questions about using Access more effectively or securely, keep learning and do not hesitate to ask. |
