dotnet run

dotnet run by Alex Hedley

from Computer Learning Zone News https://599cd.com/3786

The Setting That Silently Limits Microsoft Access Query Results That You Might Not Know Exists QQ#78

Ever run a query in Microsoft Access, know for a fact there are hundreds of records behind it, and yet Access politely hands you… 99? No error. No warning. Just a quiet little ceiling on your data. If that has ever happened to you, you are not losing your mind, and your database is not broken. You have just run into one of those tiny hidden settings that can cause a whole lot of confusion if you do not know where to look.

The culprit is something called the Top Values setting. It lives right up in your query design tools, and it controls how many records Access will return. Most of the time it is set to "All," which is exactly what you want. But if it ever gets changed, intentionally or accidentally, your query will quietly stop returning results once it hits that limit. No alarms. No flashing lights. It just stops.

Let’s look at how this happens. Open any query in Design View and look at the ribbon. You will see a dropdown labeled Top Values. If that box says 10, 50, 99, or any other number, that is your cap. Access will return only that many rows, even if your underlying table has thousands.

This setting often gets changed during testing. For example, you might temporarily set Top Values to 100 while building or troubleshooting a query so it runs faster. Then later you forget to set it back. The query works fine, but now your reports or forms that rely on it are mysteriously missing data.

If you switch over to SQL View, you will see this expressed as a clause in the statement itself. It looks like this:

SELECT TOP 99 * FROM Customers;

That TOP keyword is the limiter. Remove it, or change the Top Values dropdown back to All, and suddenly your missing records reappear like magic.

This becomes especially important when queries feed other objects. If you build a form or report on top of a limited query, the limitation carries through. The form is not broken. The report is not filtering incorrectly. It is simply inheriting the row cap from the query underneath.

Another common beginner mistake is assuming the query is filtering data because of criteria, joins, or grouping. People start tearing apart relationships and WHERE clauses trying to fix something that is not even the problem. Always check Top Values first when record counts look suspiciously round.

While we are on the subject of quiet database gotchas, this came up alongside a few related discussions recently. For example, using SharePoint as a backend can introduce its own limitations and performance quirks compared to SQL Server. Another frequent issue involves parameter prompts. If multiple queries all ask for the same date range, the better approach is to pull those values from form controls instead of repeated popups. Different problem, same theme. Small design choices can have big usability consequences.

The big takeaway here is simple. When query results look incomplete, do not panic. Do not assume corruption. Check the basics first. Top Values is one of those tiny switches that can have an outsized impact on what you see.

If you want to watch the full walkthrough, including where to find the setting and how it behaves in real time, the video above shows it step by step. But now you know what to look for, and more importantly, why it happens.

Live long and prosper,
RR

Vibe Coding and the Illusion of Understanding

So let's talk about this whole "vibe coding" thing.

If you haven't heard the term yet, it basically means you just tell the AI what you want and let it write the code for you, like having a junior developer who never sleeps and doesn't complain about your specs. You stay in the driver's seat conceptually, but the machine is doing most of the typing. In theory, you focus on the idea while the AI handles the syntax, the loops, the plumbing.

Now, I'm not gonna sit here and pretend I'm above it. I use AI for code all the time. If I need a quick function to sort an array, import a text file, or hammer out some boilerplate VBA that I've written a hundred times before, yeah, I'll let the AI do it. I already know how it works. I just don't feel like spending 20 minutes typing something I've had memorized since the first Bush administration. At this point it's muscle memory, not intellectual exercise.

That's where vibe coding makes perfect sense to me. As an experienced developer, it's a power tool. It's like using a nail gun instead of a hammer. I still know how framing works. I'm just getting the job done faster, and with fewer smashed thumbs.

Where I start to get nervous is when people try to build entire applications this way without understanding what's under the hood. I see folks spinning up full websites, business apps, database systems, all by prompting an AI and copy-pasting whatever it spits out. And hey, it works... until it doesn't. And when it doesn't, it usually breaks at 4:55 PM on a Friday. Then what? When something breaks, slows down, corrupts data, or starts throwing errors that make no sense, you're stuck. If you don't understand the fundamentals, you don't even know where to start.

It's like calculators in math class. I'm all for calculators. I use one every day. But you still have to learn arithmetic first. You still need to understand why 4 + 4 equals 8 before you trust the machine telling you the answer. Otherwise you're just blindly accepting output. Same thing here. AI is fantastic at producing results. It is not automatically fantastic at teaching you how to think like a programmer. And thinking like a programmer is the real skill. (1)

Back when I was learning, I typed code line by line out of books and magazines. Half the time I didn't fully understand what I was typing, but the act of doing it built the mental wiring. You learned patterns. Logic. Cause and effect. Debugging instincts. That friction mattered. Copy, paste, run skips that entire growth process.

The car analogy fits here too. You don't need to know how an engine works to drive. That's fine. Casual users can absolutely vibe code their way to a small app, a hobby project, something non-critical. No problem there. But if you want to build cars, design engines, or fix one when it explodes, you better know what's under the hood, preferably before the explosion. Same goes for software. If it's business critical, if money, customers, or data integrity are involved, you cannot rely on vibes alone.

And this isn't a new sci-fi warning either. Star Trek covered this decades ago.

In the original series episode "The Apple", Captain Kirk and the crew encounter a society completely dependent on a machine called Vaal. It runs their lives. Keeps the weather stable. Provides food. Maintains order. Sounds great... until it starts malfunctioning. The people have no idea how anything works because they've never had to think for themselves. The Enterprise shows up and has to save the day, which I'm pretty sure violates at least three sections of the Prime Directive. Hey, even in the 23rd century somebody still has to be the sysadmin. OK, Scotty, time to three-finger-salute this entire planet...

Then you've got a similar theme in The Next Generation episode "When The Bough Breaks". Picard and the crew discover a highly advanced but dying civilization that automated everything, including reproduction and societal systems. Over time they lost the knowledge to maintain their own technology. When things started failing, they couldn't fix them. They had the tools but not the understanding.

Sound familiar?

That's the long-term risk of vibe coding if it's taken to the extreme. If we outsource all understanding to machines, we create a generation of builders who can't repair what they build.

Now, to be fair, AI is not the villain here. It's a tool. A powerful one. I actually agree with the more balanced take I've been seeing lately. Use AI as an assistant, not a replacement. Let it help you debug, explain, prototype, and accelerate. But still learn the fundamentals. Still build projects yourself. Still struggle a little. (2)

Because that struggle is where the real learning happens.

I've also been watching discussions about how coding tutorials are supposedly "dead" because of AI. I don't buy that either. The format might be changing. Attention spans might be shrinking thanks to short-form social media dopamine machines that make learning anything longer than 30 seconds feel like homework. The job market might be fluctuating. But the need for structured learning hasn't gone anywhere. If anything, it's more important now. Because when AI gives you an answer, you still need to know if it's a good answer. That's the part people forget.

In my world, especially working with databases like Access and SQL Server, fundamentals matter even more. Data integrity. Relationships. Indexing. Concurrency. Error handling. You vibe code that stuff wrong and you don't just get a bug, you get corrupted data or a business that can't operate Monday morning, which is a great way to meet your clients' lawyers. That's not a weekend hobby app anymore. That's real-world impact.

So yeah, I'm not anti vibe coding. I use it. I enjoy it. It's fun watching AI spit out Python or VBA or PowerShell scripts on demand. It feels like living in the future, minus the flying cars we were promised. But I'm also old school enough to believe you should still learn how to code if you want to call yourself a developer. Otherwise you're just steering the ship without knowing how the engines work. And if the engines fail, you're adrift, staring at error messages like they're written in ancient Klingon.

So that's my take. Use the tool. Embrace the speed. Enjoy the convenience. Just don't skip the understanding part if your livelihood depends on the software you're building.

Curious where you land on this. Are you using AI to accelerate code you already understand, or are you letting it build things you couldn't explain if they broke tomorrow?

LLAP
RR

(1) I remember back in high school and college, a lot of kids were convinced half their classes were pointless. You know the type: "Why do I have to learn this stuff? When am I ever going to use algebra? Or chemistry? Or biology?" On the surface, I understand the frustration. Not everyone grows up to be a scientist or an engineer. But that misses the bigger picture. Those subjects aren't just about the raw information. They're about training your brain. They teach logic, sequencing, cause and effect, and how to follow a structured process. It's algorithmic thinking. You might never use the Pythagorean theorem in your day-to-day life, but learning how to apply a formula, follow a repeatable method, and trust a step-by-step system is the real lesson. That mental wiring carries over directly into programming and problem solving whether people realize it or not. What really gets me is when some of those same folks grow up and start loudly questioning well-established science, like climate change or vaccines, while conveniently forgetting they spent half of science class picking dried glue off their hands or trying to light their book bag on fire with a Bunsen burner. It's hard to respect someone's scientific skepticism when they opted out of the learning part back when they had the chance. "Oh really, Nancy? You've 'done your research?' I remember you skipping science class almost every day to go smoke in the bathroom..."

(2) I don't view AI as some looming villain any more than past technological leaps were villains. Computers didn't "destroy society" because they put typewriter manufacturers out of business. Calculators didn't spell the end of civilization because slide rule and abacus makers had to pivot. Automobiles didn't get framed as evil because they replaced horse-drawn transportation. Every major technology shift displaces certain jobs while creating entirely new industries around it. AI is no different. Yes, it's going to automate some roles. That part is unavoidable. But it's also going to open doors for new specialties, new workflows, and new opportunities that didn't exist before. The real skill, as always, is learning how to adapt, how to use the tool effectively, and how to evolve alongside it, because whether people like it or not, AI isn't going anywhere. Just don't fall into the trap of letting it do your thinking for you, because that's the part of the job that actually matters. Embrace it, learn it, use it but don't outsource your understanding to it. Use it to amplify your skills, not replace the need to have them.

Because the moment the tool becomes the only one who knows how your system works, you've already lost control of it.

Live long and prosper,
RR

Stop Microsoft Access Form Errors When Referencing Other Forms That Are Not Ready Yet

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.

Live long and prosper,
RR

SQL Server Management Studio Interface Overview for Microsoft Access Users Getting Started

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.

Live long and prosper,
RR

Install SQL Server Express + SSMS (Step-by-Step) for Microsoft Access Users | Beginner 1 Lesson 2

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.

See you in the next lesson!

Live long and prosper,
RR

SQL Server for Access Users: What It Is (and Isn't), and Why You Might Actually Want It

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.

Live long and prosper,
RR