Write recordset back to source OR access Shape data in memory

Started by Yacine, July 05, 2014, 07:06:24 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.


 Hi guys,
Let's say I have drawings that are more databases than actual drawings and that visio is used as main input tool.
I set in the drawing connections between shapes, I categorize shapes by placing them in containers, I configure shapes by means of data bound lists, etc.
In external databases, I would use the data for generating reports and checking for inconsistencies.
In this scenario two problems occur:
1) speed
to edit the shapes I need to iterate through all the shapes of the page or the document and do a lot of time consuming tests.
2) input direction
for some inputs it is easier to work with a table (eg access or excel), but a direct connection to the drawing, that would select and show the shape being edited is absolutely necessary.
So the idea would be to get access to the shapes in memory – preferably as recordset and alter them by means of SQL commands. It should be possible, but I don't know how.
Update: basically a memory mapped file

Microsoft has a nice page (http://msdn.microsoft.com/en-us/library/office/ff765564%28v=office.15%29.aspx)  that explains visio's recordset capabilities, but it says also
QuoteIf data in the source changes, you can refresh the data in the drawing to reflect those changes. You cannot, however, make changes in the data in the drawing and then push those changes back to the data source.
This kills of course the requirement to have visio as primary input tool.
One idea could be to export the data to an intermediate other file, delete the source and replace it by the intermediate file. --> I'd prefer the direct access to the shapes in memory.

What's your opinion?
Thanks in advance


Short answer a 'round-tripping' solution that uses Visio as the data entry mechanism is do-able, but is not for the faint of heart. It requires an intimate knowledge of both sides of the solution, Visio and the selected data platform.

Your desire to roundtrip information is well known and desired by many. There are a couple of stumbling blocks along the way.
"preferably as recordset and alter them by means of SQL commands", datarecordsets are based upon ADO not SQL and the Visio API does not implement field addressability in updating an individual row. This leaves us the option of updating the recordset using XML. By field addressability I mean update/write, not reading. In a roundtrip solution datarecordsets supply us with the mechanics of datalinking directly with the associated shapes and save the developer a lot of time with potential editing.
Excel is one of the most common requests and IMHO has many unseen issues as well. Excel because of it's ability to have embedded formulas and formatting in the cells give tremendous flexibility to the tool. Unfortunately these are lost during data import (as well as custom worksheet formatting). Since excel does not support sharing an open workbook, it forces the solution designer into a requirement for possibly multiple (disjoint) copies of the same data. This platform leaves us open to a lot of user dissatisfaction when we cannot put back what has been imported, but, if you're willing to live with the weeping and gnashing of teeth it can be targeted as the external data platform (more on this in a later post).



Thanks for the answer Al.
When you say "datarecordsets are based upon ADO not SQL", there seems to be a confusion. SQL is the language used to query and alter recordsets - whether they are based on ADO, DAO, Jet Engine or else. I think it is very well suitable for manipulating ADO recordsets. (haven't checked if the recordset is read-only though. But still then you should be able to make a copy of the recordset and work on the copy.)

Your remarks regarding Excel are right. Since Excel would block any file that it opens against other applications.
Access however should be more flexible and allow to protect only single rows. By setting up a fairly simple error catching mechanism, one should be able to open the DB and update it from Visio, whilst it is open in Access.

Catching the changes would limit the transactions to be done.
One big issue in this regard would be to collect the changes until another shape is selected, so you would avoid doing an update for every single cell.
Another possibility could also be to hide the custom properties and write instead a propper dialog to edit them. So the update would only be started when the dialog is closed. (This would probably slow down the workflow ???)


typically we think of sql queries within the context of datasets, datatables, and rows. Working with ado we use datarecordsets, datarecordset, and row. As SQL has advanced with new datatypes that now include geography and hierarchy these have not been added to the datarecordset architecture. It appears to be doomed to a life under the covers of the office products.
The visio sdk has some basic manipulation documented that can be applied within visio but it is not complete. The sdk does however provide some routines for translating datatypes between the two platforms. I've been working on a sample project that attempts to work with some of the round-trip challenges and most of the under-the-covers tools are actually doing the data manipulation in dataset/datatable and then write back to the data recordset using the xml that visio supports.

"One big issue in this regard would be to collect the changes until another shape is selected", the visio sdk does an example of this by demonstrating the capture of and and delete events to a set of queues and the showing how to operate on them when the application has nothing pending. In v2013 the have added events that trigger after major changes with a list of shapes which have been altered. This was necessary because actions like 'replace shapes' go against what is currently selected which may be several shapes.

"Another possibility could also be to hide the custom properties and write instead a proper dialog to edit them." I purposely try to stay away from this unless I am going to own the data architecture that is being provided, although I try to have enough mechanics included so that it can be easily accomplished. The MVVM architectures try to provide this.

As an observation it is interesting that MS did not provide a LINQ that works with datarecordsets directly (although the fact that the only output of a recordset is to xml and you can LINQ with that).

hope this helps from the sidelines,



Thanks Al,
From what you're writing it is clear that you went through the whole process and you *really* know about it.
Well ... it's quite daunting to read that it is not as easy as expected.
May be I shall stay with the way MS has forseen to us poor Visio users. So to have an only DB to Visio path.
This said, how should I handle shapes being added or deleted in the drawing?
It seems quite "unergonomic" to have to add an item in both systems manually. Is there a way to add a row in the DB when a shape with the right data fields is added to the drawing?
Is there a way to see in the DB which rows are not linked to shapes? (I know you can see it in the data linking window, but is there a back track that tells the DB, that all the rows are linked?)

Furthermore is there a possibility to push formulas in the fields instead of only strings?


One of the original MVPs (graham wideman) once told me that we have to make a decision early on where the data we use is going to reside; either in the drawing or in an external source, one of them has to be the master. After that we need a plan on how to keep them in sync. I started a project a couple of years ago to use Visio as a data entry tool where the data would be kept in SQL server. This made sense at the time because the primary objective was the data had to be shareable across a large team and browser access to the data had to be available for those that did not have Visio installed. The technical services team that did rack and stack hated the text based system and was in love with the Visio client application. Here is a pointer to some videos that talked to the project. The dcrack_compnet presentation talks to data entry and shows how the database was updated.

"Is there a way to see in the DB which rows are not linked to shapes?"  Datalinking from Visio is a read only process controlled by Visio with the datasource being passive. Databases have no internal knowledge of where or who is using what data. Somewhere here on Chris' site I put an  Access example that showed two scenarios (drawing from an access database into Visio and creating a drawing where Visio controls the drawing). If you want the database to be aware of what is in the drawing you will have to trigger from the database and have it query what is there. The thing to remember is that visio shapes are required to have unique names and this is often an easy way to provide a linkable key between the datasource and the drawing. All that is required then is to provide a drawing search algorithm that gets a "tuple" (a two part object) that describes the unique page/shape names in the document (even visio has to do this under the covers) and then create a query that provides a difference between the datasource and the drawing to get the answer. For the dc_rack project all of the component names are created by concatenating a tablename and a rowindex to create the name (only one shape per row). Since real databases allow for the creation of unique indexes why not take advantage of the ability. Since all of the dc_rack drawings are created from the database (they are considered throwaways) they, at least when created, match the database. The challenge was in going the other way, when updating a drawing also update the database.

"Furthermore is there a possibility to push formulas in the fields instead of only strings?" Sure a formula is merely a string that Visio handles different from a data object. The SDK has routines for writing formulas to cells. The assumption is that you meant a Visio formula.




Hi Al,
Quote"Furthermore is there a possibility to push formulas in the fields instead of only strings?" Sure a formula is merely a string that Visio handles different from a data object. The SDK has routines for writing formulas to cells. The assumption is that you meant a Visio formula.

I would write a macro to remove the quotation marks in the updated cells to transform them to real visio formulas. (eg replace all ' "= ' by ' = ' Would you have an idea on how to do it easier?


recordsets can be 'exported' as xml, which can then be imported to just about anything. In a current project I dump the xml to an excel worksheet directly for export from the visio document.


I think that back-link could be possible, at least in simple cases.. (e.g. when the data is not "really read-only", like a result of an excel formula calculation).

I.e. it looks like there is enough information one could need to do that in in a DataRecordset/Connection- the connection string, query (table), key fields.
This could theoretically allow to write sort of a generic back-writer.

For Excel for example, now the connection string is simply locked with "ReadOnly=True" and HDR.
If you simply remove that option, you could theoretically well write the database back with Updates and Inserts.
(Excel does not seem to support updatable recordsets, so you can't do that with ADO directly)
But for the SQL Server for example, you could do with good old ADO.

The important point is how to maintain consistency... I.e. in principle user could change data both in Visio and in the data source if allow read-write was allowed.
To solve that, a copy of data can be stored in shapes on back-sync for example, to allow conflict resolution (to know where the data has actually changed)


Thanks guys,
I found out that visio's on board features are sufficient to close the loop.
The visio drawing is linked to an access DB.
In visio I will catch changes to the data and the addition of new shapes.
Then I use the export to database addon to write all the data of the drawing in that same database in a table I called export.
A query will then update the actual data in the DB.
I have still not found out how to run the addon automatically, but I am confident to be able to do so.

This seems like a long way, but when triggered by events, this should run smoothly.

Regarding my previous question, the intent was to inject some smartness via data connectivity in the shapes. ie: instead of writing a formula between quotation marks, the actual formula shall be written.
I will probably write a generic smart shape that transforms injected "smartness" in formula via SETF/GetRef (http://visguy.com/vgforum/index.php?topic=3870.0)