How To Cancel A Long Running VBA Loop With The Escape Key In Microsoft Access

Tired of waiting for a never-ending Access loop to finish, just so you can realize you needed to hit cancel three minutes ago? Let's talk about a simple way to let your users abort a running process with the keyboard - no more clunky checkboxes, no frantic clicking, and (most importantly) no forced shutdowns required. The Escape key can be your new "panic button." Here's how and why you should set this up in your own databases.

When you create long-running operations - like sending out 10,000 emails in a batch - your users might suddenly realize they need to stop the process. If you don't give them a way to abort gracefully, their options are ugly: force-quit Access, cross their fingers, or just stare in silent dread. Historically, maybe you added a hidden checkbox or "abort" button, but those solutions aren't very friendly (especially to keyboard-focused users or anyone with a penchant for hitting Escape in emergencies). Let's teach your database to listen to the Escape key and stop those runaway loops on command.

Let's start with the basic approach: tying an on-form "Cancel" button to the keyboard. Access forms have a handy built-in feature: any command button set to Cancel = Yes automatically responds to the Escape key. So, if you stick a "Cancel" button on your form and connect it to your abort logic, your users can smash Escape and halt the process without fumbling for the mouse. Just make sure you wire up your loop so it checks regularly for an abort request.

But here's a power tip: don't forget about DoEvents. This little command sprinkled inside your loop is what lets Access recognize outside actions - like button clicks or keys being pressed. Without DoEvents, your process hogs the show and ignores everything else, giving the illusion that Access has frozen. So, always include DoEvents (often inside a status update function) during each loop iteration.

Now, for those who want an even cleaner keyboard solution, you can skip the "Cancel" button entirely and work directly with keyboard events. Here's how:

1. Open your form's properties and set Key Preview to Yes. This tells Access your form wants first dibs on all keyboard activity, regardless of which control is active.
2. In the form's On Key Down event, check for the Escape key by testing If KeyCode = vbKeyEscape.
3. When Escape is pressed, set your abort variable (maybe Abort = True) and swallow the key (KeyCode = 0). For bonus feedback, update the status display so users know you heard them.

This approach is more "global" to the form, so you don't need a visible user control at all. As long as your loop keeps checking for Abort = True (don't forget that DoEvents!), users can bail out instantly, just by hitting Escape.

Keep in mind, the responsiveness depends on how often you check for that abort signal. If your loop has a long delay (for example, sleeping a full second between iterations), the cancel might feel sluggish. For faster response, either shorten the sleep interval or check for aborts in smaller increments inside your main loop.

What about taking this further, so you can trap Escape key presses throughout your entire database, not just on a single form? That's possible too with a tiny bit more advanced VBA, and I cover that in my member-exclusive extended tutorials. (Not a member? Time to consider an upgrade… there's a code vault packed full of goodies over on my website!)

Bottom line: making the Escape key your abort switch gives your users a fast, intuitive way to kill a stuck process - and it makes your applications feel a lot more professional and user-friendly. I'm a keyboard person myself, so this kind of improvement just feels natural.

If you want the detailed walk-through, check out the video above. Have questions, or want to share your own favorite abort methods? Let me know in the comments!

Live long and prosper,
RR

How To Enter Data In SQL Server Tables Using SSMS

Ready to wrangle some data directly in SQL Server? Today, we're diving into how to enter and edit records right in your tables using SQL Server Management Studio (SSMS). You'll see how to add new records, handle those infamous required fields, make sense of identity IDs (autonumbers), and even import sample data from Access - using Excel as your trusty go-between. All of this is super handy for developers and serious Access users who want a real-world set of data to play with as we build out our queries.

If you've ever made tables in Access and fiddled with data directly in them, you already get the vibe. While your users will (hopefully) never touch the backend tables, as a developer you sometimes need to sneak in behind the scenes - add a test record, make an edit, or just seed a few rows so things aren't completely empty. Being able to enter data straight into SQL Server tables through SSMS lets you do all this quickly.

The most straightforward way to enter data in SSMS is to right-click your table in the Object Explorer and choose "Edit Top 200 Rows." Why 200? Because Microsoft picked a number, and that's what you get. This opens a grid very reminiscent of Access datasheet view, putting you in edit mode so you can start typing directly into cells.

Here's how it works: Columns that are locked (like an auto-numbered primary key) can't be edited, but move over to fields like "FirstName" or "LastName" and type away. One thing to notice - unlike Access, where the autonumber appears as soon as you start typing, SQL Server assigns its identity ID only after you save the record (by moving off the row). At first this feels a little weird, but you get used to it - and it's important to understand if you're coming from Access, because it can affect how you write your code, especially if you're inserting related child records that need that ID right away.

While you're editing a row, you'll see a little pencil icon appear (just like in Access), which means the record is "dirty" and hasn't been saved yet. There's also a red exclamation point - don't panic, it's just SSMS's way of saying changes haven't been written yet. If you try to leave a required field blank (like "LastName"), you'll get an error. SQL Server's error messages are rarely warm and fuzzy, so actually read what it says. Fill in your missing data, and move off the row - the warning goes away, data is saved, and your new identity ID is assigned.

Sometimes, you might see a message that the record was saved, but a problem occurred while retrieving the data back. Relax - your record has been saved, SSMS just got confused. Just close and reopen the grid, and your data (and shiny new ID) will be there.

When entering certain types of data, like percentages, you'll want to stick to the standards most Access developers use: enter 0.1 for 10%, 0.5 for 50%, and so on. This keeps your calculations easy and consistent, and generally saves you from having to divide or multiply by 100 in your queries later. And yes - things like formatting (especially for dates) might look goofy in SSMS. You might see a big ugly datetime with seconds and milliseconds, but that's just how SQL Server wants to show it. Don't sweat it - you'll handle the pretty formatting in Access or in your reports later.

For Boolean fields, even though SSMS will display "True" and "False," you should get in the habit of entering 1 for true and 0 for false. SQL Server likes it that way, and you'll avoid those oddball issues that pop up when you try to insert other values. Access is more forgiving; SQL Server likes to keep things black and white - or, well, ones and zeros.

Let's talk about importing data. If you have a set of sample data in Access (like from my TechHelp Free Template), an easy way to bring it into SQL Server is: copy it to Excel, tidy it up, and then paste it into your SSMS table grid. This works nicely because you can rearrange, add, or delete columns in Excel so everything lines up perfectly with your SQL table's structure.

For example, if you have extra columns in your Access export (like "Phone" when your SQL table doesn't have a phone field), just delete them in Excel. Want to skip pasting IDs so SQL can assign them? Delete those values as well. Make sure your columns match the order of your SQL table - don't be afraid to cut and insert columns as needed to make everything fit. If your numbers (like credit limits) copied over as dollar amounts, remove the pesky "$" - SQL Server does not like those. Format dates as plain short dates. Remove the header row, copy your prepped data, click in the first row in SSMS, and paste. Done! If you get errors, double-check your formatting - almost always, it's a stray symbol, wrong column order, or a non-matching data type.

Keep in mind, this method is for sample or test data and not for production loads. It's the perfect way to get a handful of records into your table so you have something to work with when writing queries, forms, or reports. Trust me, testing queries on empty tables is no fun.

And there you have it! You now know how to enter and edit data right in your SQL Server tables using SSMS, manage identity IDs, and import sample data from Access via Excel. This hands-on knowledge makes it much easier to develop, test, and troubleshoot as you build your application.

If you need a deeper walk-through (or just want to see it in action), check out the video above. Parts 2 and 3 of the course are waiting for you on my site, by the way - and don't forget, Silver Members and up get a free class every month!

Live long and prosper,
RR

The Problem With Concentrated Power

Recent events have forced me to rethink a couple of long-held assumptions, and anyone who knows me knows I don't do that lightly. Not about parties or personalities, but about power itself, how it's structured, and how easily it can be stretched beyond what feels healthy for a democracy.

First is the power of the presidency. Over time, the office of President of the United States has accumulated an enormous amount of unilateral authority. Executive orders, emergency powers, tariff controls, agency directives. All tools that were meant to be scalpels, not sledgehammers. Tools that were often intended for limited or urgent use now feel like levers that can reshape massive portions of government with the stroke of a pen. That concentration of power makes me uncomfortable, regardless of who holds the office.

In Starfleet, even a captain commanding a starship doesn't operate without guardrails. There are regulations, oversight, and the possibility of being relieved of command if judgment fails. Absolute authority sounds efficient, right up until it isn't.

This isn't about Republicans or Democrats. It isn't about Trump, Biden, or whoever comes next. The concern is structural. One individual should not be able to make sweeping economic decisions, reorganize federal agencies, or redirect national policy without meaningful legislative oversight. Especially in times of peace. Decisions involving money, trade, and taxation should run through Congress. That's where the constitutional authority over the purse was designed to live. Invoking emergency or war-adjacent powers when there is no declared war blurs lines that were originally put in place as guardrails, not suggestions.

Government departments should be independent of whichever political party is in power. I want the best doctors in charge of the CDC. I want career educators shaping national education policy. I want experienced economists guiding commerce and trade decisions. I want seasoned investigators overseeing federal law enforcement agencies like the FBI. I want climate scientists directing environmental policy, engineers and urban planners leading infrastructure and transportation, energy specialists managing the power grid, banking and securities experts regulating financial markets, and scientists running federal research institutions.

These roles affect public safety, financial stability, and long-term national planning. They require institutional knowledge and technical expertise that takes years, sometimes decades, to develop, and they shouldn't turn over simply because a new administration wants ideological alignment at the top. I don't want politicians playing musical chairs with positions that require a lifetime of expertise.

If the Enterprise is in a medical crisis, you want Dr. Crusher in Sickbay, not someone appointed just because they're politically loyal to Captain Picard. Competence matters more than allegiance when lives are on the line. When the warp core is destabilizing, you want Scotty running the diagnostics, not someone assigned to the engine room because he was buddies with Kirk at the Academy.

Leadership continuity in these areas matters. These positions shouldn't be subject to abrupt dismissal on the whim of a president, or the mood of a news cycle. If anything, appointments at that level should require broader legislative approval and oversight, ensuring that competence, not partisanship, is the primary qualification.

The second shift in my thinking involves states. For years, I've questioned whether state governments were still necessary in their traditional form. They originated in a very different era, when communication was slow, travel was difficult, and real-time updates meant waiting three weeks for a letter. Regional governance filled logistical gaps that no longer exist in the same way. But I've come to see value in a secondary layer of government.

Not necessarily states as we know them today, but some form of regional authority capable of acting as a counterbalance to federal overreach. When national power becomes too centralized, there needs to be an intermediate structure with the legal standing to push back, slow things down, or chart a different course. In other words, a pressure release valve.

I'm not convinced the historical state map is the best model going forward. Larger regional or metropolitan coalitions might make more practical sense. Think in terms of major population and economic zones rather than borders drawn centuries ago. But the principle remains important. Two meaningful levels of government create friction, and friction, in governance, is often a feature rather than a bug. It prevents abrupt, sweeping change driven by a single office. It forces negotiation. It distributes power. It slows bad decisions down long enough for cooler heads to prevail.

The Federation itself is layered this way. Starfleet answers to civilian leadership, member worlds retain autonomy, and major decisions require consensus. It's slower than unilateral command, but it prevents one office from steering the entire quadrant off course. Tolkien built an entire mythology around the corrupting nature of concentrated power. The One Ring didn't just grant authority, it eroded judgment. Systems need safeguards for the same reason Middle-earth did.

So where I've landed, at least for now, is this: The presidency is too powerful and should be more tightly constrained, particularly in domestic and economic matters. Congress should reclaim more authority over money, trade, and structural agency change. And some form of strong regional governance is necessary to keep federal power in check, even if that structure evolves beyond traditional states. None of this is about scoring political points. Abuse of power is a risk inherent to power itself, regardless of ideology, party, or country. Systems matter more than personalities.

And when systems concentrate too much authority in one place, history suggests it rarely ends well. It reminds me of Rush's 2112, where the Priests of the Temples of Syrinx govern every aspect of society under the reassuring promise: "We've taken care of everything." Concentrated authority always sounds efficient... especially to the people holding it.

LLAP
RR

Choosing the Right SQL Server Data Types: Numbers, Money, and Dates That Actually Behave

If you have ever moved a database from Microsoft Access into SQL Server and thought, "Well, a number is a number, right?" ... yeah, not quite. Picking the wrong data type can quietly cause rounding issues, reporting errors, and weird date behavior that you might not notice until real data starts piling up. Today we are going to make sure that does not happen by choosing the right field types from the start.

As we continue building out our customer table, this is where database design starts to get real. Text fields are easy. Numbers, currency, and dates are where precision matters. The choices you make here affect storage, calculations, compatibility with Access, and long term data accuracy. So let’s walk through the field types you are most likely to use and how to pick the right ones without overthinking it.

Let’s start simple. Say you have a field like FamilySize. No decimals, no fractions, just whole people. This is where you use an int data type in SQL Server. If you are coming from Access, think of this as your Long Integer equivalent. Clean, efficient, and perfect for counts.

Now let’s say you want to store something like a discount rate or measurement value. That is where float comes in. Float is the SQL Server cousin of Access Double. It is designed for approximate numeric values, especially where fractional precision is not mission critical. Percentages, rates, scientific measurements, those are all fair game.

But here is where a lot of people get into trouble. Money.

If you are storing currency values like CreditLimit, do not use float. Ever. Float stores numbers as approximate binary fractions, which means rounding errors can creep in. One transaction might round up, another down, and after thousands of transactions you end up with phantom pennies floating around your database. Fun in movies. Not fun in accounting.

Instead, use decimal. Specifically, something like decimal(19,4).

That pair of numbers matters. The first number is precision, the total digits allowed. The second is scale, the digits to the right of the decimal point. So decimal(19,4) means you can store very large numbers with four digits after the decimal. This closely mirrors how Access stores currency and makes migrations much smoother.

You might notice SQL Server has a built in money type. Yes, it exists. Yes, it works. But decimal is still the better design choice. It is more predictable, more flexible, and avoids some rounding quirks that money can introduce in calculations. When in doubt, decimal wins.

Next up, dates. Access keeps this simple with a single Date/Time field. SQL Server gives you options. If you only need the date, use date. But if you are migrating from Access, chances are you have time values mixed in. That is where datetime2 comes in.

Datetime2 is the modern replacement for the older datetime type. It offers better precision and flexibility. You will often see it written like datetime2(0). That number controls fractional second precision. A zero means no milliseconds, just standard date and time down to the second, which is perfect for most business systems.

Let’s not forget Boolean values. In Access you have Yes/No fields. In SQL Server, that becomes a bit field. One or zero. True or false.

One interesting difference behind the scenes is that Access stores True as negative one, while SQL Server stores it as one. Functionally they mean the same thing, but it is something to keep in mind if you ever inspect raw values or write cross platform logic.

Once your fields are in place, SQL Server starts organizing things a little differently than Access. Tables live inside schemas, usually dbo by default. Keys, constraints, indexes, and triggers are all treated as separate database objects rather than hidden table properties.

This is actually a good thing.

For example, constraints enforce rules directly at the server level. Required fields, default values, uniqueness, referential integrity, they all live with the data itself. That means the rules apply whether the data comes from Access, an import, or a web app. No more relying on forms or VBA alone to keep data clean.

Indexes are another big one. Think of them as performance boosters. Your primary key creates one automatically, but adding indexes to frequently searched or joined fields can turn a slow database into a warp speed one overnight.

And yes, beginners often forget to index foreign keys. I have done it myself. You will know when it happens because everything suddenly feels like it is running through molasses.

The main takeaway here is simple. Data types are not just storage containers. They directly affect calculations, compatibility, performance, and long term reliability. Choosing the right ones early saves you from painful redesigns later.

If you want to see this all built step by step inside SQL Server, the full video walkthrough is embedded above. It is worth watching just to see how these choices play out in a real table design.

Live long and prosper,
RR

Building Your First Customer Table in SQL Server the Right Way from Day One

Every database project starts the same way. You sit down, crack your knuckles, and say, "All right, let’s build some tables." Seems simple enough. But here is the thing most beginners do not realize. The design decisions you make right at the start will either make your life easier... or come back to haunt you six months from now. Today we are laying the foundation properly by building a real customer table in SQL Server, and we are going to do it the right way from day one.

When you create a new table in SQL Server, you will notice right away that it feels familiar if you are coming from Microsoft Access. You define your fields, assign data types, and configure properties. Same general idea. But SQL Server gives you more flexibility and more responsibility. It assumes you know what you are doing, so it will not hold your hand the way Access sometimes does. That means we need to be intentional about every design choice.

Let’s start with the backbone of the table, the primary key. In a customer table, that is almost always going to be something like CustomerID. In Access you would use an autonumber. In SQL Server, the equivalent is an INT field with Identity enabled. You create the field as INT, then open the column properties and set Identity Specification to Yes. That tells SQL Server to auto generate the next value for each new record. Identity Seed and Increment usually stay at 1.

One very important point here. Setting Identity does not automatically make the field the primary key. That is a separate step. You still need to explicitly set the primary key using the toolbar button or right click menu. SQL Server will not remind you. Forgetting to set a primary key is one of the most common beginner mistakes, and it can cause headaches later when you start building relationships.

Once the ID is in place, we can start adding text fields like FirstName, LastName, and Email. This is where data types matter more than most people expect. SQL Server gives you several text options: CHAR, NCHAR, VARCHAR, and NVARCHAR. The difference comes down to fixed vs variable length and Unicode support.

Fixed length fields like CHAR always reserve the full space you define. If you set CHAR(50), SQL Server stores 50 characters even if the name is Bob. Variable length fields like VARCHAR only use the space required. That makes them far more efficient for most business data.

Then there is Unicode. NVARCHAR and NCHAR support international characters like accents and umlauts. Since modern systems and global users are the norm, NVARCHAR should be your default choice. Storage is cheap, compatibility is priceless, and it keeps you aligned with how Access already stores text.

For sizing, you do not want to fall into the classic beginner trap of making every field 255 characters just because you can. SQL Server actually uses declared lengths when calculating indexes and query plans. Oversizing fields can hurt performance in larger systems. A good rule of thumb is 50 characters for names, maybe 100 if you want extra breathing room, and 255 for emails.

For large notes or comments, use NVARCHAR(MAX). This is similar to the Long Text or Memo field in Access and can store very large amounts of text. Most systems will never hit its limits, but it is there when you need it.

You will also see an Allow Nulls setting. This works like the Required property in Access. Turn it off for fields that must have data, like primary keys or required names. But do not force users to enter junk just to satisfy a rule. No data is better than bad data. You can always query later for missing information.

While defining fields, stick to clean naming conventions. Avoid spaces, dashes, and weird characters. Yes, SQL Server allows them. No, you should not use them. Every space forces you to wrap field names in brackets in queries and code. Stick with simple names like CustomerID, FirstName, and Email. Your future self will thank you.

As you explore the Tables area in SQL Server, you will see other categories like system tables, file tables, external tables, and graph tables. These serve specialized purposes. System tables manage SQL Server itself. File tables allow file storage integration. External tables link outside data. Graph tables model complex relationships. All useful, none necessary for building a basic business application. Focus on standard user tables first.

One related design tip worth mentioning. Do not store files like PDFs or images directly inside your database unless you truly need to. Whether you are using Access or SQL Server, storing file paths is usually simpler, faster, and easier to maintain. SQL Server can store files internally, but it adds complexity most beginner systems do not need.

Once your fields are defined, save the table and give it a proper name, something like CustomerT if you follow my naming conventions. You now have the foundation of your first real SQL Server table, complete with identity field, primary key, and properly sized Unicode text fields.

Getting this structure right at the start makes everything else easier. Relationships, queries, forms, reports, all of it builds on this foundation. Take the extra time now so you are not rebuilding it later.

If you want to see the full step by step walkthrough, including where all the settings live on screen, check out the embedded video above.

Live long and prosper,
RR

Microsoft Access Is Not Dying: Debunking the Myths, Limits, and Replacement Hype

Every few months like clockwork, another article pops up declaring that Microsoft Access is dead, obsolete, or being quietly replaced by something newer and shinier. And every time I see one, I feel compelled to dust off the same response. Not because I enjoy arguing on the internet, but because the narrative keeps getting repeated without much real-world context. If you actually build business systems for a living, the story looks very different.

Let’s start with the biggest misconception right out of the gate. Microsoft Access is not just a file sitting on your hard drive. It is a development platform. Treating it like a glorified spreadsheet or a single flat database file is where most of the criticism begins, and where most of it goes off the rails.

You have probably heard the famous “2GB limit” argument. On paper, yes, an individual ACCDB file cannot exceed 2GB. That sounds terrifying if you stop reading there. But that is not how Access is deployed in production. Real-world systems are almost always split databases. The front end contains the forms, reports, queries, and VBA code, while the back end holds only the data tables.

Once you split the database, that 2GB ceiling stops being a practical limitation. You can link multiple back-end files, archive historical data, or move the data entirely into SQL Server, including the free SQL Express edition. I have systems running Access front ends against enterprise SQL Server databases with millions of records. At that point, worrying about a 2GB file cap is like judging a truck’s hauling capacity by the size of its glove compartment.

Another talking point that refuses to die is database corruption. Critics love to frame Access as fragile, ready to implode the moment two users log in. There is a kernel of truth there, but context matters. If multiple users open the same unsplit database file across Wi-Fi, yes, corruption risk goes up. But that deployment model has been considered bad practice for decades.

Proper architecture uses a local front end on each workstation connected to a shared back end on a server, or better yet, a SQL Server data store. Under that model, corruption is rare and usually repairable using built-in compact and repair tools. Blaming Access for corruption in badly deployed environments is like blaming a car for engine failure because someone never changed the oil.

Then we get the “Windows-only” argument. Guilty as charged. Access is a Windows desktop application. But whether that is a limitation depends entirely on your environment. Many organizations are already Windows-based from top to bottom. Employees spend their entire day on Windows machines. In those scenarios, Access being Windows-only is a non-issue.

And when mobile or browser access is required, Access does not have to stand alone. It can connect to shared data that is also exposed through web apps, remote desktops, or Power Apps overlays. Access becomes one interface among many, not the only doorway into the data.

Security is another favorite criticism, especially around row-level permissions. Standing alone, the Access database engine has limited native row-level security. But serious deployments do not rely on Access as the primary security boundary. Security is enforced at the data layer, typically in SQL Server, using Active Directory, server roles, and permission structures.

When Access connects to a secured back end, it inherits that security model. Evaluating Access as if it must single-handedly satisfy enterprise compliance frameworks misunderstands its role in a multi-tier architecture.

You will also hear complaints about Access databases being scattered across file shares, invisible to IT governance. That happens, sure. But it is not an Access problem. Excel files get scattered. PDFs get scattered. Shadow SaaS tools get scattered. Governance is an organizational discipline, not a software feature. Properly managed environments centralize storage, enforce backups, and control deployments regardless of platform.

Some critics point to VBA and macros as a liability, arguing that systems become dependent on the developer who built them. If that person leaves, the system becomes difficult to maintain. That risk exists in every development environment on earth. .NET apps, Python scripts, SQL procedures all have the same dependency risk. The solution is documentation and standards, not abandoning the platform.

Modern discussions often position the Power Platform as Access’s replacement. Microsoft is absolutely investing in Power Apps, Dataverse, and cloud development. But investment does not equal replacement. The two platforms serve different needs. Access excels at rapid desktop development, complex form design, and offline or local-network solutions. Power Apps shines in browser and mobile deployment.

They overlap, but they are complementary tools, not identical substitutes. It is also worth noting that cloud platforms introduce subscription costs that many small and mid-sized organizations simply do not need or want.

Comparisons to SaaS tools like Airtable or Notion come up as well. These platforms are fantastic for collaboration and lightweight relational work. But they do not replace mature Access applications that rely on complex relationships, advanced queries, automation, and reporting. They solve different problems.

None of this means Access is the right solution for every scenario. If you need massive web-scale concurrency, public-facing portals, or mobile-first design, there are better tools. The software landscape has evolved. But evolution expands the toolbox. It does not invalidate tools that still do their job exceptionally well.

Microsoft Access is still supported, still maintained, and still widely deployed. For the right projects, it remains one of the fastest and most cost-effective ways to turn a business need into a working solution.

If you want to see the full walk-through and deeper discussion, be sure to watch the embedded video above.

Live long and prosper,
RR

dotnet run

dotnet run by Alex Hedley

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