This is one of my sample databases
I don't remember how much data it has,
or some of the important design details.
So I wrote code to Document Tables to Excel
and posted it on MsAccessGurus.com
Hi, this is crystal
This is a sample database I'll be using
to demonstrate different types of queries.
The presentation I'm giving is with Maria Barnes
on the last day of this month, August 2021
for AccessUserGroups.org
Anyone who loves Access is welcome to attend.
I'll put a link in the video description.
If you miss the live presentation, it will be posted.
Right now, I need to know full details of what's in here,
and what the design is for each table.
Lots of times, I write things for myself and then share with others, why not?
What comes around goes around.
If what I post helps you,
if you have a little extra, click Donate
on one of my web pages.
And when you need a tutor
to empower you, think of me,
and let's connect.
Each drop raises the sea.
To run this code, you'll need 3 modules:
1. the module with Document_Tables2Excel
2. and you'll also need a module to handle Properties
3. a module that has a function to GetDataType,
the string value when you send it a number
Once you have these 3 modules in your database,
compile and save.
Then activate the Document_Tables2Excel module,
scroll to the first procedure,
launch_Document_Tables2Excel, and run it.
If you leave the optional pbGetData parameter as false,
then it just makes the summary tables,
and that's what most people will need.
If, however, you want to see all the data too --
realizing these are just point-in-time values,
you can set pbGetData to True,
debug, compile, and run.
Now let's see what we have.
The file prefaced with TableSummary
just has the structure and some statistics.
That's the first one we ran, and the default.
Let's open it up and see what it's got!
The first sheet is a ListOfTables,
and has a list of all the tables.
You can also see how many records are in each table,
how many fields each table has,
and an estimated record width for standard data types.
Column E has a hyperlink to the data structure
on the 2nd sheet, DataDictionary
What do I want to see more about?
hmm, Students
The first field is CID -- Contact ID
This links a student to a contact.
Next is the Student number.
The primary key of this table is StudentID.
Right now, there isn't an indicator to tell you that ... room for improvement.
The basics for a Data Dictionary are
tablename, fieldname, data type, and size --
but we are going to see way more with this little tool.
Notice that this data dictionary
not only documents basic structure,
but it also tells you other important values like
field Description, Format, and DefaultValue.
done looking -- let me go back to where I was.
This is so fast!
When you press Ctrl-G to get the Goto box,
the last ActiveCell address is the default.
Therefore, if you only scroll around
and don't make anything else active,
a quick way to get back to where you were
is to press Ctrl-G, accept the default,
and then press ENTER or click OK.
So now I want to look at the Contact table definition.
The hyperlink takes you to the bottom of what you want to see.
Everybody is a contact -- all the students, teachers, janitors, landscape artists, and so on.
The contact table has the full names of everyone.
And although the related contact tables aren't in this database, they could be.
The related tables keep track of phone numbers,
addresses, email addresses, web sites, etc.
I have 2 contact management systems
you can download for free.
One is a light version, which is good to start with,
unless you really know what you're doing with Access.
The full version is older, and so intense
that many were baffled at how it works
even though everything is open and well-documented.
There's just a lot there.
The full version has lots of extra tables
so you can grasp logic to hook into what
your specific business needs to track.
It's also split into a front-end and back-end
and has code to manage users.
I like the names better in the light version,
but the full version has more power.
This table, c_contacts, has a couple calculated fields.
This makes is easy to get the full name
in a combobox to pick a contact
without having to write an expression.
The DocumentTables tool documents
InputMask, ValidationRule, and ValidationText --
none of which are used here.
But if they were, you'd surely want to know.
It also tells you if a text field has Unicode Compression.
A "+" sign in this column is what you want.
Interesting that calculated fields don't use Unicode Compression.
The property isn't exposed when you look at the table design.
It might be able to be set in VBA, but I didn't try.
Interesting that the estimated width is the same for both of the calculated fields.
Because there is no Unicode Compression, the Size is multiplied by 2.
Half of 486 is 243.
For fields that aren't text, Unicode Compression doesn't matter.
The Documentation sheet has a note for each column
in case you want more details about what's in there.
So this is the Summary data.
Now let's look at the version of the file with data from tables too.
On the first sheet, the table summary, called ListOfTables,
you'll notice that column A is now called Sheet_Table, and is a hyperlink.
There are 41 records in the Classez table -- let's see what they are.
Click on the sheet/table name
Here they are.
And I haven't changed the ActiveCell
so guess what I can do to go back quickly?
You got it, Ctrl-G, Enter
Here I am, back at the ListOfTables.
In the file with the full documentation of tables,
the first 3 sheets are basically the same as the summary workbook:
ListOfTables, DataDictionary, and Docmentation
The main difference is that this workbook
also has a sheet with data for each table.
These 3 tables at the end, starting with X,
were an experiment since Excel sheet names
can't be as long as Access table names.
On the ListOfTables sheet,
column A has the sheet name, and is a hyperlink.
Sheet names in Excel can only be 31 characters.
Table names in Access can be 64 characters.
Column E has the full table name -- and now you see the difference.
The DataDictionary sheet has the full table name.
Do you like what this code does? Let me know in the comments.
If my code helps you, and you can help me back,
go to the bottom of my web page and click Donate.
If you need more intense analysis, Get the Analyzer.
It's also free -- just don't swipe the ideas and sell tools.
I connect and tutor people.
We work together on whatever it is you have.
We make it a success and you shine with your peers.
Let's connect.
Thanks for joining me.
Through sharing, we'll all get better.