strive4peaceIf the Analyzer has helped you, would you like to donate? thank you.
|Access Basics||Video Tutorials||Learn VBA||ListFiles Database||Random Picker||Error Handling||Reference Database||Whistles & Bells||Help Examples||Analyzer||Code Documenter|
Document Access databases... and anything Access can link to!
Table Summary Data Dictionary including value analysis (Deep Analysis) Field List Relationships Table Indexes
Analyzer demo video on YouTube
With the Analyzer in your toolkit, you can get a handle on what you have in Access ... structure, relationships, and data. What is each table structure? What indexes are defined? What relationships are set up? How many fields have information? How many tables have records? ...? ...? Compare one database to another, combine analysis from multiple back-ends for a higher level look, generate a list of BE tablenames for FE procedure to loop through and automatically link to, add fields for mapping imports and exports, create a field for new names so changes can be planned ... and the list goes on.
The Analyzer is important to keep free. Understanding and helping others understand is essential to creating good databases.
A little history ... back in the 90s, I saw the Analyzer reports instantly provide illumination. There was less information captured and calculated — even then, they were great. Hoping that others would see the value, I tried to market the Analyzer. Note to self: stick with developing ... ended up selling one copy (thanks, Bill <smile>) For years, I would run the Analyzer for anyone who wanted to see the results. Now I freely share it with the hope that you will respect other developers and continue sharing. If you build or use Access databases, this is a must in your toolkit.
If you build or use any database that Access can link to, this is an important tool for you as well. I feel a very personal attachment to the Analyzer. It is the main application I have developed to help myself (so often we developers are building tools for everybody else while our own needs go neglected). The Analyzer provides a way to understand and optimize the database structure and relationships. Because the it also looks at the data that is stored, the more records you have in the database to analyze, the more the Analyzer can tell you.
What is the maximum number of characters ever used in a short text or long text (text or memo) field?
... you will see fields that need their Field Size adjusted (shortened or lengthened).
You will see descriptions if you have filled them out -- for both fields and tables. And this, is why I originally wrote the Analyzer ... the data dictionary could be gotten from data gymnastics on the object analyzer report saved as a table ... but a quicker data dictionary was also a mighty convenient way to show descriptions that there was no built-in way to document.
Because the Analyzer gives you example values of the data that each field contains, you can often see information that needs a better place to go. Perhaps you need to expand and make your structure more flexible to create specific places for valuable information often lost in notes like names, contact information, amounts, and various dates.
The Field List Report shows every field in the entire database in alphabetical order ... now you know why I comment on analyzing bigger back-ends with hundreds of tables... find out fast where each field of information is stored. Because fields with the same or a similar name are next to each other, you can quickly find differences in name, description, size, and data type.
the Analyzer gives you a fairly comprehensive value analysis of the data that you and your colleagues have entered. You can see which fields are being used and how much. It reports the number of fields and records in each table along with a myriad of other highly useful information.
The Analyzer Reports are good for documenting a database — whether you are developing and showing structure, or you are tracking database useage. the Analyzer enables you to come up to speed quicker on a database that someone else created ... to slice and dice the information however you desire. That is a must for someone like me who designs and develops complex applications, re-engineers, rebuilds, studies what others have created, ... and teaches anything I can ...
Advantages to storing analysis results from different databases in one place include the ability to compare structure from a year ago to structure now ... analyze your working database without importing code and objects ... find differences in separate database files ...
The database you are analyzing can have resident and/or linked tables. This means that if you want to analyze an ODBC-compliant database like SQL Server , you can create (or use) an Access front–end (FE) database with links to the desired back-end (BE) SQL Server tables. Be sure to save the username and password in the connection string as the Analyzer is not set up to prompt for credentials. Then you can run the Analyzer on the FE database containing the linked tables. Voila! Finally a way to get useful structure and relationship information anytime!
If you want to analyze code, get the free Code Documenter
The best structure documentation for your database is a well-laid out relationships diagram and reports generated by the Analyzer (well maybe that is a bit partial) ... enjoy ~~
Analyzer Data Structure
Download, FAQs, and Comments
sorted by table name ~~~~~~~~~~~~~~~~~~~~~~~~ - Table Name - Table Description - whether it is linked - Number of Fields - Number of Records - Date Modified - Number of Indexes If a table is linked, the program also records the path, filename, and object name to the link and gets table descriptions from the BE if they are filled out.Tip: use the tablenames stored in the analyzer's Tables table to make a list of tablenames in a back-end (BE) that you want to automatically link to from the front-end (FE).
Data Dictionary with Value Analysis, sorted by Table Name and then Field order ~~~~~~~~~~~~~~~~~~~~~~~~ - Table Name - Table Description - Number of Fields - Number of Records - Date Created - Date Modified ~~~~~~~~~~~~~~~~~~~~~~~~ - Field Order - Field Name - Field Description - A if Autonumber - Data Type - Field Size - Maximum Length used (text fields) - Indexes -- Primary Key, Required, Unique, Foreign Key - # Values Filled - # Unique Values - % Filled - % Unique - Default Value - Minimum Value - Maximum ValueThe Deep Analysis Report is like a Data Dictionary on steroids and is especially useful for optimizing a database.
You can see visible indexes when you are in the table design view. When you look at the example Table Indexes report, you also see hidden indexes.
Each time you use the Analyzer, a record is created in the a_DBs table. Once the Analyzer fills information about Tables, Fields, Indexes, Relationships and more, it can calculate statistics on the data in your database.
If you really want to unleash the power that the analyzer can give, take advantage of your analyzer data. The more you understand about how the analyzer stores this information, the better you will be able to see what you can get.
Read each Field Description. Note the Field Name, Data Type, Field Size, and other properties. Look at the visible table indexes.
Tip: copy the source of the Deep Analysis report, to make a new query with information you want to transfer to Excel. Once in Excel, you can easily add, delete, calculate, and move columns ... and don't forget to turn on AutoFilter <smile> This gives you an easy way to document and plan.
Sometimes I use a customized version of the Analyzer that analyzes batches of databases to do structure comparison and data transfers (manual as well as automated).
EXAMPLE: combine the data from multiple BE (back-end) databases into one master database.
To make it easier to delete data in tables to prepare a master, loop through their names using the analyzer table and construct SQL to delete the records they contain if a flag field in Tables is set. You will also want to populate the Ordr field that is already in Tables for you to use.
Batches – BatchID - autonumber, PK – batDat - date/time, date/time batch was done – batName - text, batch name – batPath - text, 255, root path – etcUse an Append Query to create a record with BatchID = 0 for your master structure (easier to remember).
Now add one more record, using the Datasheet view of Batches. I will assume the autonumber value is 1 (one). This will be used to group the the back-ends to combine.
DBs – DbID - autonumber, PK – DbCode - text, 10, short code for database name – dbName - text, database name – etcDBs identifies the structure set of the database. Create one record for each of the back-ends you analyzed except the master.
As with the Batches table, use an Append query to add a record with value = 0 for the master database.
– FID_ — Long Integer, no default valueThis will be used for mapping. By mapping the structure, Access has a way to be know what to transfer and where to.
Use underscore after the same fieldname of the PK to indicate that the value is the PK of another record in the same table.
– BatchID — Long Integer, no default value, FK to BatchesIn our example, BatchID is 0 (zero) for the master data set and 1 (one) for the back-ends to combine.
– DbID — Long Integer, no default value, FK to DBsEach BE is a particular database type identified in DBs.
Add more fields as desired and populate them.
PK = Primary Key FK = Foreign Key
When you are ready, link to all the tables in the master database. Preface each tablename with "master_" (or "m_" or something). This does not change the source table name but will group master tables together in an alphabetical list and ensure that tablenames are unique.
Now link to all the tables in the BEs you want to combine. If you have tables with the same name that contain different data, you will have to make the names in the FE unique.
Now make Append, Update, and Select queries to transfer data and look at the progress.
Maybe you are only transferring data as a one-time task. Maybe you will do it a few times. In either case, it is a good idea to save queries as you make them with a name something like this:
q01_app_People_Customers q01: this is the first step app: append query People: table that is getting records appended to Customers: table that records are coming fromEach time you run an action query, see how many records were affected (one reason I like to use code since I have a procedure that times SQL statements and reports affected records).
For the LATEST VERSION, which is still free of course, download from GitHub.
To create additional reports and customize the Analyzer:
Your main interest will be the data that the analyzer generates.
The analyzer will only work on databases with ULS (User-Level Security) if you follow these steps:
One way to document a troublesome database is to skip the value analysis portion of the analyzer by unchecking Include Value Analysis before you Run Analyzer
If you have comments or want to tell me how the Analyzer helped you, thank you — leave your comment on a thread I am currently posting to OR
thanks to the many developers who have helped and given ideas
Free Book on Access: Access Basics
Free Tutorials: Video Tutorialsfor information on private programming and training, or if you have comments and ideas, I'd love to hear from you
Copyright © 2009-2017 Crystal Long | Last Updated Aug 2017 ... still changing ...