U
usenetposter2005
Visio 2003, or at least my lack of knowledge about Visio 2003 is
driving me insane! I have what I thought would be a fairly simple
project, that is turning into a nightmare. I would appreciate any help
anyone could give about linking a database to a Visio 2003 space plan.
Below I have outlined what I have, what I want, and what I've tried.
If someone could point out where I am going wrong I would be really
pleased.
***What I have***
1. A SQL database which contains computer equipment assets. There is
a main Asset table which contains a record for each asset along with
some purchasing details like date, vendor and a field for asset
location. There is also a table for each unique type of equipment.
For example there is a CPU table, a monitor table and a printer table.
These tables contain records that represent individual assets. Each
record has a field for the asset number which is to used to link the
table with the main asset table, and additional fields which contain
specific item information like screen size for monitors etc.
2. I have a Visio drawing which spans multiple pages which diagrams
our basic floorplan. I have added Visio 'spaces' to the drawing and
created spaces which match with the possible locations from the SQL
asset table.
***What I Would Like***
Ultimately I would like to be able to go through some simple process
which would populate the Visio drawing with computer shapes based upon
the SQL database. I would like the computer equipment shapes to be
positioned into their respective Visio spaces on the drawing. I would
like to be able to modify equipment details in either the SQL tables,
or the Visio drawing and have everything keep in sync. I would like to
be able to move an equipment icon in a Visio drawing from one space to
another and have the location changed in the SQL Asset table. I would
like to be able to add new equipment to the Visio drawing and have it
create a record in the SQL database.
***What I have tried***
1. Space Plans
I have enabled space plans, and used the Import Data option to import
computer equipment shapes into my drawing. The shapes are populated
into the appropriate spaces on the drawings and their custom properties
reflect the values in the SQL database. The problem is the computer
equipment in the drawing is not linked to the original record. If I
make a change to a custom property in the drawing, it does not update
the database. I could live with this if there was a way to Refresh the
equipment shapes in the Visio drawing that worked. Currently after my
initial import, if I modify one of the assets in the database, and
choose to Refresh from the Plan menu in Visio, it creates a duplicate
item! When doing the initial import I specify the Asset Number field
as a UniqueID so I don't understand why Visio is creating duplicate
shapes.
2. Linking a Master shape from a stencil to the database
The drawback to this is that I need to manually place the shapes on the
drawing. Also since I link the shape to a view which combines the SQL
asset table and the specific equipment table (for example I have a view
combining fields from the asset table and the CPU table which I would
link to a Tower PC shape) Visio does not let me update custom fields
for a shape back to the SQL database. I get an error ODBC Error 37000
which complains that the update could not be done because it would
affect multiple tables. I could create a new CPU table which also
contained the data from the asset table so that I was no longer using a
view, but this seems to defeat the purpose of having a relational
database.
3. Using the Database Wizard to create a new stencil containing one
shape for each item in the SQL database. This works similar to #2 in
that I cannot update the database when I change an item in the drawing
due to the same ODBC error.
Wow, that's a long message. So if anyone has any input as to how I can
go about this, I'd love to hear it.
Thanks,
Troy.
driving me insane! I have what I thought would be a fairly simple
project, that is turning into a nightmare. I would appreciate any help
anyone could give about linking a database to a Visio 2003 space plan.
Below I have outlined what I have, what I want, and what I've tried.
If someone could point out where I am going wrong I would be really
pleased.
***What I have***
1. A SQL database which contains computer equipment assets. There is
a main Asset table which contains a record for each asset along with
some purchasing details like date, vendor and a field for asset
location. There is also a table for each unique type of equipment.
For example there is a CPU table, a monitor table and a printer table.
These tables contain records that represent individual assets. Each
record has a field for the asset number which is to used to link the
table with the main asset table, and additional fields which contain
specific item information like screen size for monitors etc.
2. I have a Visio drawing which spans multiple pages which diagrams
our basic floorplan. I have added Visio 'spaces' to the drawing and
created spaces which match with the possible locations from the SQL
asset table.
***What I Would Like***
Ultimately I would like to be able to go through some simple process
which would populate the Visio drawing with computer shapes based upon
the SQL database. I would like the computer equipment shapes to be
positioned into their respective Visio spaces on the drawing. I would
like to be able to modify equipment details in either the SQL tables,
or the Visio drawing and have everything keep in sync. I would like to
be able to move an equipment icon in a Visio drawing from one space to
another and have the location changed in the SQL Asset table. I would
like to be able to add new equipment to the Visio drawing and have it
create a record in the SQL database.
***What I have tried***
1. Space Plans
I have enabled space plans, and used the Import Data option to import
computer equipment shapes into my drawing. The shapes are populated
into the appropriate spaces on the drawings and their custom properties
reflect the values in the SQL database. The problem is the computer
equipment in the drawing is not linked to the original record. If I
make a change to a custom property in the drawing, it does not update
the database. I could live with this if there was a way to Refresh the
equipment shapes in the Visio drawing that worked. Currently after my
initial import, if I modify one of the assets in the database, and
choose to Refresh from the Plan menu in Visio, it creates a duplicate
item! When doing the initial import I specify the Asset Number field
as a UniqueID so I don't understand why Visio is creating duplicate
shapes.
2. Linking a Master shape from a stencil to the database
The drawback to this is that I need to manually place the shapes on the
drawing. Also since I link the shape to a view which combines the SQL
asset table and the specific equipment table (for example I have a view
combining fields from the asset table and the CPU table which I would
link to a Tower PC shape) Visio does not let me update custom fields
for a shape back to the SQL database. I get an error ODBC Error 37000
which complains that the update could not be done because it would
affect multiple tables. I could create a new CPU table which also
contained the data from the asset table so that I was no longer using a
view, but this seems to defeat the purpose of having a relational
database.
3. Using the Database Wizard to create a new stencil containing one
shape for each item in the SQL database. This works similar to #2 in
that I cannot update the database when I change an item in the drawing
due to the same ODBC error.
Wow, that's a long message. So if anyone has any input as to how I can
go about this, I'd love to hear it.
Thanks,
Troy.