Computer tutorials, tips, and tricks to help you learn Microsoft Excel, Access, Word, and lots more. Visit our full site at www.ComputerLearningZone.com.
Ever click a button in Microsoft Access and get smacked with a runtime error because one form tried to talk to another form that was not open? Yeah. Fun times. This is one of those little annoyances that does not happen often, but when it does, it interrupts your workflow and makes your database feel fragile. Today we are going to fix that and make your code a lot more forgiving.
When one form references another in Access, everything works great as long as the target form is open and ready. But the moment it is closed, or not in the right state, Access throws an error. A common example is refreshing data on another form. If that form is not available, boom, runtime error. So the first step is learning how to safely check whether a form is open before you try to do anything with it.
The classic solution is a small global function that checks the CurrentProject.AllForms collection to see whether a form is loaded. Something like this:
Public Function IsLoaded(FormName As String) As Boolean
IsLoaded = CurrentProject.AllForms(FormName).IsLoaded End Function
You drop that into a standard module so it is available everywhere. Then you wrap your form references in a simple check. For example, before refreshing another form, you might do this:
If IsLoaded("CustomerF") Then
Forms!CustomerF.Refresh End If
That alone prevents a lot of errors. If the form is not open, your code simply skips the refresh. Nice and clean.
But here is where developers, especially during testing, run into a wrinkle. The IsLoaded function only tells you whether the form is open. It does not tell you how it is open. A form sitting in Design View still counts as loaded. Try refreshing it in that state and Access will still throw an error.
This usually does not affect end users because they should never be in Design View. But developers live there. You are opening objects, closing them, tweaking layouts, testing buttons. That is when these little runtime errors start popping up and slowing you down.
The quick and easy fix is simple error suppression. Right before the line that might fail, add:
On Error Resume Next
This tells Access, "If this line blows up, just move along." So your code becomes:
If IsLoaded("CustomerF") Then
On Error Resume Next
Forms!CustomerF.Refresh
On Error GoTo 0 End If
Now if the form is open in Design View, Layout View, or any other unusable state, Access quietly ignores the refresh attempt. No runtime error. No interruption. Life goes on.
This makes development much smoother. You can click buttons, test features, and not worry about whether every related object is perfectly staged.
However, and this is important, error suppression is not something you want to sprinkle everywhere like parmesan cheese. It has a place, but overuse can hide real problems. If something else breaks in that same block of code, you might never know.
I like to compare excessive use of On Error Resume Next to pulling the batteries out of your smoke detector because it chirps once in a while. Sure, it is quiet now, but you also removed the thing that warns you when there is an actual fire.
The best practice is to use it very selectively. Wrap only the specific line that you know might fail, then immediately restore normal error handling with On Error GoTo 0. That way you get the benefit of smoother execution without masking legitimate bugs.
If you want to go further, the more advanced approach is to check not just whether a form is loaded, but what view it is in. Form View, Design View, Datasheet View, Layout View. That takes a bit more code, but it gives you precise control. For most situations though, selective error suppression does the job just fine.
So the takeaway is simple. Always check whether a form is loaded before referencing it, and when development scenarios introduce edge cases, use targeted error handling to keep your database running smoothly.
If you want to see the full walkthrough, including live demos and expanded examples, watch the embedded video above.
If you’ve spent years working inside Microsoft Access, opening SQL Server Management Studio for the first time can feel like walking onto the bridge of a starship. Buttons everywhere. Folders everywhere. Strange terminology. And about a thousand things you’re afraid to click because you don’t know what might explode. So let’s fix that. Today we’re getting oriented inside SSMS so you know what matters, what doesn’t, and where you should actually be working.
When you first connect to your SQL Server instance, the main window you’re looking at is called Object Explorer. Think of this as the SQL Server equivalent of the Access Navigation Pane. This is your map. It shows you the server, the instance you’re connected to, and all the major containers that hold your databases and server-level objects. If you understand this layout, everything else gets much easier.
At the very top is the server itself. That’s the entire SQL Server environment. Under that is the instance. Many Access users aren’t used to this distinction because Access is file-based. You open an ACCDB file and you’re in. SQL Server is service-based. The instance is the running engine that manages databases, security, storage, and performance.
Under the instance you’ll see a whole tree of folders. This is where new users start to panic. Databases, Security, Server Objects, Replication, Management, and more. Here’s the good news: you can safely ignore most of this when you’re starting out.
The folder you’ll live in is Databases. That’s where your actual data lives. Tables, views, stored queries, relationships, all the stuff you’re used to working with in Access. Everything else in Object Explorer is mostly administrative or enterprise-level tooling.
For example, Security controls logins and permissions. Important later, but not something you need on day one. Server Objects handles linked servers and system-wide features. Replication is for syncing data across multiple servers. Sounds cool, but SQL Server Express can only subscribe to replication, not publish it, so most beginners won’t touch it.
You’ll also see things like Management tools and monitoring utilities. These are powerful, but they’re not where you build databases. Think of them like the backstage maintenance rooms. Useful, but not where the show happens.
Once you expand the Databases folder, you’ll notice system databases already there. These run SQL Server itself. Leave them alone. Don’t create tables in them. Don’t experiment. They’re like Access system tables. Important, but not your workspace.
So let’s create our own database. Right click Databases and choose New Database. Give it a name and you’ll see SQL Server automatically prepares two files. One stores the actual data. The other is the transaction log.
This is one of the biggest structural differences from Access. Access stores everything in one file. SQL Server separates data and logging so it can recover cleanly from crashes and maintain transactional integrity. It’s one of the reasons SQL Server is far more robust in multi-user environments.
You’ll also see file size and autogrowth settings. Access users often worry about database bloat and compacting. SQL Server works differently. It automatically grows as needed and reuses internal space. There’s no routine compact and repair cycle like Access. In fact, shrinking databases regularly is usually discouraged.
The default size and growth settings are fine for most projects. SQL Server is very good at managing its own storage. Beginners almost never need to change these.
You’ll also notice the file path where the database lives. By default this is inside SQL Server’s data directory. Leave it there unless you have a specific reason to move it. Do not store SQL Server databases on external USB drives or inside cloud sync folders like OneDrive or Dropbox. Those environments can cause locking issues, sync conflicts, and even corruption.
If you ever need to move database files, do it through SQL Server tools, not Windows Explorer. You can’t just drag the files around like an Access ACCDB.
Once your database is created, expand it and you’ll see another tree of objects. This is where things start looking more familiar. Tables hold your data. Views are saved queries that live on the server. Database Diagrams let you visualize relationships much like the Access Relationships window.
There’s also a folder called Programmability. This is where server-side code lives. Stored procedures, functions, triggers. Think of these like VBA modules that run directly on the server instead of inside your Access front end.
Triggers in particular are powerful. They fire automatically when data changes. Insert a record, update a field, delete something, the trigger runs. Unlike Access form events, they execute regardless of where the change originated. Access form, import job, external app, doesn’t matter. This makes them extremely useful for enforcing rules and auditing activity.
You’ll also see advanced features like Query Store, Service Broker, and storage architecture tools. These are developer and enterprise-level topics. Important down the road, but not required for getting started.
The key takeaway is simple. Don’t let the interface intimidate you. SQL Server exposes far more infrastructure than Access because it’s designed to run enterprise data systems. But for day-to-day database building, you’ll spend most of your time inside the Tables, Views, and basic database objects.
Once you understand the layout, SSMS stops feeling like a dungeon crawl and starts feeling like a well-organized control panel.
In the next step, you’re ready to start creating tables and actually building something. And that’s where the real fun begins.
If you want to see the full walkthrough, including live navigation and demos, check out the embedded video above.
If you're a Microsoft Access user and you've ever thought, "Maybe it's time to move this database to SQL Server," then congratulations: you're officially at the point where Access starts giving you that side-eye. The good news is SQL Server Express is free, solid, and perfect for learning. The slightly annoying news is that installing SQL Server can feel like assembling IKEA furniture without the pictures. So in this lesson, I'm going to walk you through installing SQL Server Express and SQL Server Management Studio (SSMS) the right way, with zero mystery and minimal suffering.
Before we can link Microsoft Access to SQL Server, we need an actual SQL Server engine running somewhere. In our case, we're keeping it simple: SQL Server running on your local computer (or a computer in your office), and Access connecting to it. That means step one is installing SQL Server Express, and step two is installing SSMS, which is the tool we'll use to actually manage databases, create tables, and generally poke at things until they behave.
Let's start with the important concept that makes everything else make sense: SQL Server Express is the database engine. It's the part that stores the data and responds when Access asks for it. It's basically the "server" part of SQL Server.
SSMS (SQL Server Management Studio) is not SQL Server itself. It's the control panel. It's the tool you use to create databases, design tables, run queries, manage security, and troubleshoot problems when something goes sideways. Think of SQL Server as the engine under the hood, and SSMS as the dashboard.
So the plan is simple: install the engine first (SQL Server Express), then install the dashboard (SSMS).
First, go download SQL Server Express from Microsoft. The exact page and link names may change over time, but the key phrase you're looking for is SQL Server Express and the current version. At the time of writing this, it's SQL Server 2025. If you're reading this in the future and Microsoft has moved on to something newer, just grab the latest Express version. The steps are basically the same.
Once the installer is downloaded, run it. You'll see a couple of choices, and this is where most people blindly click whatever sounds easiest and then wonder why things are confusing later. Choose Custom.
Not because we're doing anything fancy. We're not.
We choose Custom because it lets you actually see what's being installed and where, and it forces you to learn the terminology you'll need later when you're connecting Access to SQL Server and trying to make sense of error messages. The "Basic" install works, sure, but it hides a lot. And since this is a course, we want understanding, not button-mashing.
The installer will ask where to download the setup files. This is not where your databases will live. It's just where the installer temporarily stores the files while it does its thing. The default location is fine. Unless you're dealing with a tiny system drive and you know what you're doing, leave it alone and continue.
Once the download finishes, you'll land in something called the SQL Server Installation Center. It sounds like a place you need a badge to enter, but it's basically just a launcher. Choose the option for a New SQL Server standalone installation.
Then you'll see the license agreement screen, where Microsoft politely asks for your firstborn child and your immortal soul. (I'm kidding. Mostly.) Click accept and move on.
Next you'll hit the update setting. SQL Server can optionally receive updates via Microsoft Update. This is personal preference. I typically turn automatic updates off because I like controlling when things update, since updates can occasionally break things and I prefer discovering that on a weekend and not during a workday. If you're comfortable with auto-updates, it's fine. Either choice is valid. Just pick intentionally and continue.
After that, SQL Server will run an installation rules check. You're looking for one thing: no red X errors. Warnings are usually informational and not fatal. A common warning involves Windows Firewall, and that's normal. We're installing SQL Server locally, not exposing it to the internet. We can adjust firewall rules later if we ever need other computers on the network to connect.
And let me pause for a very important public service announcement: do not expose SQL Server directly to the internet. Yes, it can be done. No, you shouldn't do it unless you're a security professional or you're paying one. I speak from experience. One of the fastest ways to ruin your week is letting random strangers on the internet take a crack at your database server. Use proper hosting or services if you need remote access. For this course, we're keeping things in-house for a reason.
Next, you'll see a screen about Azure extension. Azure is Microsoft's cloud platform, and it's great, but it is not what we're doing here. We're building a local SQL Server setup for Access users. Turn Azure stuff off and continue. You can always explore cloud options later, after you've learned the fundamentals.
Now we get to the features selection screen. For Access users, there is exactly one thing you must install: Database Engine Services.
That's it. That is SQL Server.
The other options are for more advanced scenarios (replication, full text search, PolyBase, etc.). They're not necessary for this course. If something extra is already selected by default, it won't usually hurt anything, but the key takeaway is: Database Engine Services must be selected. Then continue.
Now comes a major vocabulary word: Instance.
This is where Access users tend to get confused because SQL Server terminology doesn't match Access terminology. In Access, you have one database file (ACCDB) and that's basically the whole universe. SQL Server doesn't work like that.
An instance is a running copy of SQL Server installed on a machine. One SQL Server instance can contain multiple databases inside it.
So think of it like this: the instance is the container, and your databases live inside that container.
SQL Server Express installs by default as a named instance called SQLEXPRESS. Keep it that way. Seriously. Leave it alone. Using the default makes everything easier, especially when you're learning. Later, you can install other instances if you truly need them, but most people never do.
Next you'll see server configuration settings (services, startup types, accounts, etc.). For almost all local learning and small office setups, the default settings are fine. This is where enterprise administrators spend their lives tuning things. We are not doing that in this course. Leave the defaults and continue.
Then you'll hit the authentication mode screen. Choose Windows Authentication.
This is the best option for in-house setups because it uses your Windows login. No separate SQL password. No storing credentials. And it integrates very naturally with Access.
And no, you do not need a Windows domain. Windows authentication works in peer-to-peer networks too, as long as the users who need access have Windows logins that exist on the SQL Server machine.
For example, imagine a tiny office with three people: Joe, Bob, and Sue. Everyone has their own computer, and Bob's machine becomes the SQL Server machine (because Bob is the "database guy"). If Joe and Sue need to use Access linked tables against SQL Server on Bob's computer, they just need accounts on Bob's machine. Many offices keep it simple by using the same username/password across all PCs for each user. Totally normal.
Once you click next, SQL Server installs the engine. This part might take several minutes. It's a great time to grab coffee, pet the dog, or tell yourself you're going to reorganize your office later (you won't, but it's nice to dream).
When it's finished, you should see green check marks and "Install successful." If not, don't panic. Post in the forums and we'll troubleshoot it, but in most cases the install is smooth.
At this point, the SQL Server engine is installed and running. Now we just need SSMS so we can manage it.
Go download SQL Server Management Studio (SSMS) from Microsoft. Note that Microsoft now ships SSMS separately. Years ago it was bundled, but not anymore. This is actually a good thing because it means you can install SSMS on your workstation and manage SQL Server on a different machine, which is common in real-world setups.
Run the SSMS installer. For our purposes, the defaults are fine. Install it and let it finish.
You may be prompted to restart Windows afterward, especially on a fresh SQL Server setup. If you're prompted, restart. Don't skip it. SQL Server installs a bunch of services and components, and you'll save yourself a lot of weirdness by doing a clean reboot now.
Once SSMS is installed, launch it. I recommend pinning it to your taskbar because you'll be using it constantly throughout this course.
SSMS may ask you to sign into a Microsoft or GitHub account. You can skip that. It's not required to use SSMS or connect to SQL Server. That's mainly for syncing settings and cloud-related features. We're staying local and simple.
Now you'll see the connection screen. This is where everything clicks together.
The Server name format is:
ComputerName\InstanceName
So if your computer is named KIRK (because you're a Star Trek nerd like me), and you installed the default instance SQLEXPRESS, your server name would be:
KIRK\SQLEXPRESS
Choose Windows Authentication and click Connect.
You may see an error that looks scary, something like a connection warning involving encryption and certificates. On a new local install, this is often the famous "self-signed certificate" message. Translation: SQL Server is trying to encrypt the connection, but Windows doesn't automatically trust the certificate because SQL Server created it itself. On a local training system or in-house setup, this is normal.
The fix is simple: check the box that says Trust server certificate and connect again.
In a big corporate or internet-facing environment, you'd install a proper certificate from a trusted authority. But for local and in-house use, trusting the server certificate is perfectly normal and very common.
Once connected, you'll see the Object Explorer on the left and you're officially in business. SQL Server is installed, SSMS is working, and you're ready for the fun part: actually building databases and connecting Access to them.
And yes, in the next lessons we'll start creating tables, learning the SQL Server equivalents of Access concepts, and eventually linking your Access front-end to SQL Server tables like it was born to do it.
If you want the full screen-by-screen walkthrough, the embedded video above will walk you through the entire process exactly as shown. But if you followed this article, you should now understand what you're installing, why you're choosing those options, and what to do when SQL Server throws one of its dramatic little warnings at you.
Let me guess... you keep hearing the words “SQL Server” and your brain immediately jumps to corporate IT departments, thousand-dollar licenses, cloud dashboards, and a guy named Todd who makes you submit a ticket to change your password. Yeah... no. Not what we’re doing here. In this lesson, I’m going to show you what SQL Server actually is (and what it definitely is not), why Microsoft Access users move up to it, and why you don’t need to “abandon Access” to do it.
The most important thing to understand right from the start is this: SQL Server is not a replacement for Microsoft Access. It’s an upgrade to what’s underneath it. You still build your forms, reports, queries, VBA code, and all the user interface stuff in Access. SQL Server simply becomes the place where your data lives... more securely, more efficiently, and with a whole lot less risk of someone doing something dumb and expensive.
So what is SQL Server?
SQL Server is a database engine. It runs quietly in the background as a Windows service. You don’t double-click it like an Access database file. You don’t “open” SQL Server the way you open an ACCDB. SQL Server just sits there like a well-trained guard dog, waiting for authorized applications to connect and ask for data.
And in our case, that application is Microsoft Access.
Think of Access as your front end: the stuff people see. The forms, buttons, reports, dashboards, search screens, and the things users click on when they’re pretending they “don’t do computers.” SQL Server is the back end: the vault where the data is stored, protected, and served up only when requested.
That alone is a major shift from how shared Access backends work.
With Access, the database is the file. If your backend is sitting in a shared folder, then every user must have read/write access to the file. And if they have access to the file, they can copy it. If they can copy it, they can open it. And if they can open it... they can see everything inside it. Tables, customer data, confidential notes, credit cards (please don’t), and yes, they can delete your stuff too.
I’ve literally had clients call me because a disgruntled employee deleted the backend database file and walked out with a copy of it on a thumb drive. Not because Access is “bad.” Because Access is file-based, and file-based systems have limitations. That’s just reality.
Now SQL Server changes that completely.
With SQL Server, users never touch the database file. In fact, most users couldn’t even find it if you offered them $100 and a map. Their Access front end sends requests to the SQL Server, and SQL Server decides what they’re allowed to see and do.
That’s where the big win happens: permissions.
You can allow a user to read certain tables but not others. You can allow updates but prevent deletes. You can hide entire sections of data. SQL Server enforces security at the server level, not the “hope nobody clicks the wrong thing” level. And if someone’s angry and wants to cause damage, they can’t just grab the database and walk away with it like they’re stealing snacks from a vending machine.
This matters even more when you’re dealing with sensitive data. Credit card numbers are the classic example. Medical information, personal identity data, financial records... these are things that should never be sitting in a shared Access backend file on a network drive. SQL Server supports encryption, auditing, controlled access, and real security. In some industries, it’s not even optional. It’s policy. It’s regulation. It’s the law. This isn’t paranoia. It’s responsible database design.
Now let’s talk about one of my favorite analogies for this.
A shared Microsoft Access backend is like a restaurant with a big open salad bar.
The food is just out there. Anybody can walk up. No one is checking plates. No one is monitoring what you’re allowed to take. And once you’re in the buffet area, you can touch everything. We’ve all seen it: little kids sticking their grubby fingers in the croutons, people reaching over each other, and someone sneezes within six inches of the lettuce. It’s chaos. It’s uncontrolled access.
That’s basically a shared Access backend. If a user can open the file, they’re standing at the buffet. They can see everything, copy everything, delete everything, or take the whole tray and leave the building.
SQL Server, on the other hand, is fine dining.
You don’t walk into the kitchen. You don’t open the fridge. You sit at a table and you place an order with a waiter. The waiter checks the menu, confirms it’s available (not “86ed”), and sends the request to the kitchen. The kitchen prepares exactly what you’re allowed to have, and the waiter delivers it back to your table.
That’s SQL Server.
Users submit requests through Access. SQL Server checks permissions. SQL Server returns only what they’re allowed to have. The server stays in control the entire time. Where would you rather eat?
Now let’s squash another big misconception.
A lot of people hear “SQL Server” and assume you need a big corporate network, a domain controller, and an IT department. Nope. Not for what we’re doing here.
We’re using SQL Server Express, which is free. It installs right on Windows 11. You don’t need Windows Server. You can even install it on the same machine you’re using to build your Access databases. And it works great in small offices running a basic peer-to-peer network.
You can absolutely run SQL Server locally in-house and still get the biggest benefits: security, performance, and scalability. No cloud required.
Now yes, there are hosted versions of SQL Server. Microsoft calls theirs Azure, which is basically SQL Server living on Microsoft’s servers. There are also third-party hosting companies like Winhost (the provider I recommend and personally use) that offer hosted SQL Server as well. Those are great when you need outside access or don’t want to manage any hardware yourself.
But the key point is: the cloud is optional. SQL Server itself is the upgrade, and you can deploy it however you want.
Next big benefit: performance.
Let’s say you have a million orders in your Orders table and you want to find ten of them. With a shared Access backend, Access often pulls large chunks of data across the network to your PC, then processes it locally. That means a lot of network traffic, a lot of waiting, and a lot of unnecessary “work” happening on every user machine.
SQL Server flips that around.
With SQL Server, the server processes the query right where the data lives, then sends back only the rows you need. Not the whole table. Not 200,000 records to sift through. Just the ten results. Less network traffic. Faster performance. Happier users. Less coffee consumed while staring at a frozen form.
This is one of the main reasons Access databases feel fast when the data is local, but slow when the backend is shared over the network. It’s not always the number of users. It’s the architecture. With SQL Server, you stop dragging data across the network just to do work on it.
Or as I like to say: don’t drag the data to the work... bring the work to the data.
And this leads into scalability.
People often think SQL Server only becomes worthwhile when you have dozens of users. I disagree. Even with three, four, or five employees, security starts to matter. Risk starts to matter. And performance becomes noticeable quickly as your data grows.
But once you start scaling up, the difference becomes huge.
With a shared Access backend, the more users you add, the more network traffic you create, and the more processing happens on each PC. Things slow down. Corruption becomes more likely. Somebody starts complaining, “It was working fine last year!” (Yes, because last year you had 8,000 records. Now you have 800,000.)
With SQL Server, the architecture doesn’t change when you grow. Five users, fifty users, five hundred users. It’s still Access front ends sending requests to SQL Server. SQL Server is designed for this. It is literally its job. I’ve personally built systems with 150-200 users all running Access front ends connected to SQL Server, and they ran just fine.
And if you ever need to go bigger, SQL Server can scale to thousands or tens of thousands of users. The important part is: you don’t have to start at that level. SQL Server lets your database grow at the same pace as your business, without hitting a hard wall where everything breaks.
So what are we going to build in this course?
We’re keeping it simple and practical.
First, we’ll install SQL Server Express and SQL Server Management Studio (SSMS). SSMS is the tool you use to manage SQL Server, create tables, create views, manage security, and run queries. Both SQL Server Express and SSMS are free downloads from Microsoft.
Then we’ll create a simple table so you can see how SQL Server compares to Access tables. We’ll build a query and save it as a view (that’s basically SQL Server’s version of a saved query). We’ll talk about differences between Access SQL and SQL Server SQL (which is called T-SQL, because yes, tech people love abbreviations).
And finally, we’ll connect an Access database to SQL Server so you can see the whole pipeline working end-to-end: Access forms and queries using data stored in SQL Server.
This course is primarily designed for Access users, so I’m assuming you already understand the basics: tables, queries, relationships, primary keys, and how Access applications are put together.
If you’re brand new to Access, I strongly recommend starting with my Access Beginner courses first. I’ve got a completely free Access Beginner Level 1 course that’s about four hours long and covers the fundamentals. SQL Server will make a lot more sense once you’ve got that foundation.
Also, I strongly recommend you understand proper table design, normalization, and relationships. SQL Server is much less forgiving than Access when your design is sloppy. If you’re still storing multiple values in one field or building “one table to rule them all,” SQL Server is going to smack you upside the head. (Not literally... it’s passive-aggressive like that.)
A working knowledge of SQL is also recommended. You don’t need to be an SQL wizard, but you should be comfortable writing basic SELECT queries, using WHERE clauses, ORDER BY, joins, and understanding how queries drive forms and reports.
If your SQL skills feel rusty, I also have a three-part SQL seminar series that focuses on SQL in Microsoft Access. It’s optional, but it can help a lot before you step into the SQL Server world.
And no, you don’t need to be a VBA developer to benefit from SQL Server. You can absolutely use SQL Server as a backend with linked tables and Access queries without writing code. But if you do like VBA, the developer series of this course will cover advanced techniques like connecting without linked tables, executing SQL Server logic from VBA, stored procedures, and building more secure and flexible systems.
Now for the big takeaway, because I want you to really tattoo this on your brain:
You do not stop being an Access developer when you start using SQL Server.
You become a better Access developer.
Same tools. Same front end. Same skills. But now your application has a stronger foundation underneath it: better security, better performance, and the ability to grow without collapsing under its own weight.
If you understand Access, you already understand most of what you need. SQL Server builds on the same concepts, just in a more controlled and professional environment.
So don’t think of this as “leaving Access behind.” Think of it as leveling up what you already know.
And with that big-picture overview out of the way, in the next lesson we’ll stop talking about SQL Server and actually install it: SQL Server Express and SQL Server Management Studio. You’re going to see it’s not scary at all.
If you want the full walk-through, examples, and on-screen setup, watch the embedded video above. But you should now understand what SQL Server is, why Access users use it, and why it’s such a powerful next step.
If you've been watching my videos for any amount of time, you probably know I keep a Microsoft Access database running on what I lovingly refer to as my "server machine." It's not a real server. It's basically a copy of Windows 10 sitting in the corner doing background work like a dedicated crewman who never sleeps.
This machine runs a 24-hour loop and does all kinds of automated tasks for me: sending emails (using my Email Seminar template), performing backups, running scheduled routines, and generally keeping my business humming along.
Now, for years I've noticed something annoying: if I don't reboot that machine at least once a week, Access starts throwing weird errors. Out-of-stack space. Random instability. Strange behavior that always magically disappears after a reboot. I assumed it was just Windows being Windows.
So I decided to run a little experiment. Starting January 15th, I changed my routine:
1) My nightly backup runs.
2) After the backup finishes, I force-kill MSACCESS.EXE. See Kill Access
3) That runs automatically three nights per week: Monday, Wednesday, and Friday.
And guess what?
Since I started doing that, I have not gotten a single one of those weird Access errors. We're going on three weeks now.
To put that in perspective: before this, if I didn't reboot that machine at least weekly, I'd get errors by Day 7 without question. And I'm talking years of this behavior. Like clockwork. In fact, if I was on vacation and the vacation would last longer than seven days, I had to make sure I logged in remotely to reboot the server. That meant if I was on a cruise in the middle of the Caribbean, I had to find a way to get online. Which nowadays is not a big deal, but you know ten years ago this was an issue.
So what does that tell me?
It strongly suggests that Access (or something in my environment) leaks memory over time, and simply closing the database isn't enough. Because here's the key part: my database already closes and restarts itself every 30 minutes during the loop. But I'm not killing the actual MSACCESS.EXE process, and clearly something is sticking around in memory.
Now, to be fair, this could be my code too.
This database has been evolving for about 20 years. It's huge. It does a LOT. And 20-years-ago Rick did not have today's coding skills. So I wouldn't be shocked if there's some old nonsense in there... unclosed objects, recordsets left hanging, orphaned references, duct tape, bailing wire... basically Scotty's engine room with bypasses everywhere like a Christmas tree.
That's why one of my favorite lines in class is: If you set it, you got to forget it.
But regardless of whether the leak is Access itself or my Franken-database, the result is real: killing MSACCESS.EXE a few times a week seems to prevent most of these cumulative weird errors.
So if you're running long-term Access automation (especially on a machine that stays up 24/7), consider scheduling an occasional full Access process kill, not just closing the database. It might save you from the classic creeping instability that shows up after several days.
If you started your billing database in Microsoft Access like an Excel spreadsheet, you're not alone... but you're probably making reports WAY harder than they need to be. In this week’s TechHelp Quick Queries, I cover a classic beginner mistake (and a simple upgrade that makes your whole database smarter).
The problem being addressed: One of my students built a billing system where each customer record has separate fields for January, February, March, etc. It looks clean at first, but the moment you want to answer a basic business question like "How much money did we collect today?" everything starts falling apart.
Why common solutions fail: When you store repeating monthly payments as separate fields, every new need creates more clutter: a paid flag for each month, then a date paid for each month, then an amount paid for each month... and suddenly one table record turns into a monster. Reporting becomes painful because you have to check a dozen different fields just to total deposits for one day. And when the next year rolls around? Now you're duplicating customer data just to start over with another set of month fields.
The practical solution presented: The better approach is using a related payments table. Instead of one record holding twelve months of payment fields, you store each payment as its own record with the customer ID, payment date, and amount paid (and optionally what billing period the payment applies to). This makes end-of-day reports simple, lets you query payments by date instantly, and keeps your database scalable without redesigning everything every January.
Who the content is for: This one is for beginners (especially anyone transitioning from Excel to Access) who are building billing, membership, or subscription databases. If you've ever created one table and started adding "JanPaid, FebPaid, MarPaid..." columns, this video will save you a lot of future headaches.
What is coming next: I also answer a bunch of other great questions in this episode, including tabbed documents vs overlapping windows, Power Query merge performance compared to Access joins, combo box list caching, fixing report footer gaps, hiding tables with VBA (and why it is not real security), and whether Access is still worth learning in 2026. Lots of good stuff in this Quick Queries!
