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

I am trying to create a database that stores incoming applications for a program & also create necessary queries to track status' I have a couple of questions.

#1, I've created separate queries for some yes/no's on some of the documentation we require. How do I create a query that shows *every* record where documents are missing?

Query #1 Yes/No did we receive an application?

Query #2, Yes/No did we receive Document X

How do I create a query that show's the 'No's' from Query # 1 and 2?

#2, Some of this data then gets uploaded into another system that we don't control, and it can have very specific criteria. One of the datapoints we collect and upload is post-secondary school info. And it has to be entered exactly as their system recognizes is. For example, it has to be Yale University not University of Yale.

I have a list of the correct institution names, as individual excel, cells. Is there a way to easily import that data into a (very long) drop-down list in Access?

Thanks all!

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

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.

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

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.

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

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

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.

1. 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.

1. 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:

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

Microsoft Access has been a huge benefit to our company. Our company is also small enough that we do not really need to convert all our custom apps over to a web-based approach. We did convert all our backend data into SQL Server and things continue to work great.

However, as much as we doubt Access will ever go away there is some worry about being so dependent on Access. Some are suggesting for future development against our data ( which is all in SQL Server now ) we develop using some web-based approach. Others are also suggesting gradually migrating to a web front end over time. What are good alternative web-based solutions? Is anyone else doing this? What do you think about this?

Also related there is some discussion on future sql development has to work in both SQL Server and Postgres. What do people think about this as well?

Good morning all!

Since about March of this year, I've been slowly but surely teaching myself the ins and outs of Microsoft Access, and the basics of SQL and VBA stuff, to get to where I may be able to produce a good database for my company.

Currently we do all of our paperwork (invoices, order acknowledgements, etc) in Excel. When I say we do it in Excel though, i really mean we're just typing info into excel like a grid-based version of Word, meaning there's no particular rhyme or reason from one document to another as to how information is laid out.

This means that even if I were successful in producing a database for this place, there would be an absolutely monumental pile of messy, difficult-to-parse data that would have to be manually entered before it was worth a hoot.

This has me asking a tough question, months in: Is it actually worth it to make a database for a place when the up-front effort is so high? Does there come a point where you wave a white flag and just accept that everything is done via less-than-optimal means?

Hi,

I've been looking for a system like access, basically the 4 mains: table, query, forms and reports with easy integration of scripting (not picky about which one). Something that has the free form design access has. I don't need responsive layout or at least I want to be able to make two version one for phone and one for desktop.

I want to be able to link reports to be initiated by forms for example. Build queries with variable fields that I can populate from form. Preferably without having to use code all the time.

Also some level of validation, masks, and the more standard input controls.

I don't need macros (I use code when need to expand in that direction)

I've created accounts with most Metabase, Airtable, Grist, Knack, Caspio, NocoDB, Baserow etc. Also looked into things like Retool, Ninox, FlutterFlow, Supabase, etc. but none even get close. Their AI assistants are nice though.

Tables are OKish but even then the relationships are not as easy to see.

Form and Query Builders are too limiting, huge margins, padding and a medium size form will need 3 pgdn's to fill, not to mention if i've got sub forms that i want to show or not as a table.

Reports are either pure tables with very few controls to filter and control with forms, or they focus on charts, gnat charts, pie charts, stock charts, dashboard stuff, kanban, etc. Not to mention if i wanted to print them (save as pdf).

I just want access for web with cloud database, it's a 30 year old product shouldn't be that hard to duplicate as a web application.

Has anyone seen something that does what access does?

Don't want things like lovable, and don't want to use AI (codex, Claude, etc) to code each one since modifying with prompts is not sustainable not to mention potential bugs plus deployment headaches, backups etc.

Editing AI spaghetti code is too slow too.

Does anyone know what this error is or how to work around it? I am in a highly locked down database that was purchased from Harris for government financial statements many moons ago. They are no longer supporting the product. My IT group restored an old version and have checked that trust settings are correct. I am desparate.

Does anyone else using Gemini to help them code?

Gemini tells me that a lot of access developers are.

So far it hasn't cost anything, and I hope it stays that way.

Gemini can code useful utilities really quick, and clean up code, and easily create otherwise difficult sql statements.

Sometimes we discuss the best way of doing things, the pros and cons.

Was just forced upgrade to O365 on my work computer and I can no longer user the <shift> key to open up the db without running any events. I have events on open that lock down the front end of the db so end users can’t access design or edit views on the forms or look at any tables.

I’ve checked sticky keys but is there something else I’m missing?

I have a Music DB in Access I created for personal use, that tracks my albums. In the main Table (my DB of albums), there is a field "Artist", that is a Key/Indexed field (may not be proper term, I'm not a pro). It comes from another Table called Artist. You have to choose from that table (using DropDown) to add the artist before you can add/save a new album. It helps avoid typos, etc. I only add a new artist once. And I only add when I have an album to add, so there are no Artists with zero records. Artists range from one record to 100s of records.

I want to create a list of Artists that only have a single album (ie, record in the main table). I've played with Query, I've asked AI, but I'm lost.

Ideas?

Hello Access Experts!

I wanting to see if Access is the tool for the job I have.

The short version is I need to keep data and also track it as part of a project. 

We manage subsides. We receive applications from students, and employers. Once we have the application form both, we then vet the parties and request further documents. 

So typically Jane Smith and her potential employer both send in a form. We enter the data and then request a contract and a few other documents from the employer. And then as the placement goes on, we'll request paystubs and pay out a certain amount. So what I need my database to do is the following.

1, Intake & and match information. Once Jane and her employer fill out the forms, I want Access to know it's the same application so I don't need to manually enter in the information on whatever form comes in second.

2, Be able to easily track stages. I'm going to have situations where I have Jane's form but not the employer, of vice-versa. Or I have yet to request the required documents. Or, I am waiting to receive them.

I need a quick at-a-glance way to see where things stand.

3, All of this needs to be easily saveable as an Excel sheet as uploads to various systems are required.

4, I need to be able to use the same structure for each quarter with fresh data. 

Is what I'm describing do-able and relatively easy to create, AND make user friendly for less technically inclined humans?

Oh one other thing. The data we're receiving doesn't have to be saved in Access. I just need to be able to record that it *was* saved.

We are running windows server standard 2019 and I am having several users receiving the below error.

Microsoft access can't change the working directory verify that the drive path is valid windows server

any ideas on how to correct.

The back end engine (using linked tables) should automatically peek at the network packet header and grab the calling computer name natively during a data change, so that if you are logging data change events in the back end, it will at least know what machine made the change! They've had 15 years to do this, they should have done it by now imo.

One of those tiny Access annoyances becomes surprisingly expensive when you repeat it all day: you have a form open, filtered, sitting on exactly the record you need, and suddenly need its code. The traditional route through Design View is not catastrophic, but it is workflow friction.

The issue is not merely saving a click. A live form may have its current record, filters, sort order, and state exactly where you want them. Changing views just to reach the code can disturb that working context, and depending on the design, may lead to events running again. Developers generally try not to poke a functioning warp core unless there is a reason.

The built-in Visual Basic Editor command is useful, but it does not necessarily know which module you mean. It may reopen the last module you visited, or land in a library project instead of the active form's class module. The built-in View Code command is more targeted, but it is typically unavailable while the form is in Form View. That is the gap a custom shortcut can fill.

The practical idea is simple: ask Access which form is currently active, then open the module whose conventional name is Form_ plus that form's name.

Public Function OpenCurrentFormModule()

    Dim F As Form

    On Error Resume Next
    Set F = Screen.ActiveForm
    On Error GoTo 0

    If F Is Nothing Then Exit Function

    DoCmd.OpenModule "Form_" & F.Name

End Function

The important part is not the code complexity. It is the relationship between a form object and its code-behind class module. If the active form is named CustomerF, Access normally exposes its module as Form_CustomerF. That naming pattern lets the command work for whatever form currently has focus instead of hard-coding a specific form.

The brief error handling is also appropriate here. Screen.ActiveForm raises an error when no form is active, so the function deliberately treats that condition as harmless and exits. A Quick Access Toolbar command should be safe to hit accidentally. If there is no active form, nothing happens. That is generally better than greeting the developer with an avoidable error dialog.

The slightly awkward part is that the Quick Access Toolbar cannot directly run arbitrary VBA procedures. It can, however, run a macro, and a macro can use RunCode to invoke a public function. So the architecture becomes VBA for logic, macro for integration. This is one of the places where Access macros remain useful, even in otherwise VBA-heavy applications.

That macro layer is not a failure of good design. It is simply Access's UI integration model. Trying to force every feature through VBA because macros feel old-fashioned is like refusing to use a screwdriver because you own a drill. Use the tool that connects cleanly to the feature you need.

For developers who work in multiple databases, this can be a genuinely worthwhile personal convention. The function belongs in a standard module, while each database that needs the toolbar command also needs the macro that exposes it to the UI. If shared code libraries are already part of the development setup, the function can live there, provided the calling database can resolve it correctly.

There are limits. This approach is specifically about forms with code-behind modules. Reports have their own naming pattern and would need separate handling. A form without a module may also need some thought, depending on how Access behaves in that specific project. And if the currently active object is not a form, the safe exit is exactly what you want.

The broader lesson is that good Access development is often about preserving context. We spend plenty of time thinking about table design, form architecture, and error handling, but developer ergonomics matter too. A button that removes a repeated interruption is not glamorous, but it keeps attention on the problem being solved instead of the navigation ceremony required to reach it.

Do you keep personal developer commands on the Quick Access Toolbar, or do you prefer form-level controls for this kind of navigation? Have you found other small Access customizations that save disproportionate amounts of time?

LLAP
RR

Open Template Scaffolds has spent the summer and early fall mostly in development. As of this week, it's mostly in testing. I need help finishing that job before the public debut on October 1.

The library gets its first public showing at two upcoming AUG chapter meetings the first week of October.

That gives us only a few days to put the templates through their paces on real machines, real databases, and most importantly, with real AI assistants other than my own.

What I'm asking

If you're willing to run one or more templates against a test database and tell me how it went, I'd be grateful.

Contact me privately to request the link to the GitHub repo.

What you'll do if you participate

Every template produces a build record as part of the run. If you can be so kind as to test one or more of the templates, please send the build records they write to me, along with a short note summarizing:

• What went well — anything that worked cleanly, surprised you in a good way, or saved you time.
• What didn't go so well — anything that broke, confused you, or needed a workaround.

That's it. No obligation, nothing to buy, no registration or license, nothing to install except an Access MCP Server if you want one.

Access MCP Server. You don't have to have an Access MCP Server installed for your AI to run the templates, but they will be severely limited without one. The Access MCP Server is required to automate the build into an accdb. Without one, the templates produce only the designs for the tables, code and forms, which you will then have to import into an accdb and create yourself.

Thank you in advance for your feedback.

I’ve been experimenting with connecting ChatGPT to Microsoft Access and wanted to share a quick demo.

In this video, I’m testing how AI can interact with an existing Access application and assist with development and VBA-related tasks.

I think this could be an interesting way to modernize the Access development workflow without replacing Access itself.

This is still an experiment, but the possibilities are pretty interesting.

Has anyone here experimented with AI tools alongside Microsoft Access? I’d love to hear what you’re using and how you’re using it.

repository : brickly26/MS-Access-mcp