strive4peaceIf you like the Analyzer, please consider donating. 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
Quick Jump Index
Last update: 26 Nov 2013
Join the Analyzer Developer Project on CodePlex
free Analyzer for Microsoft Access on CodePlex
-- Document and Optimize Access Databases
download what professionals use to more quickly figure out what is in an Access database -- Free!
Analyzer demo video on YouTube
For the NEWEST VERSION... go to the Analyzer for Microsoft Access Download page on CodePlexWith 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. For years, I have depended on the Analyzer and run it on almost every database I touch.
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. There are those who are greedy and will try to keep you from learning about this tool -- give it a try. 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 Analyzer 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 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, my friend, 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!
The analyzer does not look at queries, forms, reports, macros, or modules and it matters not at all if the database to analyze even has them.
The best structure documentation for your database is a well-laid out relationship 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.
sorted by Field Name and then by Table Name ~~~~~~~~~~~~~~~~~~~~~~~~ - Field Name - Table Name - Data Type - Field Size - # Values Filled - # Unique Values - Field DescriptionThe FieldList report serves as a guide to look up which tables contain each field, and as a tool to ensure that key fields have the same data type. The Fieldlist Report is also useful to see where relationships should be and where data should be normalized ... helps if fieldnames are consistent ...
Relationships Report ~~~~~~~~~~~~~~~~~~~~~~~~ - Primary Table - Foreign Table - Key field (and Data Type and Size) - Foreign Key Field (and Data Type and Size) - RI - if Referential Integrity is enforced - Link - if tables are linked or residentThe Relationships Report shows visible as well as hidden relationships
Table Indexes Report ~~~~~~~~~~~~~~~~~~~~~~~~ - Tablename - # Relationship Indexes - # Table Indexes - Number of Records if >0 - Number of fields ~~~~~~~~~~~~~~~~~~~~~~~~ - Index Name - Key field (and data type and size) - Foreign Key field (and data type and size) - # and % filled - # and % Unique values - if foreign key, main table and fieldnameThe Table Indexes Report shows visible indexes as well as those hidden indexes created by Access to enforce referential integrity (RI) on defined relationships, referred to as Index for RI
You can see visible indexes when you are in the table design view. This example has just one visible index for the PrimaryKey.
When you look at the example Table Indexes report, you see there is also a hidden index on the TID field in the Flds table.
TID is a foreign key and, in the relationships window, there is a relationship with RI from the Tables table. Because Access automatically creates relationships for foreign keys when you enforce referential integrity, there is no need for you to make visible indexes for them ...
... unless you want to use the index name in code — then you might want to create indexes on foreign keys yourself with more meaningful names.
Each time you use the Analyzer, a record is created in the Runs table. Once the analyzer fills Tables, Fields (Flds), Indexes (Idx, IdxFlds), and Relationships (Rel), it looks up and populates foreign keys. It also stores some calculations such as the number of hidden and visible table indexes. The Analyzer give you information about your databases according to this diagram:
If you really want to unleash the power that the analyzer can give, you will 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.
SELECT DISTINCT R.RunID , R.Rpt , R.db , getLetter([T]) AS Letter , getSort10([T]) AS Sort10 , T.TID , F.FID , T.T , F.FldNum , F.Fld , F.IdxString AS IdxF , IIf([Auto],"A","") AS A , F.DatType , F.DataType , F.FldSize , F.MaxLen AS Big , F.Filled , F.Uniq , [Filled]/[numRecords] AS pcF , IIf(nz([Filled])=0,0,[Uniq]/[Filled]) AS pcU , T.numRecords , T.numFields , F.DefVal , [min] & IIf(nz([min])<>nz([max])," --> " & [max]) AS MinMax , T.CreateDate , T.ModDate , T.Desc AS TDesc , F.Desc , T.IsLink FROM (Runs AS R INNER JOIN Tables AS T ON R.RunID = T.RunID) INNER JOIN Flds AS F ON T.TID = F.TID ORDER BY getSort10([T]) , T.T , F.FldNum;The reason there is no criteria is because the WHERE clause of OpenReport is used when the report is generated from the menu. If you do, indeed, want the Deep Analysis to use other criteria, then save a copy of qFields without any modifications (so you can put it back for the Analyzer). Now modify qFields to add your criteria and open the Deep Analysis report (zDeepAnalysis) manually.
When you make a copy of this query to modify for your needs, keep main columns like
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 go to CodePlex.
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
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-2016 Crystal Long | All rights reserved | Last Updated Nov 2016 |