Database Documenter
By Richard Rost
2 years ago
Reveal Hidden Dependencies with DB Documenter
In this Microsoft Access tutorial, I will show you how to use the Database Documenter to uncover hidden object dependencies that standard tool might miss. We'll learn how to generate detailed reports on your database's objects and see why exporting to a text file might not always be the best option.
In my previous video, I showed you how to use the Object Dependencies tool in Microsoft Access to figure out which queries are safe to delete if they aren't being used anywhere. However, if the query is used in a function in one of your forms or reports, it might not show up in the Object Dependencies tool. So today, I'm going to show you how to discover those hidden dependencies using the Database Documenter.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Resources
Third Party Tools
Keywords
TechHelp Access, Database Documenter, hidden object dependencies, Object Dependencies tool, DSUM function, query dependencies, export database report, Access report formatting, PDF export Access, detect unused queries, VBA code dependencies, module documentation, relationship mapping, Access database analysis, third-party database tools, Total Access Analyzer, Total Access Detective, database change detection, saving Access objects
Intro In this video, I will show you how to use the Database Documenter tool in Microsoft Access to generate detailed reports about your database objects and find hidden object dependencies that the standard object dependencies tool might miss. We will walk through selecting specific tables and forms to document, export the results in different formats like text and PDF, compare the usefulness of each format for searching and analyzing data, and discuss the best way to locate hidden references within your database objects.Transcript Today, I'm going to teach you how to use the database documenter tool in Access, specifically to discover hidden object dependencies that the object dependencies tool might not catch. In my previous video on Friday, we talked about the object dependencies tool and how you can use it to see which queries or other objects are safe to delete that no one else depends on. Well, if you've got a form with a calculation in it like a DSUM and in that calculation you've got a query mentioned, well, the object dependencies tool is not going to find that because it's technically inside of a text string. So how can we locate things like this in our database that we might not want to delete?
Well, that's where the database documenter tool comes in. Now this tool is used so you can generate a file that has all of the information, all the properties, all the code, everything about all the objects or whatever objects you pick in your database. You can save it as either a text file or a PDF file or a Word document or whatever. And so you've got a backup of the stuff that makes up your database. You can essentially pick which tables, queries, forms, reports, macros that you want. Modules, you'll get your module code. All the objects around this one, if you want to go to all objects and hit select all, that selects everybody. But I'm warning you, this can take a long time if you want to do all of the objects in your database. Current database properties and relationships, that kind of stuff. So you can take all this stuff and export it. And save it. And this way later on, you can also go back and look and see if you made some changes where those changes are.
There are third-party tools that you can use to give them this information and it'll tell you where the changes in your database are. We'll talk about some of those at the end of the video. But what I want to focus on today is how we can use this tool to catch things that the object dependencies tool wouldn't catch. Like I mentioned before we put that the sum in our customer form. So let's analyze, let's just do a small segment of our database. Of course if you're looking for things that are safe to delete you're going to want to pick all of the objects. At least all of the forms, reports, macros, and modules.
But let's just start with, let's analyze the customer table and the customer form. I'll just pick those two objects. I'll hit OK and it takes a moment and it generates this object definition file. And if you look at it, if you zoom in, it's basically a report. If you zoom in, it'll say, okay, here's customer T, all right, it's got the date and time right there. Here's all the properties for that table. And here's the columns, each field, customer ID, it's a long integer, there's its properties, first name and so on. And there's a bunch of pages, how many pages? There's 37 pages, let me move this down just a bit. It's 37 pages just for these two objects. I did the whole database earlier and this is a pretty small database and it was over 300 pages, okay? But you could see, here's the information on the customer form. In fact, there's the code. It's got all the code in it. If you go back a page. Here's the code module in there. It's got all my VBA code, okay? To also find dependencies that might just be in our form fields and stuff. All right, they're not in the VBA code, they might be in the form fields.
For example, I know, where is that guy? It's in the family size field. Let's see if I can find it. There it is. These things are alphabetical. Family size. There it is right there. See? The control source is DSUM, order total, order summary queue. That's the query that I'm looking for. This guy, that doesn't show up in the dependencies tool. So how can I search through this thing?
Well, it looks like a report but it doesn't really behave like a normal report. You can't just save it as a report. What you can do is you can export it in different formats. Now you might be thinking to yourself, "Self, probably the easiest way to do this is to export it as a text file and then just search in the text file." But that's not the best way to do it. Let me show you what happens.
Let's export this as a text file. All right, browse to where you want to save it. I'm gonna save it in my drive and that gives it a weird name. That's fine. I don't care it. Okay, and save it is what type of text you want. Windows, DOS, doesn't matter, Windows is the default. All right, so it's exporting. This takes a second. All right, when we're done, this thing still flashing back there. Okay, I don't want to save those steps, go away.
All right, when we're done we get this nice text file. It's got all the same data. Okay, let's do a search. Control F, let's find that family size field. Well, we're actually really looking for order summary queue. Let's do a search for that. Oh, it doesn't show up. Why is it not showing up? That's the name of the field. Okay, how about, let's go back and check for family size. That's why, that's the last search because I was like, "Wait a minute, where is it?" And there's family size, okay. All right, so scroll down and look here, what's going on? Family size. Alright, that's the field in the table, let's keep going. Alright, okay. Alright, there it is, there it is, there's the text box. Let's see, okay, there's the controls. Oh, wait a minute, wait a minute, see what happened? Order summary queue got split on the multiple lines like that because of the text file. All right, so be careful of that, you can't rely on this being a text file because its format is important. Access is trying to do its best job to make this look like the report was. So what's the fix? Well, use a different format. I recommend PDF for searching. PDF search is fine. You can save it as an Excel file. It'll work just fine. You can save it as an RTF, a rich text file. Let's save it as a PDF.
All right, same thing. We'll save it to my drive, publish it. It actually runs faster than a text file. All right, now the PDF opens up. Now let's search for that order summary queue. Ready? Oh, look, 101, or 01, there it is right there. See, I found it. Because the PDF saves this stuff in proper table cells. See, so it doesn't break that onto different lines. And even if it did kind of split, it would still wrap around properly. Right, because this is like one big block of text inside that cell.
So if you're gonna use this database documenter to search for things that might not show up in the object dependencies, make sure you save it as a PDF or an Excel file or one of those formats that won't break the text up like text will. You'd think text would be the best one, but it's not.
Now, if you're interested more about this, I actually did a video a while back where you can save an object like a form as text. So for example, if you want to send just an object, just a report or a form, you want to send the information about it, you can send it to someone else by email, for example. This came up because one of my students, they don't allow attachments, so we could send it as text in an email. And you can save and load the object back and forth. There are also a handful of third-party tools that are better than the built-in database documenter if you're interested more on this. IslaDocs is a really good one, the Database Analyzer Pro. MS Access Gurus have one. A lot of these are MVPs like me. Crystal's got one, a code documenter. This one's been around forever, FMS's Total Access Analyzer. And they've got a Total Access Detective which you can use to detect changes between two versions. There's a list of them for you. I'll put links to all these down below if you want to check them out, tell them I sent you.
And just out of curiosity, I did try using chat GPT to see if it could detect the changes between two databases. I took a simple database like this. I just exported a file that included customer T and customer F, saved it as a PDF. Then I made a few minor changes, saved that as another PDF, uploaded those both to GPT and said, "Hey, can you tell me the differences between the two?" It read them, it analyzed them, but it got it wrong. It listed a whole bunch of stuff, and no, I just made two little changes. So I'm confident one day it'll get there, but it's not there right now. GPT is getting better at writing code, but it still doesn't know Microsoft Access. So hey, OpenAI, if you wanna use all my materials to train ChatGPT, I'm open, give me a call. Or don't call me, email me. I got lots and lots of material you can use to train the AI with, just saying.
Alright, that's going to do it for today's video folks. That's your tech help video. I hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
Using the Database Documenter in Access
Discover hidden object dependencies
Generate detailed object reports in Access
Exporting database information as text, PDF, or Word
Analyzing object definitions in Access reports
Searching for specific items within exported files
Impact of export format on text layout
Optimal formats for searching within exported documents
Using the Database Documenter to analyze specific tables and forms
Understanding properties and fields of database objects through documentation
Limitations of text file exports for comprehensive searches
Benefits of PDF format for maintaining layout in exports
Exploring third-party tools for database documentation and analysisQuiz Q1. What is the primary function of the Database Documenter tool in Access?
A. To modify database properties and relationships
B. To export data into another database
C. To generate detailed reports of database objects for backup and review
D. To create new database queries
Q2. Why might the Object Dependencies tool fail to identify certain dependencies in Access?
A. It can only detect dependencies in VBA code
B. It cannot detect dependencies mentioned inside calculations in forms, such as a DSUM
C. It is unable to access the properties of database modules
D. It only works with table fields and ignores form controls
Q3. What types of files can the Database Documenter export its reports into?
A. Only PDF
B. PDF, text file, Word document
C. Text file, PDF, Excel, RTF
D. SQL scripts, CSV
Q4. Why is exporting a Database Documenter report as a text file not recommended for finding specific terms?
A. The text file format does not support text searching
B. Terms might get split across multiple lines, affecting search accuracy
C. Text files are too large to handle efficiently
D. Access does not properly export data to text formats
Q5. What is the advantage of exporting the Database Documenter report as a PDF or an Excel file?
A. These formats are quicker to generate compared to others
B. Such formats arrange data in cells, preventing text from breaking incorrectly
C. They are the only formats that preserve color coding of the original report
D. Only these formats can be emailed directly from Access
Q6. What did the presenter mention about using an AI like ChatGPT for comparing database changes?
A. ChatGPT perfectly detected all changes in the databases
B. ChatGPT could not correctly identify the changes made to the databases
C. The presenter did not mention using AI for database comparison
D. GPT was used to detect dependencies in Access databases
Q7. Which of the following is true about the Database Documenter in Access according to the video?
A. It cannot document the code within database objects
B. It only allows exporting in a single-file format
C. It can provide an exhaustive document of selected database objects
D. It automatically emails the generated reports to users
Q8. Why did the presenter suggest using third-party tools over Access's built-in Database Documenter?
A. Third-party tools are always free to use
B. Access does not have a built-in Database Documenter
C. Some third-party tools offer advanced features like detecting changes between versions
D. Third-party tools are required to use the Database Documenter
Answers: 1-C; 2-B; 3-C; 4-B; 5-B; 6-B; 7-C; 8-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers how to use the Access Database Documenter tool to uncover object dependencies that the standard object dependencies tool might miss.
Previously, I discussed how the object dependencies feature in Access can reveal which queries or other objects rely on something and help you identify objects that are safe to delete. However, dependencies embedded inside expressions or calculations within forms, such as those using functions like DSUM, may not appear in the standard tool. For instance, if a query is referenced inside a calculation in a form control, the object dependencies tool will overlook it because the reference exists inside a text string or an expression, not as a direct link.
To thoroughly search for these types of hidden dependencies, you need to use the Database Documenter. This tool allows you to create a comprehensive file containing all the properties, settings, and details about selected objects in your database. You can choose to include tables, queries, forms, reports, macros, or modules, and export their information into formats such as text, PDF, or Word. This serves both as a documentation backup and as a way to track changes over time. For instance, you can document your whole database and, later on, compare this information to see what was altered.
There are third-party tools out there that do a similar job and may offer more features, but today our focus is on how to harness the built-in Documenter to find hidden dependencies that would otherwise go unnoticed.
If your goal is to see what you can safely delete from your database, you should at least document all forms, reports, macros, and modules. For demonstration purposes, though, let's keep it simple and document just the customer table and the customer form. Once you select these objects and let the tool run, Access generates an object definition report. This details everything about the selected objects: properties, fields, types, and, for forms, the VBA code as well. Even a small selection like a single table and form can produce dozens of pages, while an entire (even modest) database can go past 300 pages.
Upon reviewing the documented information for a form, you will see not just the design and code but also the properties for each control. For example, a calculation in a form field that uses DSUM and references a specific query will appear here, even if the dependencies tool missed it.
However, while the report looks like a standard Access report, it does not behave the same way and you cannot save it directly as an Access report object. Instead, you need to export it. This is where the choice of format makes a significant difference. You might expect exporting to a text file would be easiest to search, but this isn't ideal. When Access exports to text, it tries to preserve the appearance of the report, which means long text lines can get broken up. For example, if a query name referenced in a calculation spans multiple lines, searching for that query name directly may fail in the text file export.
To get the best results when searching for specific dependency references, export your documentation as a PDF or Excel file. These formats maintain the proper structure of information without splitting lines or misaligning data. Searching within a PDF, for instance, allows you to find any mention of a query, control, or function, as everything stays in its intended cell or paragraph.
If you need only to document or send information about a single object, such as a particular form, you can save just that item's details. This can be useful if, for example, you need to send information to someone by email and attachments are not allowed; the exported document can be copied into the message as text.
For more advanced documentation, comparison, or change-tracking needs, you may consider using third-party tools. Some well-known options include IslaDocs, Database Analyzer Pro, MS Access Gurus' tools, FMS's Total Access Analyzer, and Total Access Detective. Many of these solutions come from respected experts in the Access community, and links for these can often be found alongside my video tutorials.
I should also mention that while large language models like ChatGPT are improving, they currently struggle to reliably compare versions of exported Access documentation and accurately list differences between versions. While they can process and read through the content, they frequently miss subtle differences or report inaccurate results. Hopefully, this will improve in the future, but for now these specific comparison tools remain your best bet.
That covers today's lesson on using the Database Documenter to find hidden dependencies in Access that the dependencies tool cannot see. For a full step-by-step video tutorial on everything discussed here, you can visit my website at the link below. Live long and prosper, my friends.Topic List Using the Database Documenter tool in Access
Finding hidden object dependencies missed by the dependencies tool
Generating detailed reports of selected database objects
Exporting database object documentation to text PDF or Word
Analyzing properties fields and code in exported reports
Selecting specific tables and forms for documentation
Searching for text strings within exported documentation
Limitations of using text file exports for searches
Benefits of exporting documentation as PDF or Excel
Identifying calculation references inside form controls
Reviewing VBA code embedded in forms via documenter
Understanding report structure in Database Documenter exports |
