Weird one. Starting today, I can't paste into the SQL editor when creating a brand new query in Access.

Steps:

• Create > Query Design
• Close the Show Table dialog
• Switch to SQL View
• Ctrl+V, right-click paste, and drag-and-drop all fail (shows the 🚫 cursor)

I can still type SQL manually and run it just fine.

Even stranger, if I manually type a query, save it, close it, and then reopen it, I'm able to paste into it normally. The problem only happens with fresh/new SQL queries.

Clipboard works everywhere else in Windows and Access is fully up to date (Office Professional Plus 2019 / Access 2016, Version 2606).

Has anyone run into this before or know what might cause new SQL queries to reject paste operations?

Weird issue that just started today.

When I create a brand new SQL query:

• Create > SQL Query
• The "Show Table" dialog pops up and I click Close.
• I try to paste some SQL text.

Nothing gets pasted. My mouse cursor briefly shows the blue spinning circle for about a millisecond and then nothing happens. If I spam Ctrl + V, eventually I get an error saying:

What's strange is that I can still type SQL manually and run it without any problems.

If I manually type a query, save it, close it, and then reopen it, I'm able to paste into it normally. The issue only happens when creating a fresh/new SQL query.

Clipboard works everywhere else in Windows 11, and Access is fully up to date (Office Professional Plus 2019 / Access 2016, Version 2606).

Has anyone seen this before or know what could cause new SQL queries to reject paste operations?

So, you want your Access database humming along in the cloud, wired up to a SQL Server backend that users can hit from anywhere with an internet connection (or, let's be real, your co-worker with an unreasonable passion for Excel pivot tables). The thing is, plenty of folks make this sound like some arcane ritual involving complicated wizards or flaky migration tools. But the real trick isn't wizardry. It's understanding what's actually going on under the hood, what goes right, and, of course, what tends to go annoyingly wrong.

Let's get one big misconception out of the way first: moving Access "online" isn't just about flinging your tables up to SQL Server and calling it a day. The architecture really matters. When you connect Access to SQL Server, you're making Access into a front end talking across the network (or the actual internet, which has its own lovely collection of latency quirks). Access becomes the client, SQL Server is the data store, and now every form you open, every query you run, depends on that network connection.

The reasons for doing this are legit: multi-location teams, remote work, centralized backups, or meeting regulatory requirements. Just don't be fooled into thinking your old single-ACCDB app will magically scale once you check the SQL Server box. There are design and architectural consequences galore.

ODBC is where a lot of folks stumble, specifically the difference between machine DSNs, file DSNs, and DSN-less connections. Machine DSNs live on a specific PC, which can make deployment a pain. File DSNs move the connection information into a separate .dsn file that can be shared across machines. These days, however, many developers skip both approaches and use DSN-less connection strings instead, allowing the application to create or refresh links programmatically without requiring any DSNs at all. Pick the approach that best fits your deployment strategy, but understand the tradeoffs.

But let's talk linking. When you upload an Access table to SQL Server, you're exporting the definition and contents, then linking back to it as an external table. Sounds simple, but this is where a lot of Access veterans get caught by SQL Server's more rigid data types and behaviors. Field types translate... ish. Short Text becomes nvarchar, Yes/No turns into bit, and so on. If your schema makes heavy use of Access-only features like Attachment fields or multi-valued fields, expect some translation headaches. You shouldn't be using these anyways, as they're on my Evil Access list. But that's a different article altogether (coming soon).

Also, for the love of Gygax, don't blindly trust SQL Server Migration Assistant to get everything right if your schema is even mildly complex. It's convenient for large migrations, but it can miss details or leave you with subtle mismatches. Manual review and testing almost always pay off.

Choosing to store your SQL Server credentials is another classic debate. Saving the password in the linked tables is certainly convenient, but those credentials should not be considered securely protected inside your Access file. Anyone with sufficient access to the frontend may be able to recover them. That might be acceptable within your own organization, but don't hand out your frontend ACCDE willy-nilly unless you're comfortable with users having direct access to the backend. Use SQL Server security properly, assign appropriate permissions, and keep your frontend cleanly separated from your data. And remember: hiding the Navigation Pane is a convenience feature, not a security feature.

Personally, I've often stored the SQL Server credentials in VBA and distributed only ACCDE files. That's not bulletproof security, but it does avoid storing the credentials directly in linked table definitions and raises the bar for casual users. The downside is that if you ever need to change those SQL Server credentials, you'll have to redistribute updated frontend files to everyone.

And yes, before a few of you chime in, I know ACCDE files aren't impossible to crack. With the right tools and enough expertise, determined attackers can still get at the code. But that's a far cry from Joe in Accounting accidentally discovering your SQL Server password while trying to print the quarterly TPS reports.

A more flexible approach is to authenticate users through your own web API. The user logs in with an application username and password, your server validates those credentials, and then returns the SQL Server connection information to the frontend over an encrypted HTTPS connection. That allows you to rotate SQL Server passwords without redistributing your application. You can even provision separate SQL Server accounts for individual users if your security requirements justify it, although managing that many accounts can become a project in itself. There are even more secure architectures that avoid exposing SQL credentials to the client altogether, but that's a much bigger discussion for another day.

There's another misconception that performance in the cloud will be "about as good" as running everything on your local network. Not quite. High-latency connections change the game. Modern versions of Access do an excellent job of pushing filtering, sorting, and other operations back to SQL Server whenever possible. The query optimizer has improved dramatically over the years. But it isn't omniscient. Complex expressions, VBA functions, certain joins, aggregates, and poorly optimized queries can still cause more work to happen on the client than you'd like.

That's why learning actual SQL pays dividends. Use pass-through queries when appropriate. Push filtering to the server whenever possible. Create SQL Server views for commonly used datasets. Only retrieve the data you actually need over the wire.

And don't neglect indexing. Good indexes are worth their weight in gold-pressed latinum. They're just as important on SQL Server as they were in Access, arguably even more so. A poorly indexed table over a high-latency connection is an excellent way to discover new definitions of "click the button and take a coffee break."

If you're designing new SQL Server tables, consider adding a rowversion column. Access doesn't require it, but it can make concurrency handling and change detection much more reliable in multi-user environments.

In practice, linking Access to SQL Server online works extremely well for thoughtfully designed applications where you control the flow of data. If you treat SQL Server as simply "Access on the internet," you're signing yourself up for confusion, frustrated users, and unnecessary performance problems.

Best practice? Design your frontend to minimize chatty traffic. Understand what ODBC is actually doing. Build good indexes. Review your schema after migration. Test with realistic network conditions instead of just your gigabit office LAN. Manual control, not blind faith in migration tools, usually gets you further with fewer surprises.

It's not that running Access with SQL Server online is a bad idea. Quite the opposite. It can be an outstanding solution when it's done thoughtfully. Just understand the moving parts, and you can absolutely build sustainable, real-world applications.

Leave the magic to Gandalf. Database design isn't wizardry. It's engineering. When it works, nobody notices. When it doesn't, everybody notices.

Would love to hear from anyone who's navigated the odyssey of remote Access backends, especially with dozens (or hundreds) of users. What's bitten you? What worked? Any horror stories to share? Do you have any super cool fancy ways of authenticating your users that I didn't talk about? I'd love to hear them. Post them in the comments.

LLAP
RR

Hi everyone, I’ll give you a brief overview of my situation. I’m currently pursuing a degree in Archival Studies / Archival Science at the National Autonomous University of Mexico (UNAM). During my internships, I was asked to create a database in Microsoft Access to keep track of the files, I have a basic understanding of what’s involved—things like primary keys and entity relationships—but I’ve never actually created one before. My question is: does anyone know of a tutorial that explains how to create databases in an easy way? Best regards to everyone

I remember back in the day there was a recommendation to put the data in one Access database and the front end stuff like forms, reports, etc in another and connect them. Is that still the recommended practice?

Also, does Microsoft still do major development for Access or nah? I feel like the last time Access was relevant was like 2005, no? After that, SQL Server took over, then other stuff emerged, etc..

So the problem is everytime I try to change the db format to 97 or try to open the 97 format db the system ask me for the password which I have never set it before? Are there anyone who are facing the same problem as me? How can I fix this issues since I asked ai about it before and it didn't help me quite well so. You can see the attached photo for example.

Few things poke the bear like opening your Access database and realizing it's stuck in read-only mode. You'd think there was a forcefield blocking your edits. The classic panic: is my database corrupted, did I mess up a setting, or is the universe simply out to get developers today?

The reality is, "read-only" in Access usually isn't a sign of anything irreversibly broken. More often than not, it's a side effect of one of a handful of mundane (and occasionally hilarious) missteps. That said, there's a lot of confusion about what triggers read-only states and what Access is actually protecting you from.

First, a lot of people overlook the obvious: sometimes you asked for it. Access has a few different ways to open a file: normal, read-only, exclusive, and so forth. If you use File > Open and pick the wrong option in that little dropdown, you can land yourself in read-only mode by accident. Happens to the best of us. This doesn't usually need a deep-dive Windows-detective investigation. Make sure you didn't just pick the 'look but don't touch' setting by mistake.

But it's rarely just about how you open the file. File attributes are the next classic culprit. Windows can mark a file as read-only at the filesystem level. Sometimes this happens when you copy a file from another machine, or when you download from email or a network share. Even Grandma copying files around on the family NAS can sneak up and flip that bit for you. A quick trip to File Explorer's Properties will tell you. And while we're on it, Windows loves to add "blocked" status to files from downloads or email, which can add another layer of drama.

Here's where Access's design starts to show its age. For multi-user scenarios, when anyone opens a database, Access creates a lock file (.LACCDB) in the same folder. If Access can't create or alter that file because the folder is permission-locked, your database goes read-only as a defense mechanism. So you need write permission to the folder too, not just the file. Corporate IT loves setting these "helpful" permissions, and they come back to bite Access users all the time.

Network drives? Even more fun. If a colleague opens the database in exclusive mode, or leaves a form in design view, everyone else gets the door slammed in their face or gets downgraded to read-only. Sometimes Access doesn't even need a fellow Access user to cause trouble. Backup utilities, virus scanners, Excel with external data connections, and the denizens of Dropbox or OneDrive folders can lock files just long enough to make Access think someone else has dibs. This is why Access and file-synced cloud folders is basically a 'here be dragons' situation.

A less common but supremely annoying problem: orphaned lock files. If Access or Windows crashes hard, that .LACCDB file can get left behind. Access sees the lock and assumes the database is still in use, even if everyone's out. Removing a lock file is simple, but you have to be certain absolutely nobody has the database open, or you can bork an otherwise intact file.

Let's not skip extensions, because there's a conceptual misunderstanding that trips up more than a few. If your file is an .ACCDE, that's a compiled runtime version. It looks and feels like Access, but you're not going to see design view or VBA. That's not a malfunction, it's the whole idea. Don't try to reverse-engineer your way in unless you like pain or you're embracing the Sith path.

Beyond that, consider good old corruption, compact and repair routines, and database trust locations. These are rarer but important, especially on legacy files with long histories or lots of linked tables pointing to network resources where permissions shift daily, like tribbles multiplying on the Enterprise.

So what's the real takeaway here? Read-only in Access is far more about environment and permissions than about Access itself misbehaving. It's almost always Windows or your network setup quietly locking things down to "protect" you. That, or a simple mistake in how you opened the file. Access is conservative when it detects any hint of trouble and defaults to protecting the data, even at the expense of developer frustration.

Best practice: before you even think about restoring backups or suspect deep corruption, check the basics. File properties, folder permissions, lock files, exclusive flags, file extensions. Don't run your live database out of a cloud-synced folder, and remember real multi-user setups require careful ops - not just sharing the same file and hoping things "just work."

If you've run into a weird scenario that stumped you for too long, share it below. And yes, sometimes the answer is embarrassingly simple. We've all been there.

LLAP
RR

Hi everyone,

i'm a beginner with Access and VBA and i've been having a problem with my database:

sometimes changes to existing recordsets are not saved.

1. Background Info:

- Split Database, backend and frontend are on a server.

- users have a launcher-file which creates a local copy of the frontend.

- the main form frmNavigation contains a subform-element sfrmMain.

- Users navigate with buttons in frmNavigation which load subforms into sfrmMain

(Me.sfrmMain.SourceObject = sfrmX).

- Changes are applied in these subforms within sfrmMain.

- frmNavigation and the subforms are bound

- at this point no new recordsets are created, only reading and editing existing ones

2. The issue:

afaik Access is supposed to save changes whenever a new subform is loaded or when users change the recordset, right?

And that mostly works, however: It seems that the drive where the database is saved is on a... let's call it potato-server? Speed is not great and the connection less than stable.

There are frequent time-outs which cause changes to get lost.

I'm not IT and have no insight into (or clue about) any technical specifications of that server. This drive and this server are the only option as of now.

3. What I tried:

My soulution for now was to force a save with a button:

        If Me.sfrmMain.Form.Dirty Then
           Me.sfrmMain.Form.Dirty = False
        End If

But apparently that didn't do the trick, because today a user reported that she frequently used the save-button while applying changes to a recordset. After a while she recieved an error message (server-connection lost) with a prompt to restart Access. After doing so, she found all her previous input gone (despite "saving").

But even if the code didn't work, the loss of focus by clicking outside the form should have forced a save, right?

I have no idea what to do. So I'm kindly asking you guys for help. Any ideas as to what i can do about this?

(Apologies if this has been answered before. Save issues seem to be a frequent topic, but I didn't find anything fitting this case)

So… some of my co workers do not have access installed. I have been exporting tables to excel and opening them in wps office before sharing them. Just wanna know… Is there a better workflow that people generally use?

I have an old database where the main table uses manually assigned record numbers, and no proper primary key. I want to change the record numbers to be the primary key (so automatically assigned as new records are added, no risk of duplication), however there are likely to be duplicate numbers already existing in the data.

Will assigning a primary key cause issues for legacy data?

Hello,

I'm trying to create a named macro at the database level, not just tied to a single table.

If I create the named macro from the table, create parameter is right there. If I create the macro, so it's in navigation pane under "Macros", it's not

I'm new to the access data macro editor and I'm trying to create parameters for such macro, but any sort of "create parameter" or "add parameter" command seems to have been re-stylized into oblivion.

Does anyone have any idea where to find it on version pasted below?

Microsoft® Access® for Microsoft 365 MSO (Version 2605 Build 16.0.20026.20166) 64-bit

Hello!

I would like to know the easiest way to migrate an .adp project created in Access 2010 so that I can use it with Access 2024 and give it a much more modern user interface. I’m not an expert in programming or coding.
Our POS system was built in Microsoft Access and is connected to SQL Server. I’d like to know what the best approach would be to create a more modern and elegant UI for our store while continuing to use our existing database.

Also, do you know of any online courses or training that could help me learn how to expand and improve our POS system?

Thank you!

If you have worked with Access for any substantial amount of time, sooner or later you are going to get hit with that infamous message: Automation Error Catastrophic Failure. Sounds like your database just nuked itself off the map, right? Relax. It almost never means what it says. Microsoft's flair for dramatic error messages is alive and well.

What this cryptic error is really telling you is, "something weird happened, and I have no idea how to explain it, so here you go." The usual culprit is not your logic or painstakingly-crafted VBA. It is almost always the compiled "pcode" Access uses behind the scenes to run your VBA faster. When that compiled copy gets corrupted, goes out of sync, or just gets moody after an Office or Windows update (or Access didn't have its coffee this morning), you get this scary-sounding error.

The easiest fixes are also the ones people tend to overlook or dismiss as too simple. First, make a backup. Yes, even if it is already broken. No, you are not immune. Next, close out of Access completely. Check Task Manager for any leftover zombie msaccess.exe processes. Restart Windows all the way, not just a fake reboot. When Office apps lock horns behind the scenes, a full restart often calms things down.

If your database still refuses to cooperate, try a compact and repair. That solves a surprising number of weird behaviors. If you are still stuck, it's time to decompile. All that does is throw away the bad pcode and force Access to rebuild a fresh compiled version from your source. Most of the time, decompile fixes these bizarre issues right away, and people sing the praises of the Access gods (or the Bajoran Prophets, or Paladine, or Thor, depending on your fandom). Always make another backup at this point so you never have to fight this battle again.

If things are still borked, now is when you check references in the VBA editor for anything marked missing, especially if you are dealing with imported objects or old ActiveX controls. Broken references are like tribbles: ignored long enough, they cause chaos everywhere. Sometimes it is just a single corrupted form or report, so replacing that object from backup can clear things up. And if all else fails, create a new blank database and import your objects one at a time to find out which one is tripping the alarm.

The important philosophy here is not to panic\. Access catastrophic failures are rarely actually catastrophic. The overwhelming majority of these cases are recoverable, provided you follow the basics: backups, patience, compact/repair, and the occasional decompile (and if you don't know what */decompile** is, Google it). All that drama in the error message is mostly a smokescreen for what is usually just a finicky pcode problem, not the definitive end of your database as you know it.

If you take one lesson from this: treat backups like airlocks on a starship. Not optional. Without them, a minor hiccup becomes an actual disaster. With them, even if the Borg show up, you can still recover Picard. Although I guess in the age of transporters, you really don't need airlocks anymore, do you? Makes one think. But no, your backups aren't optional. Backup, backup, and backup again.

Most of us have been through this cycle enough times to know that working calmly through the usual suspects almost always brings Access back to life. No need for panic, just a solid troubleshooting mindset and the knowledge that catastrophic failure is rarely as dire as it sounds.

LLAP
RR

\* Telling someone not to panic is like telling someone to calm down. Never in the history of calming down has anyone calmed down from being told to calm down.

Hello everyone,

I know Access is a bit outdated for the form design and styling, but I think you might be able to guide me a little bit.

You can see on my printscreen what my list designs usually look like on my app right now. I've added 2 examples where the fields are not as separate, but the information is still well organised.

I would really appreciate any tips that you have to make your forms look a bit more modern!

Thanks, and happy building

Hello I'm currently expanding my forms site and I have a button that is linked to a hyperlink which is on my c drive.

It's a .doc word file .

Everything fine until there, BUT as I said I want to expand it in order to open different .doc word files which is also in the same folder and everything i get a security issue Pop up which leads me to open the file after hitting open.

The problem is the file is path is already via trust center in trusted path

I literally copied the file and changed it from the original which has no security warning whatsoever.

Please I can't find the issue..

Has someone experienced it or am I forgetting something.

I have a Microsoft Access (.accdb) database used to manage numbered plots/sites. The table currently contains records for plots 1–300, but I need to expand it to 500.
The problem is that after record 300, the database doesn’t already contain any additional records. I want to add records 301–500 while keeping the same structure, default values, calculated fields, forms, and everything else working exactly as before.
The database has only one form with a small amount of VBA code, and I couldn’t find any hardcoded 300 limit in the code. It looks like the table itself was simply pre-populated with 300 records.
What’s the best way to generate records 301–500 automatically in Access instead of creating them manually? Ideally using SQL or another built-in Access feature.

I forgot to add, I just got to know Access today and I don't understand anything about it.

Hello,

In the image above, you can see a way I track a tree-like structure in Excel, that I'd like to make an equivalent of in Access.

Now the way excel does this is:

• Check whether the parent ID is empty
  • Yes = it is a root object and it's path is just its ID
  • No = it checks what it's entire path is and concatenates it before the current obect's ID, that way avoiding some nested nightmare of a function, that would have to check the parent for its parent ID and the parent's parent... etc.

In Access, I tried to play around with calculated fields, but I don't know how to make this distinction between current record vs one of all other records with a corresponding value, and to my understanding, Access just isn't even built for that at all.

So maybe, it'd make more sense to go the the route. That being some sort of recursive query, that goes after Parent > Parent's Parent > Parent's Parent's Parent... until it hits root, but I don't know how to do that, or if it's as unfeasible idea as the one above.

Hi all.. I'm curious how many people here use microsoft access together with another office suite instead of relying on one ecosystem.
I use wps office for everyday stuff like docs and spreadsheets because it suits my workflow while i use access when i need a proper desktop database.
For those who work with both have you run into any issues importing .xlsx files or linking data between them??

Here's something I keep seeing trip up Access developers: the idea that putting your database online by upsizing to SQL Server is either a magic bullet or complete overkill. The truth sits somewhere in the middle, and plenty of people misunderstand what moving an Access backend to SQL Server, and hosting it online, actually means for real-world work.

Let's get this out of the way: if you have users who need to access your Access database from more than one location, you're going to run into problems fast with a simple file share. Whether you've got employees working from home, people on the road, or multiple offices, a back-end ACCDB hanging off your company VPN is clunky, slow, and full of headache potential. Data corruption, performance bottlenecks, limited user connections, "who forgot to close Access?" drama... you know the drill.

Connect Access to SQL Server online and suddenly your backend lives on infrastructure designed to handle concurrent connections, security, reliability, and accessibility from just about anywhere with an internet connection. What gets lost in translation, though, are the tradeoffs. People assume they'll get the same performance (or better), that the migration is just a technical checkbox, and that nothing about their application has to change. Not quite.

One of the biggest advantages is flexibility. Authorized users can work with the same data whether they're in the office, at home, or halfway around the world. Free Access Runtime can connect to it just as easily as the full version of Access, and other applications can work with the same data too. Excel, custom web applications, mobile apps, and even Macs can all connect to SQL Server with the appropriate tools.

Pass-through queries become a great tool for performance because they let SQL Server do the heavy lifting before sending results back to Access. Modern versions of Access are also much smarter than they used to be about working with linked SQL Server tables. With properly indexed tables and well-designed queries, Access does a good job of pushing work to SQL Server instead of blindly pulling back entire tables. That said, for complex joins, large reports, aggregations, or other data-intensive operations, pass-through queries can still provide a noticeable performance boost by letting SQL Server do the work natively.

Forms and reports that work perfectly on a local network may need a little tuning when the data lives across the internet. Usually that means filtering records sooner, returning only the data you actually need, avoiding unnecessary round trips to the server, and letting SQL Server handle more of the heavy lifting when appropriate. Good database design matters even more when your users aren't all sitting in the same building.

Security isn't a freebie either. Once your SQL Server is reachable over the internet, security becomes much more important. The good news is that SQL Server gives you far better security options than simply sharing an Access backend file on a network. Use strong authentication, least-privilege user accounts, encrypted connections, and don't hard-code credentials into your application. A little planning here goes a long way.

On the subject of resilience, moving your backend to SQL Server means your data isn't going up in smoke if someone microwaves fish in the server room. But your biggest dependency becomes your internet connection. That's true whether you're working from home or sitting in the office. If nobody can reach the server, nobody's getting any work done. A backup internet connection, whether it's a second ISP or even a phone hotspot in an emergency, can be worth every penny.

When it comes to management overhead, a good hosting provider handles backups, operating system patches, hardware maintenance, and much of the infrastructure work for you. You give up a little control, but unless you enjoy troubleshooting SQL Server at three in the morning, that's usually a trade most people are happy to make. I want to be busy working on my database and my business, and not patching SQL Server.

There are gotchas. SQL Server doesn't support Access-specific features like attachments or multi-valued fields, so those will need to be redesigned. Hyperlink fields can still be stored as ordinary text, but you'll lose some of Access's built-in hyperlink behavior. Honestly, if you're building an application that's meant to scale beyond a local office, you probably shouldn't be relying on those features anyway.

From a licensing standpoint, only the developer needs a paid copy of Access. Everyone else can use the free Access Runtime. And if some users don't need the full Access interface, you can always build a simple web front end that lets them do exactly what they need without exposing the entire database.

Do you always need SQL Server online? Of course not. If you're running a small office with a couple dozen users, everyone's on the same local network, and your current setup is working well, a split Access database is still a perfectly valid solution. If only one or two people occasionally need remote access, you might not need to migrate at all. A remote desktop solution can be a simple and effective alternative. For example, if your sales rep Bob just needs to check the database while he's on the road, you can leave his office PC running and have him connect to it remotely using something like Chrome Remote Desktop. That's exactly what I do when I travel. But once your users are spread across multiple locations, working remotely every day, or you simply want a more scalable and reliable backend, SQL Server starts becoming a very attractive option.

The important thing is understanding the tradeoffs. You may need to rethink parts of your application for performance. You'll definitely want to take security seriously. And while the migration is often much easier than people expect, it isn't completely "lift and shift."

What you're really doing is changing where Access fits into your overall architecture. Instead of treating it like a file-sharing database, you're turning it into a professional front end for an enterprise-class database server. That's one of the reasons Access remains such a powerful development platform even after all these years.

Could you stick with a shared ACCDB forever? Sure. You could also keep using your TRS-80 Color Computer because "it still works." Sometimes technology moves forward for good reasons, though. That said, there's no rule that says you have to migrate to SQL Server. I still have clients running Access databases that I built back in the 1990s, and they're still doing exactly what those businesses need them to do. If your application fits your needs, there's nothing wrong with leaving it alone. But if your business grows, your users become more distributed, or your requirements change, Access gives you a path to grow without having to throw everything away and start over. That's one of the things I've always loved about Access. It's remarkably scalable.

One final point that often gets overlooked: SQL Server doesn't replace Access. It replaces the Access backend. Access remains the front end, and in my opinion it's still the best rapid application development tool ever created for Windows database applications. Forms, reports, VBA, and the overall development experience are why so many of us continue building Access applications decades later. SQL Server simply gives Access a more powerful place to store and manage the data when your application outgrows a shared ACCDB file.

I'd love to hear your experiences. Have you migrated an Access application to SQL Server? What worked well? What surprised you? Any horror stories, clever workarounds, or tales of IT departments making life unnecessarily difficult? And if you've somehow managed to connect to SQL Server from the depths of Moria, I definitely want to hear that story.

LLAP
RR

I am creating a genealogy database, and I have everything in place except for a pop-up form that displays the marriages of each individual after clicking on a View Marriages button.

The source material is a genealogical record book that goes back to the late 17th Century, and some of the information is fragmented (e.g. only the month or the year is listed for things like births, deaths, and marriages -- that's why I have separate fields for month, day, and year).

I have two marriage tables: MarriagePersonT and MarriageDataT, referenced in the attached image. I realize now that I probably could've just had one table with HusbandID and WifeID fields (there weren't any same-sex marriages listed in the book), but there are over 8,000 names, and a re-do would take too long.

I set up a MarriageQ query (top part of the attached image), and the data linked together smoothly when I ran it, but I can't figure out how to set up the pop-up marriages form that will display the information I want.

I made a rough mock-up image to give an idea of what I want displayed on the form (I can adjust color, font size, field position, etc. later, and I can also concatenate the date info together -- I just put a "Marriage Date" field with full dates as a placeholder). The main thing is that I need to know what to do so that the pop-up form displays the right info.

Thanks in advance!

EDIT: For clarification, I know how to create a pop-up form. The issue is making sure that I have the right query criteria and VBA code so that the form displays what I need.

One thing that I'm worried about is that I don't want the form to list the individual as being their own spouse (e.g., for Eric Anderson, I don't want his name in the spouse list).

I figured it might be the same priniciple as setting up the form showing a person's children (which I know how to do), but because each child can only have one set of parents and each person can have more than one spouse, I can't do the marriages form like the children form in regard to the queries and VBA.

Apologies for the confusion, but I had a long day at work yesterday, and I thought I had created and proofread enough drafts of my post to make sure it was clear and detailed, as the page rules require.

I’m an MS Access developer, and I was hoping to get some ideas from the community.

I already use AI for coding, debugging, and the usual development tasks. What I’m really curious about is how you’re using AI inside the applications themselves for your users.

Have you integrated AI into an Access database? If so, what does it actually help users do?

I’d love to hear about real-world examples or even ideas you’re exploring.

Thanks for your valuable thoughts already.

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.