Get at database definitions

[Ann]

I have reverse engineered a database into visio professional 2003. I now want to be able to write programs which operate on the database definitions - eg load in new talbes and columns from an excel spreadsheet (and therefore create new tables and so on so i don't have to retype them), download selected data to a spreadsheet (eg table and column details), add new information through the interface (eg transformation rules for my data conversion project), add new information in a spreadsheet and then upload into visio (eg get a table list, add some new data, and load that up to visio), control shape appearance with attributes (maybe user defined attributes) such as all tables with the "in scope" flag set are shown in blue...in short, extend and inteface the visio diagram to document and control my whole project. The problem is - i can't find where the database definitions are kept! If there are 100 tables with 1000s of columns and indexes etc, where are they kept? How can you access it through code?

 

[onedaywhen]

One way is to use ADO. Secifically, use the Connection object's
OpenSchema method:

ADO 2.8 API Reference
OpenSchema Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdamth03_4.asp

--

I have reverse engineered a database into visio professional 2003.

I now want to be able to write programs which operate on the database
definitions - eg load in new talbes and columns from an excel
spreadsheet (and therefore create new tables and so on so i don't have
to retype them), download selected data to a spreadsheet (eg table and
column details), add new information through the interface (eg
transformation rules for my data conversion project), add new
information in a spreadsheet and then upload into visio (eg get a
table list, add some new data, and load that up to visio), control
shape appearance with attributes (maybe user defined attributes) such
as all tables with the "in scope" flag set are shown in blue...in
short, extend and inteface the visio diagram to document and control
my whole project. The problem is - i can't find where the database
definitions are kept! If there are 100 tables with 1000s of columns
and indexes etc, where are they kept? How can you access it through
code?

 

[Ann]

I'm not sure this what I need. I looked up connection object, which is about connections to a data source. As far as I can tell, the database definitions are not stored externally to visio - i.e. when I reverse engineer a database, and save it as a vsd file, all the definitions are stored somewhere in the vsd file (there are windows in the application which let you change add tables and columns and indexes and stuff). But the "db engineer" application (which I think is an add-on) must read this from the vsd file, process it while the diagram is open, then store it back to the vsd file. Or this could all be complete crap because I am very new to this! I can't see anything that looks like the database definitions when browsing the visio file (I saved it to xml format to have a look). Help!

 

[Al Edlund]

One of the neat things about the current versions of visio is that they
allow you to export the drawing as xml (as you discovered). I am sure that
if you do and examine the output you will find a proprietary binary object
embedded in the document section (i.e. it's in there, you just can't get to
it). At least that is how they implemented the directory services add-on.
Al

I'm not sure this what I need. I looked up connection object, which is

about connections to a data source. As far as I can tell, the database
definitions are not stored externally to visio - i.e. when I reverse
engineer a database, and save it as a vsd file, all the definitions are
stored somewhere in the vsd file (there are windows in the application which
let you change add tables and columns and indexes and stuff). But the "db
engineer" application (which I think is an add-on) must read this from the
vsd file, process it while the diagram is open, then store it back to the
vsd file. Or this could all be complete crap because I am very new to this!
I can't see anything that looks like the database definitions when browsing
the visio file (I saved it to xml format to have a look). Help!

 

[Ann]

So.....there's no way to decipher it? Where can I get documenation about the add-on?