My Analyzer
... the ultimate Data Dictionary Tool
by Crystal
My Analyzer
|
|
H A P P Y N E W Y E A R ! |
Table Summary
Deep Analysis including Data Dictionary
Field List
Relationships
Table Indexes
With my 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.
Table Summarysorted 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 AnalysisData 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. 
Field Listsorted 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 ... 
RelationshipsRelationships 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 IndexesTable 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. 
Relationship DiagramIn 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 relationship diagram.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. Design of Tables:
Design of Flds (Fields):
qFields QueryThis 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.qFields DesignHere is the design view of qFields:
qFields SQLHere is the SQL view of qFields: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. Go To Top Quick Jump qFields Datasheet View showing Flds tableThere are probably more fields here than you want. And some fields you want that are not showing -- like the value analysis columns. I hid columns in this screen-shot (they are really there, just look at the SQL) since, in my example, they were empty.When you make a copy of this query to modify for your needs, keep main columns like
Take TimeTake 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 AnalyzerThe 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 qFieldsTip: 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 my 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 DatabaseThink 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 userTo 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 My Analyzer DatabaseMake a copy of my analyzer so you can modify it
Create tablesBatches – 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. Add FieldsAdd fields to the Flds table:– 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 Plan and Perform Data TransferYou 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 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). Download Database and Help FilesAnalyzer_2K_081113_16_9p__MDE_PPS_DOC_TXT.zipThe analyzer may be freely distributed as an MDE. If you are selling an application and wish to incorporate code or ideas from the Analyzer, you will need to obtain written permission from me. I posted an MDE because I am not ready to divulge all my secrets — but that is ok as your main interest is the data that the analyzer generates — which you can freely get to. contents:
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 issues because something in your database snags the code and since you are running an MDE, you can't get into it to make changes, then send me the database you are unsuccessful at analyzing. First, shoot me an email with "Analyzer" in the subject and describe the problem — and tell me how big your zipped, compacted database is, what version it is, and whether or not it has security. Once I respond, you can send the file and I will run the analyzer for you. Frequently Asked Questions (FAQs)
Comments
If you have comments or want to tell me how my Analyzer helped you, thank you — leave your comment on a thread I am currently posting to OR |
|||||||||||||
Free Book on Access: Access BasicsFree Tutorials: Video Tutorialsfor information on private programming and training, or if you have comments and ideas, I'd love to hear from you |
