README visualize - x-ian/dha-mis-migration GitHub Wiki

Process:

1. Run queries in Access
2. Copy/paste results to text editor
3. Manually create .dot file for graphviz by search&replace
4. Run graphviz

Not necessarily all queries are captured. E.g. unions seem to not be included in the Access Metatables and therefore aren't listed as dependencies. Maybe also related to the case where a direct SELECT statement is given as a dependency.

Query1 (all Access objects, use columns QueryName and Source):

SELECT MSysObjects.Name AS QueryName, Nz([expression],[name1]) AS Source, MSysQueries.Name2 AS Alias, MSysObjects.Flags, t.Target
FROM (MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId) LEFT JOIN (SELECT ObjectId, Name1 AS Target FROM MSysQueries WHERE (Name1 Not Like "ODBC*") AND (Attribute=1))  AS t ON MSysObjects.Id = t.ObjectId
WHERE ((MSysQueries.Attribute=5)) OR ((MSysQueries.Name1 Like "ODBC*"));

Query2 (include only queries, no tables):

SELECT Query1.QueryName, Query1.Source
FROM MSysObjects INNER JOIN Query1 ON MSysObjects.Name = Query1.Source
WHERE ((MSysObjects.type IN (5)));

(pieced together from http://stackoverflow.com/questions/25199627/access-list-query-dependencies http://stackoverflow.com/questions/3994956/meaning-of-msysobjects-values-32758-32757-and-3-microsoft-access)

Modify CSV Export from Excel:

1. Replace '(tab)' with ' -> '
2. Replace ~ with '__'
3. Replace + with plus
4. Append trailing ; at each line
5. Add at beginning: digraph G { rankdir=LR;
6. Add at end: }

call graphviz with dot -Tpng export.dot >export.png

Replace png with svg for searchable file (e.g. open in Chrome)

dot -Tsvg analyse-only-with-queries.dot > analyse-only-with-queries.svg dot -Tsvg analyse-with-all-tables.dot > analyse-with-all-tables.svg