r/MSAccess

Error message when logging in. Internet search turned up a GitHub reference saying it is "scBaseCount: An AI agent-curated, uniformly processed, and continually expanding single cell data repository."

I Tried "add remove" and there was nothing found but the Add Remove program also went to System> AI components and stated that "There are no AI components currently installed. "

What is the next step?

There is this persistent wish out there: "Why can't I check my Access database from my phone, without rebuilding the whole thing?" Every time this comes up, people tend to overcomplicate or look for magic solutions. Here's the reality: Access isn't getting replaced in your tech stack. But if you're hoping to get mobile access to your data, it's time to start thinking about moving your data to SQL Server and using something like Power Apps to build a lightweight mobile front end.

This is not about abandoning Access. Anyone who tells you Access is obsolete because of cloud and mobile is missing the point. Access is still one of the strongest options for a desktop front end, especially on Windows. The trick is treating Access as just one possible front end to a central database, ideally SQL Server. Get your tables out of local ACCDB files and into a solid SQL backend and suddenly you've got real flexibility. Your Access users can keep using what they've always used, and you can bolt on Power Apps, web apps, reporting tools, whatever you need. SQL Server is the real backbone, not Access itself.

Now, let's get real about mobile and web. If your only goal is to use your existing Access UI remotely, don't overthink it. Remote Desktop or a cloud-hosted Windows session works surprisingly well for most power users. But if you want a true mobile-native experience or you're keen on giving folks a clean phone/tablet front end, Power Apps is where Microsoft would like you to look. It's not just another replacement-for-Access scheme. It's more like a drag-and-drop form builder reminiscent of Access Form Design View, only pointed at cloud data.

There are a few catches. Power Apps is designed for internal business applications, not for public-facing apps. Every user needs a Microsoft account and most will need a premium Power Apps license to work with SQL Server data directly. If your idea is to publish your customer data to the world or give public kiosk-style access, this isn't the tool. Power Apps shines for employee or line-of-business stuff where accounts and permissions are expected.

Another thing: building in Power Apps isn't just rewiring your Access forms. It's a different way of thinking, much more like laying out a canvas than traditional Access form-bound controls. The logic is lighter and less code-heavy, but the learning curve is mostly about getting used to the model rather than wrangling code.

Is Power Apps the only low-code tool in town? No, there's a zoo of tools that can bolt onto SQL Server and give you a mobile or web experience. Retool, Appsmith, Tooljet, and a dozen others are out there. Some people love them. Most of us stick to Power Apps if we're already neck-deep in the Microsoft 365 world since the integration is tighter and the ecosystem more familiar.

If you're an Access developer, the best practice is to treat your backend as the center of gravity. SQL Server should hold the data, then you have options. Access remains a world-class Windows front end. Build out a Power App for mobile staff, spin up web reports with another tool, connect your reporting suite, whatever. The key is to see your database and its relationships as the core, not the forms or the bells and whistles around them.

So the philosophy here is: one backend to rule them all (thank you, Gandalf), and specialized front ends for each user type. Power Apps isn't a silver bullet, but it fits a real need for mobile business access without burning down your existing Access setup. Just don't pretend it's something it isn't.

Curious whether your scenario fits an exception? If you're all-in on the Power Platform, maybe Dataverse is worth a look, but SQL Server will always win on flexibility and long-term viability.

Wherever you land, keep your data centralized, avoid split-brain architectures, and adapt your front ends to your actual user environments. That's durable design, and it applies to Access, Power Apps, or whatever comes next.

What do you think? Are you using a hybrid solution? Share in the comments.

LLAP
RR

Hello,

is there a way to get an autonumber assigned to each row, but then turn it into a string with leading zeros, so that it is always the same length, that I can potentially concatenate with some prefix or suffix?

I managed to do prefix + autonumber in a calculated field, but it doesn't have leading zeros and the calculated field simply does not take in the Format() function.

The goal is to maintain the reliable non-duplication of autonumber, but be able to work with it like a string and be able to sort it alphanumerically.

Hi all,
I managed everything in spreadsheets using wps office and it worked fine but recently my files are getting bigger and it’s hard to manage. I’m thinking about trying Microsoft access. Just wondering is it worth learning access? Really love to hear your experience.

I’m currently using excel sheets to manage a members list of the company I work for and they wanted to change to a more optimized database. I wanted to know if access could be that database. Some things to keep in mind is that I often work with seniors so I’d like to keep it simple. Thank you!

Normalization might be one of the most over-discussed and misunderstood topics in Access circles. The second someone mentions first normal form, second, third, or their imaginary 85th normal form, half the room's eyes glaze over. There's this myth that you have to have a computer science degree just to build a solid Access database, as if you need to memorize a stack of academic rules and hand in a paper to Dr. Codd. That's just not the case for real-world Access work.

The heart of normalization is dead simple: every table should be about one thing. Not two things, not five, definitely not "customer-and-order-and-everything-else." If you've got customers, keep customer stuff together, orders as orders, products as products. Don't shove everything into one messy table and call it a day. Yes, this is "first normal form" territory, but you don't need to quote the definition in a job interview.

Next up: duplication is your database's arch-nemesis. If you keep copying the same customer details onto every invoice, appointment, or order, you are not normalizing, you're just asking for trouble. When a customer's email changes, how many places do you want to fix it? Store each fact once. Link it everywhere it's needed, but only store the actual data in one spot.

Now for the classic rookie move: "I need to store up to 10 phone numbers for a customer, so I'll create phone1, phone2, up to phone10 columns." Please, in the name of all that's relational, don't do it. If there's any possibility someone might need more than, say, three items (like phone numbers, emails, addresses), it belongs in a related table. One row per thing, not one column per thing. Think "one to many," not "one to wide."

Look, most Access projects never need more than this basic discipline. If your tables are about one single subject, you aren't duplicating info, and you're handling repeating data with real relationships, you are already doing a better job than most spreadsheets masquerading as databases. The deep theoretical levels of normalization are great if you're building high-volume, transaction-heavy financial systems, or trying to pass a university exam, but for 95% of us, this is how you build a robust, low-stress database.

Are there exceptions? Absolutely. Sometimes, for history or reporting reasons, you denormalize and copy a shipping address onto an order so you have a historical snapshot even if the customer moves. That's not bad design, it's just practical. Use your judgment, not dogma.

A big mistake people bring over from Excel is thinking redundancy is normal. It's not. That's just the spreadsheet mindset leaking into relational design.

If you walk away with anything, it's this: don't get paralyzed by normalization theory. The actual rules come down to a much more practical philosophy. Model your data so it reflects the real-world relationships. Store each fact once. Use related tables instead of cramming repeating stuff into new columns (or into the same column - I'm talking to you, Multi-Valued Fields). If you can do that, your Access database is going to serve you well, and you're already more normalized than most people in the wild.

It can be easy to get lost in the swirl of academic jargon about normal forms. The reality is, clean, practical design serves businesses better than theory and textbook lingo. Discussions about whether you've hit fifth normal form or whatever are useful mainly to database theorists. For actual day-to-day Access developers, keep it simple, keep it clean, and solve real problems.

So, what's your take? Do you find normalization theory useful in your day-to-day, or is it just something you quietly ignore unless a database goes off the rails? Let's hear your stories about messy tables, normalization wins, or the time you inherited the beast from Excel.

LLAP
RR

I’m trying to learn Microsoft Access and I want to go beyond just basic tables and queries. My goal is to build proper applications with nice-looking interfaces (like dashboards, forms, navigation menus, etc.). I couldn't find anything. May you help me?

I have a table that I need to delete duplicates from. The number of fields that need to be checked is way more than 10 and includes 2 long-text fields. To start, I used a select query to concatenate all the data that needs to be checked for duplication into one long-text field and then used a make table query to split each of those concatenated fields into 255-character chunks (which, thankfully, is under 10 chunks). Then I used the Query Wizard to do a find duplicates query for those chunks. Because I’m only looking for truly exact duplicates in terms of the combination of those fields, this seems to do the trick.

What I’m struggling with now is figuring out how to accurately delete the duplicates from the original table now that I’ve identified them. My original table has an auto-number field that I also carried through all the steps to the find duplicates query (just as a reference—I didn’t check that field for duplicates, obviously). So the idea was to populate a delete query with the auto-numbers of all of the duplicate values. However, I can’t figure out how to parse the duplicate wizard query to just select the auto-numbers for the 2^(nd), 3^(rd), 4^(th), etc. instances of duplicates and ignore the first instance. I feel like there should be a simple answer to this, but I can’t figure it out.

I can provide code for any of the existing queries if requested.

Thanks!

Access should have some global events, such as OnAnyFormOpen or OnAnyError.

Here's another one for the beginners in the group who are looking to add some automation to their Access databases, but have zero desire to "nerd it up" and learn how to code.

Macros are one of those Access features that don't get talked about very much anymore because most experienced developers (like most of you in this subreddit) jump straight into VBA. But if all you want to do is automate a few repetitive tasks like opening forms, running queries, printing reports, or stringing a few actions together with a single button click, macros are often exactly the right tool for the job.

Here's the thing a lot of Access users get wrong: when you mention macros, most folks immediately picture the Excel variety - the old "hit Record Macro, twiddle some buttons, stop recording, and watch as VBA code unfolds in the background" trick. Somewhere between that and watching reruns of The Next Generation, they assume Access macros are the same creature. They're not.

Access macros are a completely different animal. There is no recorder. There is no VBA lurking under the hood. Instead, macros in Access are a stack of predefined actions that you arrange in the interface to run in order, like a to-do list for the database. Open a form, run a query, show a message, open a report, export something, whatever. You pick your actions, give them an order, and Access will run down the list. It's automation, just not the programmable kind.

Now, who actually benefits from macros, and why do they matter? Here's the reality: the people who get the most value from macros are usually not trying to become programmers. (Caveat: If your end goal is serious development or custom logic, VBA is still your warp core.) Most macro users are people who built a database themselves, inherited someone else's half-baked schema, or just want to automate the drudgery - opening forms, running reports, prepping a mailing list, importing yesterday's sales, stuff like that.

The best part about macros: you don't need to know code. You don't need to debug a wall of unfamiliar VBA. The list of actions is right there, and you just stack them up: OpenForm, RunQuery, ShowMessage. If you've used the command button wizard, you've actually already brushed up against the concept. But the wizard can only do one action at a time. The moment you need a sequence of actions - three queries, a report, and a confirmation message - you're stepping onto macro territory.

It's not about loving automation for its own sake either. Most macros exist because database users get tired of clicking the same buttons every month. If Bob in accounting is still manually opening the Sales form, running three queries, then exporting a report to Excel, that's a strong argument for handing Bob a macro and reclaiming at least a sliver of your own sanity. Bonus points if you get to explain to your boss that their fancy workflow now takes two clicks instead of twelve.

That said, it's easy to fall into the trap of thinking macros are just training wheels for beginning users. The truth is, macros sometimes do what VBA can't, especially around database startup. For example, the old AutoExec macro is still the best option for triggering tasks the second a database opens, including startup logic like checking trusted locations - all before VBA is even allowed out of bed. There are still edge cases in security models or very early lifecycle events where macros are safer, or the only way.

One important gotcha: macros are powerful but blunt instruments. If you need advanced conditional logic, loops, complex validation, external data handling, or anything that generally starts with "I wish Access could just..." you probably need VBA. Macros don't scale to advanced application logic, but they're not meant to. They're for routine multi-step grunt work, not for building the Starship Enterprise.

The real philosophy here: Use macros for repetitive, predictable automation - especially for tasks that don't need complex business logic. Don't over-engineer macros into pseudo-programs. When you hit the complexity wall, that's your signal to move to VBA. Until then, let macros take care of the boring stuff and leave your dev time for challenges that actually need a human brain.

I'm curious: What annoying, repetitive Access tasks have you managed to banish with macros? Where did they let you dodge the bullet of writing yet another VBA sub? Or do you still default to VBA for everything out of habit? Would love to hear the hacks and the horror stories.

LLAP
RR

So here's an old chestnut in the Access world: Someone's ripping through a batch of customer records, realizes three edits later they typo'd something, and suddenly they're frantically jamming Ctrl+Z like it's 1998 and they're in Word. Then… nothing. No rollback. The bad edit just laughs at them from the form. Cue the existential dread.

It's a common misconception that Access handles Undo anything like Word, Excel, or other Office apps. But once you've been in the trenches with Access for a while, you learn pretty quickly: Access's Undo is more of a fire extinguisher than a time machine. It'll douse that last change if you react fast, but it won't let you travel through every past edit with the grace of a revision historian.

Let's get into why. Access isn't a document editor. Every time you shift focus, or especially when you move off a record, you're pushing data into the table. That change is usually permanent. The built-in Undo will undo the last thing you typed in a field, or the whole record if you're still on it and you haven't moved away yet. Once you leave the record and start futzing around with other records, your Undo opportunities mostly slam shut.

People get tripped up because the interface feels a lot like the rest of Office, and Ctrl+Z is a muscle memory for nearly everyone. But databases just have different priorities. Reliability of data, transactional safety, shared multi-user editing - all that takes precedence over luxury features like infinite Undo. That nifty Undo stack in Excel? Think of that like Vulcan mind-meld magic compared to Access's approach.

So in practice, the best you can expect in native Access is:

• Undo the current field if you're still typing in it
• Undo the whole record if you haven't saved/moved off yet
• Maybe, and this is a big maybe, undo the most recent saved record in certain situations, but don't count on it

Multi-level Undo, with proper history, just doesn't exist out of the box. Redo is even more flighty - sometimes it appears, often it doesn't, and never when you really need it.

Now, could you build a more robust Undo system for your app? Absolutely, if you log every change in a custom table and roll your own VBA logic. Think of it as developing your own mini time-travel feature. It's not trivial, but it's 100 percent doable for advanced business apps where audit logs or compliance demands it. You'd need to record old values, new values, timestamps, user IDs, and pretty much manage your own Undo and Redo stacks. For most forms though, it's overkill - and unless you absolutely require it, you're better off focusing on data validation and error-prevention designs.

The tough-love takeaway: If you're looking for comfort-food Undo, this isn't the right kitchen. Access expects you to be deliberate with data changes. If you make a mistake, spot it fast, or plan for versioning and history using custom logging. Don't build for casual undo-fests. Build for traceability, accountability, and data confidence.

So here's a genuine discussion question for the floor: If a form needs multi-level Undo, is it ever for the right reason, or is it usually a bandage over a UX or process problem? How far do you go before it's better to just train users or rework your form logic to avoid the oopsies in the first place? I'd love to hear how people have handled this conundrum, especially in regulated verticals.

LLAP
RR

Hey guys, How do you sort a Field in Report Layout View (I can only see A-Z options)

Like In Excel: Where you can sort list With Longest Name as First and Shortest Name at Last by using the formula =Len(Cell Reference)