Diagram Access DB Process with Visio

[Chris Moore]

In my organization we make extensive use of Access to perform tasks and
processes that in my mind basically amount to data transformation. For
example we may import a text file or Excel spreadsheet from an external
source into an Access database, then run a Make Table query that combines
that data with data from a different source into a new table, then run an
Update query to format some of the columns in that new table differently so
that they match the data types in yet another table which then allows us to
join those two tables in a query to make yet another table, etc. etc. etc.
The complexity of it all can get bit overwhelming. I'd like to create some
graphical representations of these processes to make everyone's life a little
easier and I thought Visio would be an ideal tool for the job.

However I'm having some difficulty finding examples of such a diagram. I
can't be the first person to have tried this, I'm wondering if there are any
existing conventions for diagramming this type of process. For example: How
should I properly represent a Make Table query vs. an Update query? I'm also
struggling with how much detail to include. For example if I'm going to run a
Delete query to delete certain rows from a table I'd like the diagram to give
some kind of indication of what the logic is behind the delete. Perhaps I
could put the query's criteria in a field on the shape that represents the
delete query. I don't know, but if there is already a standard or convention
for representing these types of objects and actions I'd prefer to adopt that
rather then invent something myself. Any comments would be appreciated. Thank
you.

-Chris

 

[Jeanette Cunningham]

Hi Chris,
I'm not sure that there's much info around about this.
One thing that I am starting to do is to use screen prints saved as jpg's.
For example, I will do a screen print of the list of tables in a database
and use MSPaint to highlight the particular tables that need to be changed
or are participating in a particular manoeuvre. You can do the same with
queries in icon view, in design view, in datasheet view.

I find that suits my needs in some ways as I like the visual representation
instead of just written step by step instructions.

These screen prints are quick and easy to do and to my mind a lot quicker
than drawing stuff in visio.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John... Visio MVP]

In my organization we make extensive use of Access to perform tasks and
processes that in my mind basically amount to data transformation. For
example we may import a text file or Excel spreadsheet from an external
source into an Access database, then run a Make Table query that combines
that data with data from a different source into a new table, then run an
Update query to format some of the columns in that new table differently
so
that they match the data types in yet another table which then allows us
to
join those two tables in a query to make yet another table, etc. etc. etc.
The complexity of it all can get bit overwhelming. I'd like to create some
graphical representations of these processes to make everyone's life a
little
easier and I thought Visio would be an ideal tool for the job.

However I'm having some difficulty finding examples of such a diagram. I
can't be the first person to have tried this, I'm wondering if there are
any
existing conventions for diagramming this type of process. For example:
How
should I properly represent a Make Table query vs. an Update query? I'm
also
struggling with how much detail to include. For example if I'm going to
run a
Delete query to delete certain rows from a table I'd like the diagram to
give
some kind of indication of what the logic is behind the delete. Perhaps I
could put the query's criteria in a field on the shape that represents the
delete query. I don't know, but if there is already a standard or
convention
for representing these types of objects and actions I'd prefer to adopt
that
rather then invent something myself. Any comments would be appreciated.
Thank
you.

-Chris

I have not come across a standard set of shapes for laying out work flow as
it relates to databases. Normally what I have seen are shapes that
represents the transitions and comments on the line representing the
actions. Just sit down and decide what actions you need to represent and
come up with a set of shapes your organization can agree on. You will then
have a cheat sheet to help understand the drawings. Over time, you can
refine the shapes, but for now, just keep it simple.

After collecting Visio shapes for eighteen years, I am reasonably
comfortable saying that there is no widely held standard for this type of
application. (Though there should be)

With Visio, it is not a trivial task, but it is possible to write code to
read things like JCL or SQL to create Visio drawings so the process can be
automated.

Once you get into Visio, there are more experts hanging out in the
microsoft.public.visio.* newsgroups.

John... Visio MVP

 

[Chris Moore]

This confirms my suspicions. Thanks to both of you for your feedback.