strive4peace

If the Analyzer has helped you, would you like to donate? thank you.
thank you

Analyzer in Microsoft Access

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

DOWNLOAD from GitHub

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 ~~

Quick Jump To Sections

Database Analyzer Reports

Table Summary
Deep Analysis
Field List
Relationships
Table Indexes

Analyzer Data Structure

Download, FAQs, and Comments




Table Summary

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



Deep Analysis

		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 Value
		
The Deep Analysis Report is like a Data Dictionary on steroids and is especially useful for optimizing a database.



Field List

The 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

The Relationships Report shows visible as well as hidden relationships

Table Indexes

The 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. When you look at the example Table Indexes report, you also see hidden indexes.



Relationships Diagram

In addition to the report examples showing structure of the Analyzer, there is nothing that can give you a bird-eye view faster than a good relationships diagram. Have a look at it! Field descriptions are filled in tables -- and you can also analyze a copy of the Analyzer to learn it faster ~

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.

qFields Query

This is the query used to feed the Deep Analysis Report ... so don't change it. Instead, make a copy of it and modify it until you have what you want. Most often, you will probably want to export a list of tables and fields to Excel. If you start with the qFields query, you can rearrange columns and delete information you have no use for.



Make Time

... to look at the design view of each table.

Read each Field Description. Note the Field Name, Data Type, Field Size, and other properties. Look at the visible table indexes.


Customize the Analyzer

The Deep Analysis report gives you every piece of information about table and field structure that the Analyzer displays. For this reason, the report is based on a query so it can be copied and customized. The name of this query is qFields

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.

Make a Master Database

Think of your Master Database as a cookie-cutter to make databases just like it. It will be your latest and greatest structure, have desired lookup values populated, and be empty of any values which need to be converted or specified by the user

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.

Copy the Analyzer Database

Make a copy of the Analyzer so you can modify it
  • Run the analyzer on your master database.
  • Run the analyzer on the back-ends to combine.
  • Modify the structure of the analyzer.

Create tables

BatchesBatchID - autonumber, PK
 – batDat - date/time, date/time batch was done
 – batName - text, batch name
 – batPath - text, 255, root path
 – etc

Use 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.
DBsDbID - autonumber, PK
 – DbCode - text, 10, short code for database name
 – dbName - text, database name
 – etc

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

Add Fields

Add fields to the Flds table:
FID_ — Long Integer, no default value 
This 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 Batches  
In 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 DBs

Each BE is a particular database type identified in DBs.

Add more fields as desired and populate them.
		PK = Primary Key
		FK = Foreign Key
		

Plan and Perform Data Transfer

You can use your customized analyzer structure to plan and perform data transfer.

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 from 
		
Each 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).


Download Database and Help Files

OLD VERSION: Access 2000 version
Analyzer_2K_081113_16_9p__MDE_PPS_DOC_TXT.zip

For the LATEST VERSION, which is still free of course, download from GitHub.
https://github.com/strive4peace/Analyzer


The new interface is different than the screen shots shown.
  1. Browse... to database to document
  2. click Run Analyzer
  3. look at Reports
The new versions of Analyzer gets a LOT more information. The analyzer is constantly being updated and expanded. Since you can analyze several databases and combine results, and analyze big data, watch your file size! Access databases are limited to 2 gb.
I often put analysis of databases for a project (at their various stages) in its own Analyzer database (and reflect the project in the filename). The closer I get to a deliverable, the more likely I am to use an empty Analyzer to run the Analysis. Then I link to the Analyzer tables and run update and append queries to put data into, for instance, a table of tables I keep in most of my FE databases.

Because the Analyzer is open source, you can modify it. For instance, it you run it on other databases such as SQL Server, there may be additional properties you wish to document. Simply look at the table definitions and use the same naming convention to add more properties.

Here is an older version (now there is even more) of the Relationships Diagram:

<<database analyzer Relationships1>>

<<database analyzer Relationships2>>

<<database analyzer Relationships3>>


Do YOU want to contribute? If you explore the tables, you will see a LOT more information is collected than is reported.

To create additional reports and customize the Analyzer:

  1. make a blank database and link to the Analyzer tables
  2. If you have more than one analysis, filter on DbID
    (each time you analyze a database, DbID is the PK in a_DBs)
... then you can simply send me your report database to add the good stuff you have defined to share with others ;)
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:
  1. open your protected database with your username and password
  2. close your database but don't close Access
  3. open the Analyzer and run it on the protected database
Another thing you can try on a database that has ULS imposed is making a blank database to link to all the tables ... be sure to check the box to Save username and password.

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


Frequently Asked Questions (FAQs)

]

"Regarding the analyzer…is it pretty easy to run?"

— Ward


Thank you for asking, Ward. Yes!
It is as easy as 1,2,3 ...


When you open the Analyzer, you are given a menu
  1. click the Browse... command button to pick your database.

  2. choose Run Analyzer

  3. Look at reports Once the analyzer is done, the 5 report buttons become enabled.




Print the Table Summary and Deep Analysis reports. Glance at the others on screen. It is likely that you will run the analyzer again after you make changes.

In the 2010 version ...

1. Browse...




2. Run Analyzer



3. Look at Reports




Comments

"Happy New Year to you too!

"I downloaded and used your analyzer tonight - nice job! It worked flawlessly on a couple of my production apps. I made PDF versions of the Field List and Deep Analysis reports and they will serve as great documentation for the current version ... Thank you."

— John Mishefske, Microsoft MVP, Access


Through Tigeronomy Software, John designs and constructs a variety of computer systems from single PC apps to large scale web applications for financial, insurance and automotive industries including comprehensive subrogation and overpayment tracking for a Fortune 100 company, automotive industry inventory and sales tracking, real estate work flow programs, print shop estimate and invoicing apps, concert ticket systems, and home remodeling support systems. He also provides sub-contracting services for other companies and contractors. John is a brilliant developer with a deep level of knowledge encompassing several platforms, a musician, good-hearted, and an outstanding person to know.

"... thank you so much for a very special gift!!! I have been puttering with the Data Dictionary Tool for about 20 minutes and I am amazed at the power and completeness. "

— Jack Cowley, Microsoft MVP, Access

"Good morning, Crystal -

"Wow, I am completely blown away by your Data Dictionary Tool! I read your entire introductory missive explaining the concept and history behind it in spite of being crazy-busy yesterday - I just couldn't stop reading until I got to the end - I was so intrigued! That is an amazing piece of work!

"This is going to be very helpful as I go through and try to backtrack and document things in my current database that I should have been more militant about doing all along but was just too immersed in trying to get things to work and didn't do as thoroughly as I should.

"It will also be tremendously helpful in going through all my old databases to help make sense of them. My version back then of what I considered to be "good enough" documentation was fine when it was all fresh and I was constantly referring back to them, but it hasn't held up well over time, because I have had one disappointment after another as I've had those moments like, "Oh, I remember I did such-and-such in the menu database; I'll just go see what I did there and replicate it." Easier said than done - there are many "such-and-suches" that work wonderfully in the old databases, but are a total mystery now as to how I made it happen LOL.

"This is WAY cool. KUDOS to you for creating and making available to the world such a fabulous tool!

"Have a great day!"

— Karen

"I want to thank you once more for the Analyzer... when you originally posted the link I had no idea the power of this tool. It is nothing short of amazing, and quite unlike anything I have ever seen before...

The concept of letting your data tell you about the structure of an application... at some point during the never ending learning process I came to the realization that it can often be difficult to know exactly what works best until you have some data there, but the depth that you take this to (and the depth that the Analyzer takes the user to) is something else entirely, nothing I have ever considered or even heard mention of before.

"This concept brings a momentus change to the way I will look at an application. I'm very excited to be able to start seeing things from this new perspective... after a time it becomes rare to come across an idea or concept that can have such an impact. So I owe you yet another thanks :) "

— Jack L

"I have used Crystals database analyzer many times and had great results.

"Best regards"

— Chris


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 written code and built forms and reports ... Adrian Bell, AD Tejpal, Allen Browne, Anders "TheSmileyCoder" Ebro, Bill Mosca, Brent Spaulding, Mark Davis (CyberCow), Wayne Phillips, and more ...




Free Book on Access: Access Basics

Free Tutorials: Video Tutorials

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