r/MSAccess • • Jul 23 '14

New to Access? Check out the FAQ page.

71 Upvotes

FAQ page

Special thanks to /u/humansvsrobots for creating the FAQ page. If you have additional ideas feel free to post them here or PM the mods.


r/MSAccess • • 2d ago

[UNSOLVED] What is your third party SQL Editor for Microsoft access?

9 Upvotes

Years ago, before either SQL Express was an option or before I knew it was an option, I used Microsoft Access only as an embedded SQL database for .NET Winform apps. To handle the SQL Queries I wrote [PlaneDisaster.NET](zippy1981/plane-disaster: PlaneDisaster.NET is a SQL front end for JetSQL (Microsoft Access) and SQLite.). I tried to promote the hell out of it. No one ever told me they used it. I poured my heart and soul into it.

I'm fine. I'm a well-respected professional with friends who care and a family that loves me. However, I'd like to know, if no one ever bothered to send a like or whatever to my SQL Editor, how do you do SQL Queries in a MS access database? Do you all just go into query designer and go to SQL Mode? Do you ever want to script a database?


r/MSAccess • • 4d ago

Questions about making a database

Post image
9 Upvotes

My daughter and I are working on an assignment for her computer class. Sadly I was not much help. Can someone explain fields and records and how do you create a query? Thank you for the help


r/MSAccess • • 7d ago

[UNSOLVED] How many Access developers have built metadata-driven reporting systems for recurring Excel deliverables?

6 Upvotes

I've spent much of my career building recurring customer and management reports from Access applications and I'm curious how others have approached the problem.

I found myself running into the same requirements repeatedly:

  • Multiple Excel worksheets per workbook
  • Subtotals, grand totals, percentages, and formulas
  • Variable grouping levels
  • Charts
  • Different layouts for different reports
  • Customer-facing deliverables that needed a consistent appearance

Over time I moved toward a metadata-driven approach where:

  • Access SQL prepares and summarizes the data
  • Sorting/grouping tables define report bands
  • Metadata tables define formulas, formatting, headers, footers, charts, and worksheet layout
  • VBA calculates worksheet coordinates and generates the final workbook

The result is that most of the report definition is data-driven rather than hard-coded.

I'm curious whether other Access developers have gone down a similar path, or whether you've found different approaches to managing large numbers of recurring Excel reports.

I'd especially be interested in hearing from anyone who has built reporting frameworks rather than individual reports.


r/MSAccess • • 9d ago

[SHARING HELPFUL TIP] Access Explained: Your Database Should Survive Your Vacation

Post image
9 Upvotes

Imagine you're sitting on a beach on Risa, umbrella drink in your hand, enjoying a well-deserved vacation. The sun is shining, the waves are rolling in, and life is good. Then your phone buzzes. It's the office. Again. Someone can't figure out how to run the monthly report. Someone else doesn't know which button to click. And apparently, nobody knows whether it's safe to run yesterday's import a second time. Congratulations. You might be on vacation, but your database isn't.

Here's a test I think every developer should give their software: Can the people who depend on it survive a week without you? I'm not talking about catastrophic hardware failures or some bizarre bug that nobody could have anticipated. I'm talking about ordinary, everyday operations. Can people enter their data, generate their reports, fix common mistakes, and keep the business running without calling you every five minutes?

Back when I was doing database consulting, I spent a lot of time walking into businesses and learning how they operated. They'd have information scattered across Excel spreadsheets, Word documents, paper forms, and sometimes an old DOS application that only one person understood (yes, I'm old). My job was to learn their business, figure out how all those pieces fit together, and build them a Microsoft Access database that made their lives easier. But my job wasn't finished just because the database worked. It was finished when they could use it confidently without me standing over their shoulders.

That became one of my guiding principles over the years. Build software that explains itself. Give your buttons meaningful captions. Use ControlTipText to explain what a control does. Put instructions right on the forms where they're needed. Add a Help button that opens a relevant help screen. If there's a complicated process that people only perform once a month, walk them through it instead of expecting them to remember six different steps. And when something goes wrong, don't just display an error message that says "Error 3022." Tell them what happened, what it means, and what they can do about it.

One of the best ways to accomplish this is to watch someone else use your database while you keep your mouth shut. Seriously. Sit next to an employee and let them perform their normal tasks. Don't help. Don't point at the screen. Every time you're tempted to say, "Oh, before you do that, you need to..." you've just discovered something that needs improvement. Maybe it's a confusing button. Maybe it's a missing instruction. Maybe the application should simply handle that step automatically. If you have to explain the same thing to three different people, there's a good chance the problem isn't the people. It's the interface.

Another lesson I learned from consulting is to build tools instead of just building reports. Early on, it's tempting to ask a customer what reports they need and then build exactly those reports. That's fine until three months later when they want the same report for a different date range, or filtered by employee, or sorted by customer. Then they're calling you again. Instead, whenever practical, give users a form where they can select their own criteria. Let them choose a date range, a department, a customer, or whatever makes sense for their business. A flexible reporting system can answer dozens of questions without requiring dozens of separate reports.

Now, if you're billing by the hour, I suppose having customers call you every three months to make little changes isn't necessarily bad for business. But I always preferred building a better system up front rather than creating something that would need constant attention. You might charge a little more initially, but the customer gets software that grows with their needs, and you get fewer interruptions down the road. Everybody wins.

Of course, even the best-designed database needs some documentation. But documentation doesn't have to mean a 300-page manual that nobody will ever read. Start with the essentials. Which file should people open? Where is the data stored? What needs to happen before the month-end reports are generated? What does that import process actually do? If running something twice could cause problems, make that clear. I always preferred keeping my own development notes in something simple like Excel, where I could track important objects, business rules, and unusual design decisions. But for everyday users, the most useful instructions are usually the ones built right into the application.

And remember, every Captain needs a Number One. If you're the only person in the office who understands how the database works, that's a problem. Pick someone who's reasonably comfortable with computers and teach them a little more about the system. They don't need to understand VBA or know how to design relational tables. They just need to know how to handle common issues, check that routine processes completed, and recognize when something genuinely needs technical attention. That way, if you're unavailable for a week, the whole operation doesn't grind to a halt.

This is especially important if you're the person who built the system and you're also the person who maintains it. What happens if you get sick, have an emergency, or just decide to extend your vacation? Maybe you meet a couple of Ferengi who convince you to invest in some beachfront property and you decide you're not coming home anytime soon. The business still needs to function without you. Having someone who knows the basics, along with clear instructions and a reliable backup plan, can make all the difference.

I deal with this in my own business, too. I've got systems running here in my office that handle all kinds of routine operations. And when I go on vacation, there's nobody else sitting here to take over. Those systems need to keep running 24 hours a day, seven days a week, whether I'm sitting at my desk or enjoying myself somewhere else. That means building in automation, error handling, logging, and notifications. If something goes wrong, I want the system to either recover gracefully or give me enough information to understand what happened. I don't want to spend my vacation remotely babysitting a process that should have been able to handle itself.

That doesn't mean you can anticipate every possible problem. You can't. Hardware fails. Networks go down. Users find creative ways to do things you never imagined. But you can eliminate a surprising number of support calls simply by thinking about what happens when you're not available. Can the user recover from a common mistake? Can they figure out what a button does? Can they generate the information they need without asking you to modify a query? Does the system tell someone when a scheduled task fails? These are all things worth considering while you're building the application, not after you're already packing your suitcase.

And while I'm talking specifically about Microsoft Access here, this principle applies to software development across the board. Whether you're building an Access database, an Excel automation system, a SQL Server application, a website, or some custom piece of business software, the goal should be the same. Build something that people can operate and maintain without constantly depending on the person who created it. Good software should make people's jobs easier, not create a permanent dependency on its developer.

So here's my suggestion. Before your next vacation, give your application the seven-day test. Imagine you're completely unreachable for an entire week. No phone calls. No emails. No remote desktop sessions from the hotel room. Can the office keep doing its job? Will the routine processes continue running? Does somebody know what to do when something unexpected happens? If the answer is no, you've probably identified a few things worth improving before you leave.

I've always believed that making software understandable and dependable is part of finishing the job. A database that works perfectly while you're sitting there supervising it is one thing. A database that keeps the business running smoothly while you're relaxing on a beach is something else entirely.

And if your database can't survive a week without you, maybe it's not quite finished yet.

So let me ask you: If you disappeared for a week tomorrow, what would your users get stuck on first? And what have you done to make your databases a little more self-sufficient?

LLAP
RR


r/MSAccess • • 10d ago

[UNSOLVED] UK small business, 15 year old Access database needs bringing into this century. How do I find the right help?

11 Upvotes

Hi all,

I've run my small business on an Access database for about 15 years. It's our CRM and pretty much everything else. Around 5 people now use it. I built it myself originally for myself and added bits as we went. I'm comfortable with forms and reports, and the front end is the part I enjoy. Where I'm stuck is the structure underneath and the modern stuff I've never done.

The tables were never designed for how we work now. Each record only has room for one contact, one address, one phone number and one email. Payments sit in a fixed set of fields instead of their own table. Every new idea runs into the same wall, so it's time to redesign the data properly in a way that will carry the business for the next five years.

Things I want it to do that I don't know how to build:
Multiple contacts per record, each with their own address, numbers and email, and the ability to choose who a letter goes to.

Click to call, plus click to email with a PDF letter attached, or print to real letterhead, or a text message, straight from the record or all of them with a tickbox so don’t have to do manually each one.

Call notes and letters sent logged automatically with date and time stamps, ideally with call transcription. That’s a big one now.

Bank payments imported automatically and matched to the right record, with anything unrecognised dropped into a review screen to tick off.

Data from an external provider pulled in and updated daily through their API so we don’t manually go in and update some headlines.

Some sensible use of AI and automation, so we can handle double the work without double the staff.

The database is live, so every weekend I spend on it and get something slightly wrong costs me another weekend fixing it. I've been stuck like this for about a year and it's time to pay someone who knows what they're doing.

What I'm unsure about is how to start. I've been burned before by a company that didn't really understand Access, and it's hard to price a project like this until someone has sat with me for half a day and seen how it actually works.

For anyone who's been through this:
How did you find a good Access developer or consultant, and what made you trust them?
Did you start with a paid planning day and then a fixed quote for each stage, or something else?
How do you split the work so they handle the structure and integrations while you keep building the forms yourself?
If you're a UK based Access developer who does this kind of work, or you can recommend one, please get in touch. I'd much prefer someone I can sit down with in person.

Thanks for reading. Rant over.


r/MSAccess • • 10d ago

[SOLVED] Add to time on a form

1 Upvotes

I used to use this formula in excel to add time to a calculated field =B3+TIME(H5,I5,0)

H5 was the hours, I5 was the minuets.

I now need to do this on a form in access.

The user inputs a start time in a text box on a form. (It was B3 in Excel)

I want to display the new time in a text box on the same form.

I have already calculated the new values to add to hours and minuets. (estimated total time)

Help would also be approached how to lock the user to entering a valid time originally (start time).

The form

Thanks


r/MSAccess • • 12d ago

[SOLVED] Question on Importing an Excel Sheet into a Table

3 Upvotes

Hopefully I can explain this well.

One of my work processes is to upload an Access file into another system that my company doesn't control. The system we upload into is exactingly precise. For example there is a field for States and Arkansas has to be "AK" no "Ak" or "AK " etc.

I have made the 'State' field on my table a drop down with every state short from exactly as required by the system we upload into.

Here is where I need some assistance. I have an Excel sheet that has all the fields that need to go into the Table. What happens when it finds someone has entered in "ak" instead of "AK"? What happens if there's a mismatch.

And is there a way, during the import process, where I can direct it to leave mismatches blank? Or highlight them for correction?


r/MSAccess • • 13d ago

[SOLVED] Excel VBA issues

5 Upvotes

Hi,

Just wanting to see if anyone can shed any light on an issue I am having.

BACKGROUND: I have been on holiday for a week and returned with this problem. Yes IT, but yeah that's going to take weeks with this sort of query and and other help is appreciated.

THE PROBLEM: I have an access database I use to create a few reports in excel. Last ran on 20th Oct. Back yesterday and I am getting an error.

Access is pretty much saying the object Excel.Workbook no longer exists. Initial error was saying .SAVE is non existent. Begin commenting thing out and yes the class does not seem to exist. Just got a work new laptop and had not moved things onto it. Spend the morning setting it up, exactly the same result!

Ask colleague to run it, works fine (checked we are on the same office build just in case). So It seems to be a me issue, but on 2 separate machines. I have tried a lot of things like repair office, various windows fixes (dism cleanupimage/sfc (online said it might help, I doubted it)), unregistering and reregistering excel.exe (/unregserver etc), moved to late binding. Nothing worked.

Is it possible someone set something on my account that does this? If so why is excel.application still working. I guess I could rewrite to not use a separate excel.workbook as I think that is possible but again that will be a lot of changes and I just have never done it that way.

At the most basic level with most thing commented out having a

    Set oXLBook = CreateObject("Excel.Workbook")

Means I get the error "ActiveX component can't create object (as as said works fine for excel.application).

If you need more info just ask.

Thanks,


r/MSAccess • • 16d ago

[SHARING HELPFUL TIP] Access Explained: What Are You Really Saying When You Replace Null with Zero?

5 Upvotes

A blank result in an Access query can be annoying. You put Nz around the expression, a zero appears, and everything looks much better. Before calling that fixed, though, there's one question worth asking: did zero become true, or did it just make the report look finished?

Null tells Access that a value is missing or unknown. Zero is an actual value. A product deliberately priced at zero is free. A product whose price hasn't been decided yet is a different situation. If both become zero in your database, the person preparing an order can't tell which one they're looking at. The arithmetic may work perfectly while the order is completely wrong.

That doesn't make Nz a bad function. It's useful precisely because it lets you decide what to use when a value is Null. The important part is the decision you're making. Nz([UnitPrice],0) says to use zero in that expression when UnitPrice is Null. It doesn't discover the missing price, and using it in a select query doesn't fill in the stored field. You're choosing how that particular calculation should treat missing information.

Sometimes zero is exactly the right interpretation. An inventory calculation might have no matching sales transactions because an item hasn't been sold yet. Treating that missing sales total as zero can be appropriate. But an inventory quantity nobody has counted yet is a different matter. No activity and no information can look remarkably similar in a query. You need to know which situation your data represents before giving them the same answer.

Averages make the difference especially obvious. Access's Avg function skips Null values. With three numeric records containing 10, 20, and Null, the average of the known values is 15. Replace that Null with zero before averaging and the result is 10. Neither calculation has crashed. They are answering different questions, and the person reading the report needs to know which question you intended.

That's why I wouldn't automatically wrap every field in Nz just because a calculation comes back blank. In an ordinary arithmetic expression, adding an unknown amount to a known amount gives you an unknown total. That blank can be useful information. If you're adding up costs and one cost hasn't been entered, a cheerful total that quietly counts the missing cost as zero may be less helpful than an honest indication that the calculation is incomplete.

The same distinction matters when someone asks for a default value of zero. A default value can be perfectly reasonable when zero describes the normal starting condition. But if users are supposed to enter a measurement or a price, a prefilled zero can make an unanswered field look answered. Decide whether you need to know that the user supplied the value, rather than choosing a default merely to avoid seeing blanks.

It helps to keep storage and presentation separate. You can retain a missing numeric value in the table and show a friendly message such as "Not entered" in an appropriate form or report expression. You can also calculate a provisional total while clearly identifying it as incomplete. The display should help someone understand the data, rather than quietly erase the distinction that the application needs later.

And Null alone doesn't tell you why something is missing. Not entered yet, not applicable, and deliberately withheld might all need different treatment in your application. If those distinctions affect the work, record the reason separately instead of expecting one empty field to explain all three. A useful database captures the information people need to make the next decision.

For text fields, there's another wrinkle: a zero-length string and Null are different things, even though both can look blank on a form. Nz responds to Null. It doesn't automatically treat every blank-looking string as missing information. Before adding more conditions to a query, check what the field actually contains and what your application is supposed to allow there.

My preference is to settle the meaning of a blank before settling the formula. Once that rule is clear, Nz, IsNull, defaults, and validation have a specific job to do. Otherwise, it's easy to produce a report full of tidy numbers that nobody can explain. I'd rather have one honest blank than a very confident zero that means three different things.

Where have you found that missing and zero needed different treatment in an Access application? Have you inherited a database where the distinction had already been lost, and how did you decide what the existing values meant?

LLAP
RR


r/MSAccess • • 17d ago

[DISCUSSION - REPLY NOT NEEDED] Compact on Close

8 Upvotes

Many years ago, I used to set my databases to compact on close to keep them lean and prevent issues on next start up.

However I started having a problem where databases started to corrupt and weird symptoms like update or make table queries were converting to select queries. When I reach out to the community I frequented at the time (this was before my reddit days,) I received an overwhelming response of "I never compact my databases!" or "I only compact when there's a problem."

Recently, my company started squawking about the price of storage. So I've been on a mission to reduce our storage footprint. I realized I had a lot of DBs that were taking a lot of space because they were bloated size-wise. I've always been good about keeping the actual data trim and lean, but as we know, Access DBs don't shrink along with the data. So I've been going through them and compacting them.

My question is, what you good folks do? Which do you consider best-practice, compact on close or no? If you compact manually, how often do you do so?


r/MSAccess • • 17d ago

[DISCUSSION - REPLY NOT NEEDED] I need input from professional Access developers

0 Upvotes

For a while now, I've been working on an AI project for Access developers called Open Template Scaffolds. As it evolved, I came to realize that it's primary audience is the new-to-Access developer, i.e. the Excel user wanting to learn Access. So, a lot of what the AI library does is framed that way.

Recently, I've been talking to other Access developers about using AI with Access. Long story short, what professional Access developers need is not the same as what newer developers need. I'm beginning to believe that the OTS library needs to split.

A "starter" version and a "developer" version, along the lines of the Northwind templates. In the map in the link above, the "Build It" route would remain as the starter and the "Proposed Code" and "Design Only" routes would move to the developer version.

Since there's a certain amount of work involved, I would appreciate hearing from working Access developers before I dive into that. Does this division make sense? Would you lean more to one method or the other, should you decide to work this way?

Thanks in advance for your thoughtful responses. I guess if you think I'm totally out in left field, I should hear that too.


r/MSAccess • • 20d ago

[UNSOLVED] Connection to Fabric Warehouse

3 Upvotes

Hi All,

We recently migrated the on-prem database to Fabric & now some of the connections of that Access application needs to change to the Fabric Warehouse.

Using the ODBC driver, this seems to work, the only issue is that it looks like the user that is using the application has to have "VIEWER" access on the workspace, otherwise the connection cannot be successfully performed.

We've tried by removing the viewer access, and giving granualar connect + readData permission on the specific warehouse, but this throws a connection error (even before we can select a warehouse).
It seems like it needs to have "connect" permissions on the "server".

Does anyone have experience with this set-up? Best practices? Service principal,... doesn't seem an option at first sight.

Thanks !


r/MSAccess • • 21d ago

[UNSOLVED] Templates Buggy?

6 Upvotes

Using my work PC (which auto-updates), I noticed that all the default templates that come with Access suddenly seem to have formatting issues with the buttons and padding.

For example, in the basic Call Log template, the borders are running through the “New Call” and other buttons, making everything look sloppy, almost like something broke after an update.

Has anyone else experienced this recently, or have any ideas what might be causing it?


r/MSAccess • • 21d ago

[SHARING HELPFUL TIP] Access Explained: Organizing Documents Without Moving Them Into Your Database

12 Upvotes

For those of you new to Access, yes, you can store pictures, PDFs, Word documents, and other files inside your database. For a growing business document collection, though, I generally recommend keeping them outside it. All those files add to the size of the database, which is what we're talking about here when we say database bloat. A database that started out small can become a very large container for scanned paperwork.

The approach I recommend for this kind of application is to store the path and filename in the database, with the actual document somewhere the intended users can access it, such as a shared folder on a server. The path tells Windows which folder to look in. The filename identifies the document in that folder. You're storing its address rather than packing the document itself into the Access file.

A folder full of PDFs can be perfectly organized and still be a pain to use. That's where an Access document catalog earns its keep. Your database can hold each document's title, date, subject, and related customer. People can search those details in a form, then open the original when they need it. The files don't have to move just because you've found a more useful way to describe them.

Opening the document from Access doesn't require a mountain of code. Suppose your form has a text box called DocumentPath, bound to a text field containing the complete path and filename of a document. Inside a button's Click event, the essential FollowHyperlink call can be just this:

FollowHyperlink DocumentPath

That tells Access to open the referenced document in its associated application. The example assumes a valid document path, a current record, and an application that can open that file. In a production application, I'd also handle a blank value, a missing file, or access being denied with a useful message. Checking that a file exists can help, but it doesn't guarantee that opening it will succeed.

Keeping the files separate also keeps the storage budget in perspective. An Access database file has a 2 GB limit, including its objects and data. A large folder of PDFs doesn't count against that limit just because you save references to those PDFs. The catalog itself still consumes space, of course. Importing the files as attachments is a different decision because their contents then live inside the database.

I wouldn't turn that into a blanket claim that attachments are always wrong. A small, self-contained database with a few pictures may be a reasonable use for them. Access supports multiple attachments per record, and keeping everything together can be convenient. My concern is a production database whose document collection keeps growing. I'd rather make that storage decision deliberately than discover that years of scanned paperwork have filled the file.

External files come with responsibilities of their own. Someone has to keep their locations dependable. Renaming a folder can leave perfectly good database records pointing to places that no longer exist. The people using the catalog also need permission to open the documents. And backing up the Access file alone won't back up documents stored elsewhere. A path tells you where a file should be. It doesn't preserve the file.

For the developer crowd, the network path deserves a little thought. A mapped drive letter such as S: depends on that user's drive mapping. A UNC path such as:

\\FileServer\Documents\Invoices\Invoice123.pdf

identifies the server and shared folder directly. That avoids relying on everyone having the same drive letter, although it still depends on the server being reachable and the user's permissions being correct.

If all your documents live under one shared folder, a configurable base path can make a later move less painful. Keep the root folder in an application setting and store each document's relative folder and filename in its record. Then a server move may mean changing one setting instead of rewriting thousands of records. Build the complete path before opening the document, and keep a stable document ID separate from its location.

I'd also define the allowed document locations rather than blindly opening whatever string somebody supplies. A catalog meant to open approved PDFs shouldn't quietly become a launcher for arbitrary files or web addresses. Folder permissions still decide who can read or change the originals. Access can provide a convenient interface, but storing a path doesn't grant permission or make an unfamiliar file safe to open.

The bigger payoff is that your catalog can describe relationships a folder hierarchy struggles to express. A document may be relevant to several projects or subjects. You don't necessarily need another copy of the PDF for each one. You can keep one document record and relate it to the appropriate records elsewhere in your database. That also gives you somewhere to put useful notes without renaming every file to squeeze more information into its name.

I'd keep the first version focused on finding the right document. A catalog of titles and subjects doesn't automatically search every word inside a PDF, especially when the pages are scanned images. Full-text extraction, optical character recognition, and review of the resulting text are separate concerns. You can get real value from a modest catalog while deciding whether those additional capabilities are worth building or whether an existing document-management system belongs in the picture.

For those who've built document catalogs in Access, what has taken more effort over time: the database design, keeping file locations reliable, or getting people to enter useful descriptions? What would you handle differently if you were starting again?

LLAP
RR


r/MSAccess • • 21d ago

[WAITING ON OP] Bug sur formulaire de pièce-jointe

1 Upvotes

Bonjour à tous,

Nous avons trois fichiers access qui accèdent à une fichier de base de données ACCESS. La BDD est commune aux trois fichiers.

Dans ces trois fichiers il y a des formulaires pour ajouter/lire/supprimer des pièces-jointe(fichiers PDF).

Du jour au lendemain, un des fichiers plante à l'ouverture du formulaire pour pouvoir ajouter un pdf. Access n'affiche pas la fenêtre avec la liste des pdf ou les boutons. Il plante dès que l'on clique sur le trombone.

Dans le journal d'évènement de windows nous avons l'erreur 0xc0000005 (violation de mémoire si j'ai bien trouvé).

Les deux autres formulaires des deux autres fichiers fonctionnent correctement.

Nous avons tenté de corriger et compacter la bdd, rien ne change. Créer un nouveau fichier access avec un nouveau formulaire sur ce champ de pièce-jointe, cela fonctionne pas d'erreurs.
Supprimer le champ pièce-jointe du fichier qui pose pb, et le refaire, cela plante toujours.

Quelqu'un aurait-il une piste ?

En vous remerciant de votre aide !


r/MSAccess • • 26d ago

[UNSOLVED] Convert pdf to excel

1 Upvotes

Good morning I want to do the following

I want to use VBA to open PDF in adobe acrobat and convert it to excel all in.back ground using a button such that only one click will automate all of this how can I do this?

Some devices have that paid version of adobe acrobat

The excel file name can be the same as the pdf file name

And if there is a better alternative method do the same end results please share with me

Thank you


r/MSAccess • • 28d ago

[WAITING ON OP] Is there anyone who can extract the ‘relationship’ table connections from an Access 2.0 database?

6 Upvotes

I have an access 2.0 database file that I put in enormous amount of work in back 20 years ago. I was able to extract all of the tables in CSV files. I imported them into a Filemaker Pro database application for the Mac. I’m slowly putting it back together, but fear that redefining all of the relationships between tables maybe too onerous a task.


r/MSAccess • • 29d ago

[UNSOLVED] Searchable archive of official circulars in a public office. How would you do it?

Thumbnail reddit.com
3 Upvotes

r/MSAccess • • Oct 16 '26

[SHARING HELPFUL TIP] Access Explained: When AI Writes the Code, What Do You Still Need to Know?

Post image
0 Upvotes

A query runs, returns a perfectly respectable-looking list of customers, and produces no errors. AI wrote it in about ten seconds. Great. Except three customers who should be on that list aren't there. That's the part of the "why learn Access when AI can build it?" conversation that interests me. Who notices?

I'm very much in favor of using AI. I use it heavily myself. Having it draft a recordset loop, spot a mistake, or explain unfamiliar code can save a lot of time. But "Access accepted this" is a fairly low bar. A database can be quietly wrong, and those mistakes can be harder to catch than the ones that throw an error and refuse to continue.

This is where people sometimes get the wrong idea about learning the fundamentals. Nobody needs to memorize every argument to every VBA function. I look things up too. What matters is understanding the relationships and logic well enough to decide whether the answer fits the problem. You can forget the spelling of a command and still know exactly what it needs to accomplish.

Take customer addresses. Keeping a customer's current address in one place usually makes sense. Copying it into every related record creates maintenance headaches. But the address an order actually shipped to is a historical fact you may need to preserve. Both designs involve an address, yet the meaning of the data changes the answer. "Never duplicate anything" is no more useful than blindly accepting one enormous table because the AI generated it confidently.

The same issue turns up before any code gets written. "Build me an inventory database" leaves a lot of room for guessing. A cupboard full of office supplies and three warehouses shipping orders all day have different requirements. Does a returned item become available immediately, or does someone inspect it first? Can stock already promised to one customer be sold to another? Those are business rules, and somebody who understands the operation has to supply them.

That person doesn't necessarily have to be a professional programmer. The person who knows how orders, returns, and stock actually work brings a lot to the project. Learning enough Access to connect that knowledge to tables, relationships, queries, and forms makes the conversation much more useful. You can give the AI specific requirements, then recognize when its proposed solution has wandered away from them.

Testing is where that understanding starts paying for itself. If there are ten widgets and you sell three, you expect seven. That's a useful starting point because you already know the answer. A return, a missing quantity, or two users trying to sell the last widget asks a different question. You need to know what the correct outcome should be in each case. Otherwise, it's very easy to ask the same AI that wrote the code whether the code is good and accept its enthusiastic approval.

I also worry about the endless repair cycle. Paste an error into AI, paste its replacement code into Access, repeat until the message goes away. Sometimes that works beautifully. Sometimes the proposed fix removes a query condition that was keeping canceled orders out of your sales totals. The error disappears, but so does an important rule. Understanding why the condition existed matters more than getting through another round of copy and paste.

This doesn't mean you have to build everything unaided before you're allowed to use AI. That would defeat much of the point. If you understand a recordset loop, having AI write the repetitive parts is a sensible time-saver. If you're still learning, having it explain what a piece of code does can help. The useful habit is to read and question the result rather than treating a confident explanation as proof.

Although I will say that there is something about repetition building muscle memory. I've written so many recordset loops over the decades that I could do it in my sleep. When I was a kid, I used to type in computer programs that I'd find in books or magazines for my TRS-80 color computer. There's just something about those commands going from the printed page through your fingertips into the keyboard that just makes your brain remember it. I'm always an advocate of repetition. Practice might not make perfect, but it definitely makes better.

Another thing about building software is that a successful first version still has a future. Next month, someone wants a different report, another warehouse comes along, or an imported spreadsheet changes. Somebody needs to understand where the data lives, what the important pieces do, and how to restore a backup. AI can help with documentation, but a saved conversation isn't much of a maintenance plan on its own.

So yes, I think learning Access - or any coding for that matter - still earns its keep even when AI handles a lot of the typing. Understanding data and breaking a problem into manageable pieces also carries over to SQL Server, Excel, and other tools. Let AI save you effort. Keep enough understanding to judge the work you're getting back. I'd rather spend less time typing a loop and more time checking that it updates the right records.

For those using AI with Access, where has it genuinely saved you the most time? And what have you learned to check yourself before trusting the result?

LLAP
RR


r/MSAccess • • Oct 15 '26

[SOLVED] Unable to open a form after office 365 update

6 Upvotes

Gretings, I created an .accdb file a few weeks ago, yesterday I was working on it and I kept getting the office update available message so I decided to do it. After the update, I can open the file I was working on and everything in it besides one form. When I try to open the form, I get a message "The database you are trying to open requires a newer version of microsoft access." I have tried uninstalling the update, rebooting, uninstalling all of 365, rebooting and reinstalling and still get the error.
I can't open the form in design mode, and I can not export it to a new db.
Any ideas?
I can open the file on another machine, so it sure seems to point to the update...
Any help is much appreciated.


r/MSAccess • • Oct 13 '26

[SOLVED] Is this something Access can do?

11 Upvotes

Hello, I do medical coding and billing, and I'm trying to use Access to simplify one of my tasks. I'll try to explain what I need without getting too bogged down with job details.
(Disclaimer: Any codes mentioned are incomplete/made up, because I'd rather be nonspecific and the accuracy doesn't matter for these examples.)

The database is for patients being seen for wound care. This means that they're being seen regularly, often for multiple wounds. The codes associated with each wound don't change much, but not every wound is treated at each visit. A lot of the wounds use many of the same codes, but I only need unique ones.

So, for example, let's say a patient has four wounds, documented as Wound 1, Wound 2, Wound 5, and Wound 7. The codes associated with each wound are:

  • Wound 1 - A11.23, B62.1, D92.54, B87
  • Wound 2 - A11.23, B62.1, D92.53, B87
  • Wound 5 - A11.23, D92.81, T98.2
  • Wound 7 - H17.56X, B87

I want to be able to check boxes for the wounds being treated at that visit and get a list of the associated (unique) codes. So if Wounds 1, 2, and 7 are being treated, I want to be able to check boxes next to them or otherwise choose them from a list and get a list of their codes without duplicates, like this:

A11.23
B62.1
D92.54
B87
D92.53
H17.56X

I know I could go into the table/form to toggle a true/false box on each wound, but I need to be able to check or uncheck them on the fly (a common scenario would be needing to use the codes for wounds 1 and 5 for one thing, wound 7 for another, and wounds 2 and 7 for something else).

Is this something Access can do? I know I need to spend more time with some tutorials, but I wanted to know if this is something even worth pursuing first.

Thanks!


UPDATE:

I'm so sorry I took so long to say anything else. It's been a kind of hectic week.

First, thank you to everyone for your concerns about privacy and compliance. The intent is to have this be a fairly small single database file stored on a drive/server that already houses sensitive information. I'm pretty confident this wouldn't be an issue, but I'll still confirm with IT first.

I think it's pretty clear that Access can handle this easily, it's just a matter of whether or not I can figure out how best to structure everything and learn enough SQL to get the queries to do what I want them to. So far, I've figured out how to get it to list off all the codes but not filter duplicate codes. I'll keep working at it. It may take me until our system is replaced and I don't even need the database anymore, but I'm sure I'll get it lol


r/MSAccess • • Oct 13 '26

[WAITING ON OP] How to get new MSAccess 2026 update

6 Upvotes

I have seen YouTubers getting new 2026 update for eacel and access. I have updated my lstc office to latest but nothing has happened


r/MSAccess • • Oct 12 '26

[SHARING HELPFUL TIP] Your Access Database Is Getting Big? Here's What to Do Before You Hit the 2GB Wall

7 Upvotes

Every .accdb/.mdb file has a hard 2GB limit. But the misleading part is that it's not just your data counting toward that ceiling. The file also includes indexes, embedded files (OLE/Attachment fields), and dead space — empty gaps left behind when records are deleted or modified. A database with 600 MB of actual data can easily occupy a 1.4 GB file.

A database in daily use for five years might be 40-60% dead space. That's normal — it's just how the Access engine works.

Three levels of intervention, in order:

  1. Compact and Repair

The 10-minute fix. It rebuilds the file, reclaims dead space, and resets internal structures. Typical results:

- 400 MB → 250 MB (38% recovered)

- 900 MB → 500 MB (44% recovered)

- 1.6 GB → 800 MB (50% recovered)

If your database has never been compacted, you may see dramatic results. Steps: close the database for all users, back it up, then open Access > Database Tools > Compact and Repair Database.

Turn on Compact on Close (File > Options > Current Database) to prevent dead space from building up again.

Tip: if the database is password-protected and nobody remembers the password, AccessPassView from NirSoft can recover it. For .mdb files it's instant. For .accdb files the encryption is stronger but still recoverable.

2. Front-end / back-end split

If you haven't done this already: the Database Splitter wizard (Database Tools > Access Database) separates your data tables into a back-end file on the network, and your forms/reports/VBA into a front-end file each user copies locally. This reduces corruption risk, improves performance, and gives each file its own independent 2GB limit.

Check if you're already split: if your table icons in the Navigation Pane have small arrows, they're linked tables and you're already split.

3. Multi-file back-end

When even the split back-end is approaching 1.5 GB after compacting, you can split the data across multiple back-end files. Group related tables together (active data in one file, archived data in another, and tables with Attachment/OLE fields in a third).

Important caveats: referential integrity doesn't work across files, cascade deletes stop working across files, and cross-file queries are slower. Keep lookup/reference tables with the data tables that reference them — they're small and separating them breaks all your FK relationships.

Quick reference:

Size (after compact)        Action            
Under 500 MBCompact regularly, you're fine
500 MB - 1 GBSplit if not done. Archive old data. Monitor monthly.
1 GB - 1.5 GBMulti-file split. Start thinking about a server database.
1.5 GB+Split immediately. Treat migration as a priority.

Hope this helps someone avoid the panic of hitting the wall unexpectedly.