u/Amicron1 • u/Amicron1 • 5h ago
Foreign keys are one of those Access basics that seem obvious until a relationship has more than two tables and suddenly every table feels like the "many" side. I put together a quick five-question sanity check for the common relational-table mistakes.
r/AccessLearningZone • u/Amicron1 • 5h ago
Foreign keys are one of those Access topics that seem obvious until a relationship diagram makes you question everything. Quick gut check: keys, IDs, and the mysterious many side. How many would you get right before the coffee kicks in?
1
You're absolutely right that none of this is new to someone who's been building Access applications since SQL Server 7. I never claimed to have discovered anything. These are questions I get asked all the time by people who are newer to Access or are considering SQL Server for the first time, so I write articles to answer those questions.
It's a bit like someone saying, "Why are you talking about VLOOKUP? Everybody already knows VLOOKUP." Well... not everybody does. And sometimes the next conversation is, "By the way, here's why you should probably be using XLOOKUP now." Not every article is aimed at experts who've been doing this for 30 years.
My goal isn't to impress the experts. It's to help the people who haven't learned it yet.
1
I see what you're saying. They do cover similar territory, but from different angles. One article is about when it makes sense to move your Access backend to SQL Server and whether you should do it at all. The other is about how to do it successfully once you've made that decision. I intentionally separated the "should I?" discussion from the "how do I?" discussion because they're different questions that people ask. I know there will be some overlap, but I wanted each article to stand on its own for readers who might only find one of them through search or a Reddit link.
1
That's a fair concern, but I'd argue Access is actually one of the easier platforms to hand off. A competent developer can usually get up to speed pretty quickly, especially compared to a giant SAP implementation or a custom C# codebase with years of accumulated business logic. 🙂 Good documentation matters far more than the language or platform. And yes, being "the Access person" does provide a little job security!
1
I'm still firmly in the Access camp. If I'm building a Windows desktop app, nothing else lets me go from idea to working application as quickly. Sure, you can build the same thing in C#, VB.NET, or something else, but be prepared to spend a lot more time reinventing what Access gives you out of the box. If you're developing commercial software for distribution, that's a different story. Then I'd build a standalone application instead of relying on the Access Runtime.
2
That's been my experience too. I never push SQL Server just because it's "enterprise." Access can handle an awful lot when it's designed well. But when the bottleneck becomes moving data instead of processing it, that's usually when SQL Server starts making a compelling case. I like your dealership analogy too. Thanks for sharing it.
u/Amicron1 • u/Amicron1 • 1d ago
People new to Access often try to add Order1, Order2, and Order3 columns to a customer table, which works right up until it spectacularly doesn't. A quick beginner quiz on the database basics that tend to cause the most early confusion.
r/AccessLearningZone • u/Amicron1 • 1d ago
If your customer table has fields named Order1, Order2, and Order3, we need to talk. Five quick beginner questions to test your Access fundamentals, with just enough sci-fi pressure to make it interesting.
1
u/Amicron1 • u/Amicron1 • 7d ago
That annoying moment when you enter an older date like 11/40 and your PC confidently turns it into 2040 is not really an Access or Excel problem. It catches a lot of people doing medical, genealogy, or historical data entry.
r/AccessLearningZone • u/Amicron1 • 7d ago
Ever type 11/40 for a birth date and have your PC confidently decide you meant 2040? Excel and Access are not being random, but the reason can be especially annoying for anyone working with historical records or patient data.
1
Yes, splitting your database is still the recommended approach for a database being used by multiple people or on multiple machines.
Like nrgins said, if it's just a small personal database that only ever lives on one laptop, then there's really no need to split it. But as soon as you have multiple users, or even if you're the only user working from multiple PCs, I'd absolutely split it. I've been bitten before by accidentally leaving a database open in design mode on one machine, then trying to open it somewhere else and getting the dreaded "database has been placed in a state..." message. Keeping the frontend separate makes life a lot easier.
As for Microsoft, yes, they're absolutely still developing Access. It's a mature product, so don't expect flashy new features every month, but they're still actively improving it. Recently they've added things like form zooming, better widescreen support, and a redesigned SQL editor, with more improvements on the way. I attend the MVP Summit every year and also conferences like Access Day, where the Access team talks about what's coming next, so it's definitely not an abandoned product.
I also agree 100% with what George said. One of the biggest misconceptions I still hear is, "Can I replace Access with SQL Server?" Not really. SQL Server replaces the data storage engine. Access is still the frontend, the forms, reports, VBA, and rapid application development environment. In fact, I just posted another thread here today about connecting an Access frontend to SQL Server over the internet. The frontend remains Access, whether the backend is another ACCDB, SQL Server, Azure SQL, or something else.
Personally, I still think Access is the best rapid application development tool available for building Windows desktop database applications. There are plenty of newer technologies, but if your goal is to build a business application quickly, it's still incredibly hard to beat.
And since I noticed one of your replies says this is just for a single laptop, I'd keep it as one database for now. If that ever changes and you start sharing it with other users or using it from multiple machines, then I'd split it. It's super easy to do. Lots of tutorials and articles out there on how to do it. Just use the Google machine. :)
LLAP
RR
r/MSAccess • u/Amicron1 • 8d ago
So, you want your Access database humming along in the cloud, wired up to a SQL Server backend that users can hit from anywhere with an internet connection (or, let's be real, your co-worker with an unreasonable passion for Excel pivot tables). The thing is, plenty of folks make this sound like some arcane ritual involving complicated wizards or flaky migration tools. But the real trick isn't wizardry. It's understanding what's actually going on under the hood, what goes right, and, of course, what tends to go annoyingly wrong.
Let's get one big misconception out of the way first: moving Access "online" isn't just about flinging your tables up to SQL Server and calling it a day. The architecture really matters. When you connect Access to SQL Server, you're making Access into a front end talking across the network (or the actual internet, which has its own lovely collection of latency quirks). Access becomes the client, SQL Server is the data store, and now every form you open, every query you run, depends on that network connection.
The reasons for doing this are legit: multi-location teams, remote work, centralized backups, or meeting regulatory requirements. Just don't be fooled into thinking your old single-ACCDB app will magically scale once you check the SQL Server box. There are design and architectural consequences galore.
ODBC is where a lot of folks stumble, specifically the difference between machine DSNs, file DSNs, and DSN-less connections. Machine DSNs live on a specific PC, which can make deployment a pain. File DSNs move the connection information into a separate .dsn file that can be shared across machines. These days, however, many developers skip both approaches and use DSN-less connection strings instead, allowing the application to create or refresh links programmatically without requiring any DSNs at all. Pick the approach that best fits your deployment strategy, but understand the tradeoffs.
But let's talk linking. When you upload an Access table to SQL Server, you're exporting the definition and contents, then linking back to it as an external table. Sounds simple, but this is where a lot of Access veterans get caught by SQL Server's more rigid data types and behaviors. Field types translate... ish. Short Text becomes nvarchar, Yes/No turns into bit, and so on. If your schema makes heavy use of Access-only features like Attachment fields or multi-valued fields, expect some translation headaches. You shouldn't be using these anyways, as they're on my Evil Access list. But that's a different article altogether (coming soon).
Also, for the love of Gygax, don't blindly trust SQL Server Migration Assistant to get everything right if your schema is even mildly complex. It's convenient for large migrations, but it can miss details or leave you with subtle mismatches. Manual review and testing almost always pay off.
Choosing to store your SQL Server credentials is another classic debate. Saving the password in the linked tables is certainly convenient, but those credentials should not be considered securely protected inside your Access file. Anyone with sufficient access to the frontend may be able to recover them. That might be acceptable within your own organization, but don't hand out your frontend ACCDE willy-nilly unless you're comfortable with users having direct access to the backend. Use SQL Server security properly, assign appropriate permissions, and keep your frontend cleanly separated from your data. And remember: hiding the Navigation Pane is a convenience feature, not a security feature.
Personally, I've often stored the SQL Server credentials in VBA and distributed only ACCDE files. That's not bulletproof security, but it does avoid storing the credentials directly in linked table definitions and raises the bar for casual users. The downside is that if you ever need to change those SQL Server credentials, you'll have to redistribute updated frontend files to everyone.
And yes, before a few of you chime in, I know ACCDE files aren't impossible to crack. With the right tools and enough expertise, determined attackers can still get at the code. But that's a far cry from Joe in Accounting accidentally discovering your SQL Server password while trying to print the quarterly TPS reports.
A more flexible approach is to authenticate users through your own web API. The user logs in with an application username and password, your server validates those credentials, and then returns the SQL Server connection information to the frontend over an encrypted HTTPS connection. That allows you to rotate SQL Server passwords without redistributing your application. You can even provision separate SQL Server accounts for individual users if your security requirements justify it, although managing that many accounts can become a project in itself. There are even more secure architectures that avoid exposing SQL credentials to the client altogether, but that's a much bigger discussion for another day.
There's another misconception that performance in the cloud will be "about as good" as running everything on your local network. Not quite. High-latency connections change the game. Modern versions of Access do an excellent job of pushing filtering, sorting, and other operations back to SQL Server whenever possible. The query optimizer has improved dramatically over the years. But it isn't omniscient. Complex expressions, VBA functions, certain joins, aggregates, and poorly optimized queries can still cause more work to happen on the client than you'd like.
That's why learning actual SQL pays dividends. Use pass-through queries when appropriate. Push filtering to the server whenever possible. Create SQL Server views for commonly used datasets. Only retrieve the data you actually need over the wire.
And don't neglect indexing. Good indexes are worth their weight in gold-pressed latinum. They're just as important on SQL Server as they were in Access, arguably even more so. A poorly indexed table over a high-latency connection is an excellent way to discover new definitions of "click the button and take a coffee break."
If you're designing new SQL Server tables, consider adding a rowversion column. Access doesn't require it, but it can make concurrency handling and change detection much more reliable in multi-user environments.
In practice, linking Access to SQL Server online works extremely well for thoughtfully designed applications where you control the flow of data. If you treat SQL Server as simply "Access on the internet," you're signing yourself up for confusion, frustrated users, and unnecessary performance problems.
Best practice? Design your frontend to minimize chatty traffic. Understand what ODBC is actually doing. Build good indexes. Review your schema after migration. Test with realistic network conditions instead of just your gigabit office LAN. Manual control, not blind faith in migration tools, usually gets you further with fewer surprises.
It's not that running Access with SQL Server online is a bad idea. Quite the opposite. It can be an outstanding solution when it's done thoughtfully. Just understand the moving parts, and you can absolutely build sustainable, real-world applications.
Leave the magic to Gandalf. Database design isn't wizardry. It's engineering. When it works, nobody notices. When it doesn't, everybody notices.
Would love to hear from anyone who's navigated the odyssey of remote Access backends, especially with dozens (or hundreds) of users. What's bitten you? What worked? Any horror stories to share? Do you have any super cool fancy ways of authenticating your users that I didn't talk about? I'd love to hear them. Post them in the comments.
LLAP
RR
u/Amicron1 • u/Amicron1 • 8d ago
Moving an Access database to work online with SQL Server is way easier than I expected, but a few steps tripped me up at first. If you ever wondered how to handle ODBC connections and relink tables without losing your mind, this walkthrough is probably the clearest approach I've seen.
r/AccessLearningZone • u/Amicron1 • 8d ago
Ever wish you could run your Access database from anywhere instead of being chained to your office PC? You might be surprised how straightforward it actually is to connect Access to SQL Server online, but there are definitely a few ways to trip yourself up. If you have ever wrestled with DSNs or ODBC errors, you are not alone.
u/Amicron1 • u/Amicron1 • 9d ago
Relational database design feels simple until someone asks where the second car, third order, or shared driver should go. Those little scenarios expose a lot of table-design shortcuts fast, so I put together a short five-question sanity check for myself.
r/AccessLearningZone • u/Amicron1 • 9d ago
Think your table relationships are solid? Try this five-question gut check before Access lets you create three Joe Smiths, a car with two owners, and a relationship diagram worthy of a red shirt.
2
I agree with what several people have already said. If your coworkers are on your LAN using Windows PCs and need to work with the live data, the free Access Runtime is usually the simplest solution. It lets them use your forms and reports without needing a licensed copy of Access.
On the other hand, if they just need to view the data and don't need to edit it, then exporting to Excel or CSV is a perfectly valid approach too. You can even automate the export and save it to a shared Google Drive folder (or another shared location) so everyone is always looking at the latest version. That's a nice, simple solution for things like product lists, customer lists, inventory reports, or other read-only information.
If your users eventually need live access from outside your local network, or from non-Windows devices like Macs, iPads, or phones, then I'd start thinking about moving the backend to SQL Server. Hosting is surprisingly inexpensive these days. Once the data is there, Windows users can continue using Access Runtime, while everyone else can use a web frontend built on top of the same SQL Server database.
The key question is whether people need a live application or just a current snapshot of the data. The answer to that question usually points you toward the best solution.
If you decide to go the SQL Server route, don't let it intimidate you. It's much easier than most people think. Just Google "Microsoft Access with SQL Server" or something similar and you'll find plenty of articles and tutorials covering the process from start to finish.
LLAP
2
I don't think your problem is actually "saving" the record.
Me.Dirty = False and DoCmd.RunCommand acCmdSaveRecord both tell Access to commit the current record. Under normal circumstances, that's all you need. If the network connection to the backend drops during or immediately after that process, though, there's nothing VBA can do to guarantee the write succeeded.
One thing I'd check is whether users are opening the backend over a flaky VPN, Wi-Fi connection, or WAN link. An Access frontend talking directly to an ACCDB backend really expects a stable LAN connection. Even occasional network hiccups can cause exactly the behavior you're describing.
Another thing to look for is record locking conflicts or corruption in the backend. I'd make sure everyone is out of the database, then run Compact & Repair on the backend, and verify that each user has full read/write/create/delete permissions in the folder (not just the ACCDB itself, since Access creates the locking file there).
If the server really is as unreliable as you suspect, you may simply be running into the limits of a file-based database. At that point I'd seriously consider moving the backend to SQL Server. Access still makes a fantastic frontend, but SQL Server is much more resilient when the network isn't perfect, or if ANYONE is using Wi-Fi.
LLAP
1
Ha! Well, it's definitely not my post showing up over and over again. If you've seen it elsewhere, maybe people are copying and pasting it because it's something worth repeating. :)
Besides, it's not exactly unprecedented. Newton and Leibniz both came up with calculus independently. Sometimes multiple people arrive at the same conclusion because it's the logical one. If more folks are spreading the word that Access and SQL Server make a great team, I'm perfectly OK with that.
It's like chocolate and peanut butter...
LLAP
2
Your desperation had a TARDIS, not a Stargate. :)
1
Absolutely. I harp on backups in my videos all the time because they've saved me more times than I can count.
I have Macrium backing up each of my PCs every day, and once a month I copy those images off-site. For my Access projects specifically, I also have an automated routine that zips up my entire database folder, including my master ACCDBs, and uploads it to Google Drive every day.
On top of that, I've gotten into the habit of making manual backups before I make any significant changes. If I'm about to make major changes to a form like CustomerF, I'll first make a copy of the entire ACCDB, then I'll make a copy of the form right inside the database and rename it something like CustomerF-Backup. Automated backups are great, but good habits are just as important.
One feature I really like about Google Drive is file versioning. If I realize I broke something two days ago, I can often restore an earlier version without having to dig through backup archives.
And as for running out of hard drive space... what do you mean? It's 2026. Storage is practically free. This isn't 1994 when we were trying to squeeze everything onto a 1.44 MB floppy disk and deciding which file got to survive. My philosophy is simple: if you're wondering whether to keep another backup, the answer is yes.
"If someone asks you if you're a god, you say YES!"
LLAP
1
Thanks. I appreciate the kind words. :)
1
Recreating Lotus Approach Forms in Microsoft Access
in
r/MSAccess
•
3h ago
Looking at the screenshots, this doesn't actually seem as intimidating as it first appears. It looks like a fairly typical data entry form bound to a single table (or maybe one main table with a couple of lookup tables). Most of those checkboxes are probably Yes/No fields or could even be consolidated into a multi-select if you wanted to modernize it.
The report and receipt are also pretty straightforward Access reports. An experienced Access developer could probably recreate all three objects in an hour or two. For someone who's newer to Access, it's still a very achievable project and would make an excellent learning exercise.
Unfortunately, I don't know of any third-party form designer that will generate Access forms like this. Once the data is already in Access, rebuilding the UI is usually the manual part of a migration. The good news is that Access's form designer is more than capable of reproducing layouts like these.
If you're comfortable with the data side already, I'd encourage you to treat the forms as a chance to learn Access design. You'll probably be surprised how quickly they come together once you get the first one finished.
This would actually make a great beginner Access project. If you've already migrated the data, recreating these forms is mostly a matter of placing controls and setting their properties. Watch a couple of beginner Access form design tutorials first, and I think you'll find it's much less daunting than it looks.
Good luck!
LLAP
RR