Data Integrity --> Synchronisation between a Database & Visio Drawing

[M@]

Hey Visio Developers,

I am seeking some advice on a Data Integrity issue I am currently presented
with whilst developing a Visio Solution.

BACKGROUND:
I am currently developing a Visio solution which links a Visio Document to
an Access Database.
The language of choice is VB 6.0, using ADO & SQL for database access. The
Visio version is 2002 on Windows XP Pro.

When a user places shapes on a page they are able to 'Right Mouse' the Shape
and enter information relating to that shape via a VB form. This data is
stored in the Access database. When a user connects two shapes this
connection information is stored in the database. When a shape is deleted,
the database is updated to reflect the deleted shape. When a connection is
deleted the database is updated to reflect this deleted connection.

PROBLEM:
If Visio crashes, or the document is NOT saved when Visio Exits, data in the
Visio Drawing may become different to the data in the database.

EXAMPLE:
A user opens the Visio Document & makes some changes. Shapes are added,
their details entered into
the database. Connections are made & these are recorded in the database.
Now, the PC CRASHES, causing the user to reboot.
The user opens Visio to find the shapes & connections are no longer in the
drawing.

If the database is examined these shapes & their details will be recorded,
as are the connections made by the user prior to the crash. As the database
is updated in real-time (once the user clicks OK to close the form) this
situation occurs.

HELP / SOLUTION:
What would your suggestions be with regards to this situation?
How would I mantain data integerity in face of the above events?

Many Thanks
M@

P.S. Would love to hear as many suggestions a possiable.

 

[Al Edlund]

I guess the question starts off with which is supposed to be the master
copy? If the drawing is the master then you have to delete the out-of-sync
data records, if the database is the master then you have to have saved
enough data to recreate the lost objects.
Al

 

[Bruce]

The easiest solution would be to write code in the terminate event of your form to do a save for your Visio document

----- M@ wrote: ----

Hey Visio Developers

I am seeking some advice on a Data Integrity issue I am currently presente
with whilst developing a Visio Solution

BACKGROUND
I am currently developing a Visio solution which links a Visio Document t
an Access Database
The language of choice is VB 6.0, using ADO & SQL for database access. Th
Visio version is 2002 on Windows XP Pro

When a user places shapes on a page they are able to 'Right Mouse' the Shap
and enter information relating to that shape via a VB form. This data i
stored in the Access database. When a user connects two shapes thi
connection information is stored in the database. When a shape is deleted
the database is updated to reflect the deleted shape. When a connection i
deleted the database is updated to reflect this deleted connection

PROBLEM
If Visio crashes, or the document is NOT saved when Visio Exits, data in th
Visio Drawing may become different to the data in the database

EXAMPLE
A user opens the Visio Document & makes some changes. Shapes are added
their details entered int
the database. Connections are made & these are recorded in the database
Now, the PC CRASHES, causing the user to reboot
The user opens Visio to find the shapes & connections are no longer in th
drawing

If the database is examined these shapes & their details will be recorded
as are the connections made by the user prior to the crash. As the databas
is updated in real-time (once the user clicks OK to close the form) thi
situation occurs

HELP / SOLUTION
What would your suggestions be with regards to this situation
How would I mantain data integerity in face of the above events

Many Thank
M

P.S. Would love to hear as many suggestions a possiable. :-

 

[Amitai Palmon]

Hi Mr. M@
I had exactly the same problem.
Then I attended a 3 day Visio course, saw many other Visio solutions and
understood what is the right approach.

The Mistake
============
There is no point to save the drawing both in Visio and the database.
You will ALWAYS encounter synchronization issues.
The drawing should be stored in one place: Visio OR the database.

Solutions A
============
The prefered solution is to save the drawing in Visio. That's what Visio is
made for.
I would use the ShapeSheet mechanism to save as much data as I can WITHIN
Visio.
This way the data is all enclosed in Visio.
The user interface could be replaced by fancy VB interface, but the data can
be read and saved to the ShapeSheet in various sections: custom properties,
scrach area, SolutionXML: Visio provides many ways, you just have to make it
work
You can of course connect to a database to derive additional data (corporate
data, project data), display it and update it (transactional or non
transactional) - but this data is not the drawing !!

Solution B
===========
Save the drawing (+all other data) in the database ONLY.
You don't use Visio's save function.
Instead, when "opening a drawing" you show a list of drawings/projects from
the database.
Once the user chooses the drawing, you read the data from the database and
build the drawing yourself.
It seems scary but it is not.
You just drop all shapes and connector to the drawing without caring about
positioning.
Then you use Visio's "Layout Manager" to arrange the Visio according to the
orientation of your liking.
Saving is simple: you can save the drawing to the database along the way
(using events) or only when the user clicks the "save" button (deleting old
project data and writing over).
Had you worked in dotnet, I would have suggested using an in-memory dataset.
Since the information here is only stored in the database, you don't have
any sync problem as well.

I hope this helps
Amitai

 

[M@]

Thanks Al, Bruce & Amitai for your replies. Your suggestions have assisted
me greatly.
Amitai: Thanks for the detailed post, I found your suggestions very helpful.
As most of my data is stored in the database I am exploring the idea of
recreating the drawing, based on the database & using Visio's routing to
layout the drawing.

I also like the idea of having all the data in the shapes, although there is
quite a bit of data this would simplify things greatly.

Thanks again,
M@