This is a forum thread to describe a project wherein which I hope we can achieve something that I know a lot of modelers have asked for.
A plugin or application that allows us to connect directly with a database and allows objects to be created from the database as well as synchronize objects created in Visio back to the database.
Phase 1
In phase 1 - We should be able to:
1. Create an object from an existing row item from the database
2. Create a new object on a page will create a new line item in the database
3. Updating any object metadata from Visio will update the database
4. Updating any object from the database will update the object in the diagram
5. Creating a connection between two objects will create register which objects have a connection
We can see how we go with attaching new versions of the files and zipping them and attaching them here.
We'll keep this thread just for the releases. To collaborate on another version and talk about issues lets create new topics and code them: VisioDB rel0.1 - Issue
first release produced by Yasine.
Since the generic file title is non-descriptive, posting descriptive text with each release would be beneficial...basic features sort of thing. If this is for general consumption, even demo videos.
Sounds good.
attached zip file.
the three boxes in the vsd file are linked to DB.accdb
I inserted the first name and last name into the text of the box.
by selecting another row in the DATA table it will alter the name in the box.
attached screen shots.
Not sure what your target is, but I am assuming a lot here.
I just used the database wizard to link shapes on a page to database objects.
Wouldn't a repository system (eg Github) be the better platform for managing a project with multiple contributors?
Quote from: maclarkson on September 19, 2019, 12:14:41 PM
1. Create an object from an existing row item from the database
2. Create a new object on a page will create a new line item in the database
3. Updating any object metadata from Visio will update the database
4. Updating any object from the database will update the object in the diagram
5. Creating a connection between two objects will create register which objects have a connection
This is interesting, but I have some remarks.
- It is important to decide who is the data master, the drawing or the the DB. They can't both be the master. In my projects its generally the drawing. Even data not related directly to drawing issues should be saved in the vsd* . This way the DB becomes a simple control tool, not the recipient of the data. This is in so far important as you can replace the DB (the control tool) and continue working on existing documents.
- Creating shapes from rows in the DB is interesting but requires to have certain questions solved before hand:
- Where and how to place the shapes
- Have a stencils management system and a configuration tool for masters. This includes also the management of the custom properties
Just stumbled over Nicolay's synchronisation tool on Youtube.
Nice stuff.
It was discussed in the other topic... :)
That tool can just synchronize the data with (already connected) data source. If everything goes well.
It is not capable of drawing or laying out diagrams. By no means it is an alternative to Visio Data Visualizer, ITP commerce, Orbus, or P4B :D
can you post me the link to Nikolay's youtube >>> re:synchronising tool please.
From my perspective on Data Modelling >> The data at the back end drives the vector images at the front end.
However, The data is appended/ modified/ added and deleted via code in the vsd front end. This way the data back end is just a simple series of tables.
The VSD output is therefore >> an AS built model of the subject's data.
If you can capture snapshots of the subject's data from the real world, >> the new data set is considered the Is built, and it can be compared to the As built data set.
How and where you compare the data sets >> As built vs Is Built is up to your imagination.
:-\ hope this makes sense.
Quote from: cliff50 on September 21, 2019, 09:03:06 PM
can you post me the link to Nikolay's youtube >>> re:synchronising tool please.
https://www.youtube.com/watch?v=MZi40T-zY9M (https://www.youtube.com/watch?v=MZi40T-zY9M)
With respect to the scope of your Test project description
I am attaching a screenshot of a solution that possibly fits within your scope.
The image represents splicing arrangements between 3 X fibre optic cables.
The coloration is the blue orange green brown slate white etc that represent the real world application of colors in fibre optic cables tubes and strands.
All the "boxes" and "lines" are automatically drawn onto a blank Visio page from information in a back end Access database.
essentially the macros do the following:
1. initiate drawing on blank page.
2. draw boxes in sequence top to bottom / left to right of selected of cables.
3. draw lines that represent the splicing associations between "boxes" (strands) recorded in the database.
the nomenclature CTF (Cable Tube Fibre) is also extracted from the same access database.
Export to excel report is in screenshot.
The solution is capable, via the VSD front end, to alter the data entries pertaining to >" which strand is spliced to which other strand" and also to alter the Cable name (identity).
Cliff
Ok gents,
I have attached my first effort I used the database link to access the database and then used NiKolay's back sync to sync data back. issues are that the experience is a pretty ugle one.
1) you can create new objects and have them be created in your database so you have to create your objects in the database first
2) the shape data governed from the database does not have drop-down values that are the same as those in the database. The only way id be able to get the list values would be to create a data set and then i'd have to populate by hand all of the drop-down values.
Really nice initiative from you. I like it very much and hope that the tool will be further developed, that as many people as possible participate and have a lot of fun personally. So much for praise.
However, as an experienced user of Visio with Access combinations, I have other priorities than you. Synchronizing the DB with Visio data is just one of many details that a DB solution should implement. Polarized: "A gimmick".
In this article I would like to set up a project in which I avoid the mistakes I made in the past.
I have summarized some ideas about an ideal DB solution in a drawing that I attach to this article. Many of the ideas are not formulated as simple sentences and have no explanations.
To keep you interested, I however publish them as "nightly built" - promising to work on them furthermore.
Hay Yasin, I am trying to achieve the same as you. I am an enterprise architect and part of what I am trying to do must include infrastructure. This stage I would like to accomplish synchronisation with connected objects using connector. If we can achieve this we only have to extend the same functionality to embedded objects. When I am doing a function decomposition, or an infrastructure decomposition I will need the ability to synchronise the decomposition as connected objects. Or have to index and query all of the objects to mine for the data.
If we can achieve these things it should be just a matter of creating new objects and using the same function to collect the data.
QuoteIt is important to decide who is the data master, the drawing or the the DB. They can't both be the master. In my projects its generally the drawing. Even data not related directly to drawing issues should be saved in the vsd* . This way the DB becomes a simple control tool, not the recipient of the data. This is in so far important as you can replace the DB (the control tool) and continue working on existing documents.
Creating shapes from rows in the DB is interesting but requires to have certain questions solved beforehand:
Where and how to place the shapes
Have a stencils management system and a configuration tool for masters. This includes also the management of the custom properties
I need to be able to do it both ways. Imagine you are in a meeting and someone begins to discuss a number of changes or new configurations. You should be able to go onto your mobile and add the connections then later come back and diagram the changes.
There's nothing wrong with implementing an import function for updating the data, but still a master needs to be defined.
There may be cases where the data are split between the drawing and the DB - each category being stored in the other document.
...and there's the issue when two or more people are working on same drawing.
This strikes me as a major effort. A lot to ask and expect of volunteers. Especially the coding part.
Quote from: wapperdude on September 24, 2019, 12:54:54 PM
...and there's the issue when two or more people are working on same drawing.
This strikes me as a major effort. A lot to ask and expect of volunteers. Especially the coding part.
That's why I'm recommending an "import" function instead of an automatic update. This way it a conscious decision to overwrite the data in the master document. Much easier than a version management system.
I sense "rate of change" of subject data may also be important.
From my experience, I am familiar with this type of modelling deployment. I used "model locking" as opposed to "record locking" to minimize data conflicts.
The model consists of (4 x files)> ( VSD / DB files plus KeyLock text file plus DateTime textfile.) are located on a central server. When a change to the model is required:
The operator uses a coded script to download from the central server the VSD and the DB files.
1.The scripting routines firsts checks if the KeyLock file is "LOCKED" or "UNLOCKED"... if it is "LOCKED" it means the model is being edited by another user and will not allow any upload from the local user toward the central server. .... if unlocked, it will download the VSD/DB files regardless, providing that the datetime version of the local model is older than the server model.
Now. If the operator wishes to EDIT the model ... scripting routine will download the latest (DateTime dependent), (Unlocked) version of model from central server and change the Keylock text file on central server file to LOCKED position.
After Changes are made , the operator uses a scripting routine to upload the VSD/DB files to the central server and set the KeyLock text file to "UNLOCKED". and set the DateTime file to current date time.
Assumptions:
speed of upload/download is dependent on file sizes and link speed <central server> to <local machine>.
Rate of data change is presumed low however, it is directly proportional to number of Editors.
The above method describes how model Editors ""contend"" for the right to edit the ""leading edge version"" of the model.
Quote from: maclarkson on September 23, 2019, 12:04:54 PM
I have attached my first effort I used the database link to access the database and then used NiKolay's back sync to sync data back. issues are that the experience is a pretty ugle one.
1) you can create new objects and have them be created in your database so you have to create your objects in the database first
2) the shape data governed from the database does not have drop-down values that are the same as those in the database. The only way id be able to get the list values would be to create a data set and then i'd have to populate by hand all of the drop-down values.
Of course, the experience would be pretty ugly :D
When you connect to a data source, Visio has no idea that some fields are backed by dictionaries in the database (like your"Object Type"). So it generates default (string) fields. Therefore you don't get any dropdowns. In principle, generating such dropdowns is possible, but you don't get that "for free" with Visio. Since Visio is thought to be data *visualization* tool (i.e. you visualize your database content in Visio, not enter the data)
People who need to enter data, usually build their own (VBA?) forms, or design shapes to have those dropdowns..
You could also use SharePoint as a backing repository. So that it is all online in one place with basics (document storage, co-authoring, reviews, version history) set up. No need for home-grown document locking. You could enter data in a form with dropdowns (if you link shape to a SharePoint list item). You can even edit layout online now. Isn't it an option? From access database you can go there directly, there is a button "export database to SharePoint"
Nikolay:
I should be able to populate the files without code. I simply create a field in the database to concatenate the drop downs and add them to every object. Then when the object syncs I can use the actions in the shape sheet to update the format fields in the shape sheet.
In the meantime I cannot use lists in SharePoint as there is no way to have a many to many relationship between tables. Plus the app capability of SharePoint is crap.
I would like to build the forms using the software development kit but my knowledge of vba is slim. I was hoping with the right guidance from you guys you could help me make something. I am sure every person who uses Visio wishes they could do the things that this tool could achieve.
Yasine
I Agree that import export should be done manually. At this stage I will manage data integrity via reporting. All I need at this point is an MVP.
The source has to be the database because I will most definitely need to use objects multiple times.
If you want,
attached vsd file is a rough shell of test solution.
I use visio pro 2016 (64 bit) . I use DB.accdb (64 bit) so I hope it works on your machine.
Position Test VSD and DB.accdb files in same folder// enable macros when you initiate Test.vsd// ensure the necessary references are checked in vsd coding environment.// open ADDIN tag and you should see extra customized buttons// explore Test button it may give you ideas how to open form in Visio to code query s on database and bring results back into Visio. This shows only a select query... append delete update etc are coded slightly different.
HTH
Cliff
hay cliff,
Could you tell me step by step what I need to do to test your thingy out?
Actually I got it to work, yay, that will definitely be useful
I was looking at the code and I could not identify where there commandButton1 executes that request to the database?
if you are looking at the code ..
Private Sub CommandButton1_Click()
Test ''' <<<<<<<< this is a call to the function called test
End Sub
Public Function Test() <<< here is the function test
Dim myarray As Variant
Dim db As Database
Dim rstsource As Recordset
Dim num, R As Integer
'' target the database
Set db = DBEngine.OpenDatabase(ThisDocument.path & "DB.accdb") 'Identify the Database
Set rstsource = db.OpenRecordset("SELECT Data.Forename, Data.Lastname, Data.ShapeID " & _ ''pull the selected data into a record set
"FROM Data;")
num = EnumerateSet(rstsource) 'determines how many entries in the recordset
ReDim myarray(num, 3) '' 6) ''set the dimensions of the array
R = 1
'' iterate through the recordset transfering the contents into array
If rstsource.RecordCount > 0 Then
With rstsource
' load array with Recordset.
.MoveFirst
Do While Not .EOF
myarray(R, 0) = !Forename
myarray(R, 1) = !lastname
myarray(R, 2) = !ShapeID
R = R + 1
.MoveNext
Loop
End With
End If
frmTest.ListBox1.ColumnWidths = "42.5 pt;42.5 pt;42.5 pt;"
frmTest.ListBox1.List = myarray ''<<<<<<<< load the listbox with the contents of the array
End Function
some of the code is in the form >> frmTest
right click -> view code
Quote from: maclarkson on September 25, 2019, 02:23:03 PM
In the meantime I cannot use lists in SharePoint as there is no way to have a many to many relationship between tables.
But how is it different from Access database? Couldn't you create the same "joining" many-to-many "connector" table (well, "list" in terms of SharePoint)
Quote
Plus the app capability of SharePoint is crap.
Does not compute :) Could you clarify?
I was under impression that app capability of SharePoint is amazing, especially with "modern" pages and apps?
Nikolay,
is it possible to retrieve a ""recordset"" from SharePoint ?
I am assuming access database can be easily converted to SharePoint repository.
Therefore I also assume code to sequel query access database must have an equivalent to sequel query SharePoint repository.
using the code I posted above, how would it be modified to cater for a SharePoint data repository ?
Can SharePoint data be queried via VBA code from Visio? or, is data linking to SharePoint only accomplished via binding ?
I know it wouldn't look like the following code, but I write the example to explain my curiosity.
----------------------------------------------------------------------------------------------------------------------------------------------
Public Function Test()
Dim myarray As Variant
Dim db As SharePoint <<<<<<< declare as SharePoint
Dim rstsource As Recordset
Dim num, R As Integer
'' target the database
Set db = SharePointEngine.sharepoint( "DB.accdb") '<<<<<<<<<<<Identify the Data location
Set rstsource = SharePoint("SELECT Data.Forename, Data.Lastname, Data.ShapeID " & _ ''pull the selected data into a record set
"FROM Data;")
num = EnumerateSet(rstsource) 'determines how many entries in the recordset
ReDim myarray(num, 3) '' 6) ''set the dimensions of the array
R = 1
'' iterate through the recordset transfering the contents into array
If rstsource.RecordCount > 0 Then
With rstsource
' load array with Recordset.
.MoveFirst
Do While Not .EOF
myarray(R, 0) = !Forename
myarray(R, 1) = !lastname
myarray(R, 2) = !ShapeID
R = R + 1
.MoveNext
Loop
End With
End If
frmTest.ListBox1.ColumnWidths = "42.5 pt;42.5 pt;42.5 pt;"
frmTest.ListBox1.List = myarray ''<<<<<<<< load the listbox with the contents of the array
End Function
Working on Maclarkson's project, I wanted to set up an Access form that connects to a Visio document and catches the required events - namely:
- shapes and connections added, deleted, modified
- page changed, etc.
The solution I developped in the past, was a one way control. Access was aware of Visio but not Visio of Access.
So Access changes could be propagated automatically to Visio, but Visio changes needed to be read by pushing a button.
So the idea is now to improve this mechanism an let Visio trigger data import to Access.
I set up a form with the following code and got stuck.
Option Compare Database
Public WithEvents vApp As Visio.Application
Public WithEvents vDoc As Visio.Document
Public WithEvents vPg As Visio.Page
Private Sub Befehl0_Click()
On Error Resume Next
temp = CurrentProject.Path + "\" & "Zeichnung1.vsdx"
Set vApp = GetObject(, "Visio.Application")
If Err.Number = 429 Then
Set vApp = CreateObject("Visio.Application")
End If
Set vDoc = vApp.Documents.Open(temp)
Set vPg = vApp.ActivePage
On Error GoTo 0
End Sub
Private Sub vApp_AppActivated(ByVal app As Visio.IVApplication)
Debug.Print "vApp_AppActivated"
End Sub
Private Sub vApp_BeforeShapeDelete(ByVal Shape As Visio.IVShape)
Debug.Print "vApp_BeforeShapeDelete"
End Sub
Private Sub vApp_BeforeWindowPageTurn(ByVal Window As Visio.IVWindow)
Set vPg = vApp.ActivePage
Debug.Print vPg.NameU
End Sub
Private Sub vApp_PageChanged(ByVal Page As Visio.IVPage)
Set vPg = vApp.ActivePage
Debug.Print vPg.Name; Page.Name
End Sub
Private Sub vDoc_DocumentChanged(ByVal doc As Visio.IVDocument)
Debug.Print "vDoc_DocumentChanged"
End Sub
Private Sub vDoc_DocumentOpened(ByVal doc As Visio.IVDocument)
Set vPg = vApp.ActivePage
Debug.Print vPg.Name
End Sub
Private Sub vDoc_DocumentSaved(ByVal doc As Visio.IVDocument)
Debug.Print "vDoc_DocumentSaved"
End Sub
Private Sub vDoc_MasterAdded(ByVal Master As Visio.IVMaster)
Debug.Print "vDoc_MasterAdded"
End Sub
Private Sub vDoc_PageAdded(ByVal Page As Visio.IVPage)
Debug.Print "vDoc_PageAdded"
End Sub
Private Sub vDoc_PageChanged(ByVal Page As Visio.IVPage)
Set vPg = vApp.ActivePage
Debug.Print vPg.Name
End Sub
Private Sub vDoc_ShapeAdded(ByVal Shape As Visio.IVShape)
Debug.Print "Shape added"
End Sub
Private Sub vPg_ConnectionsAdded(ByVal Connects As Visio.IVConnects)
Debug.Print "vPg_ConnectionsAdded"
End Sub
Private Sub vPg_PageChanged(ByVal Page As Visio.IVPage)
Debug.Print "vPg_PageChanged"
End Sub
Most of the app events worked. From the doc events, only some worked: documentSaved, documentOpened. No event from the page fired.
Does any one know why not all events fire and how to set this up propperly?
Yacine,
if I understand correctly, you need functionality by way of code in Visio, to alter data entries in Access.
following code are hand modified from working functions, so I may have accidentally bumped a comma or quotation, apologies in advance.
__________________________________________________________________________________________________________________________
Private Sub vDoc_ShapeAdded(ByVal Shape As Visio.IVShape)
Debug.Print "Shape added"
Insert_to_Access (Shape)
End Sub
___________________________________________________________________________________________________________________________
Public Function Insert_to_Access(ByVal MYshape As string)
On Error GoTo Err_Insert_to_Access
Dim strqry As String
Dim db As Database
Set db = DBEngine.OpenDatabase(ThisDocument.path & "DatabaseName.accdb")
strqry = "INSERT INTO tblAccessData(MyShapeName) " & _
" VALUES (" & MYshape & "');"
db.Execute (strqry)
Exit_Insert_to_Access:
Exit Function
Err_Insert_to_Access:
MsgBox Err.Description
Resume Exit_Insert_to_Access
End Function
________________________________________________________________________________________________________________________
OR for an update
__________________________________________________________________________________________________________________________
Public Sub UpdateAccessTbl(ByVal drwgID As Integer, ByVal drwgSheet As Integer)
On Error GoTo Err_UpdateAccessTbl
'
Dim record As String
Dim db As Database
Set db = DBEngine.OpenDatabase(ThisDocument.path & "DatabaseName.accdb") 'Identify the Database
record = "UPDATE tblMYTable SET tblMyTable.sheet = " & drwgSheet & "' " & _
" WHERE (((tblMyTable.DrwgID)= " & drwgID & " ));"
db.Execute (record)
Exit_UpdateAccessTbl:
Exit Sub
Err_UpdateAccessTbl:
MsgBox Err.Description
Resume Exit_UpdateAccessTbl
End Sub
Yacine
Most of the app events worked. From the doc events, only some worked: documentSaved, documentOpened. No event from the page fired.
Does any one know why not all events fire and how to set this up properly?
Have you tried coding at the " before triggers " in the Visio coding environment? ref attached image
Nicolay
In SharePoint, you're not working with a relational database, just lists. Trying to get it to work as a relational database is impossible. I recommend you attempt to try to create the following relationship in SharePoint Objectlist>Linktable<Relationship. See if you can find anyone who can tell you how to do it!!!
From the perspective that it's not a relational database trying to create database apps in is very challenging. I had a Sharepoint guy at work who knows SharePoint really well who was saying that he would not recommend using SharePoint.
Hi Cliff,
I found your code. Right Click on the object > Code
@ maclarkson
From my perspective "horses for courses" so why not let each app do what its good at.
I like Visio because it's commonly available, affordable and good at vector graphics.
Access -> is good at relational tabulation of data, Excel -> ease of spreadsheet and graphing.
SharePoint -> unfortunately, not much experience, and I feel the lesser for it :(
I think when you get them "talking" to each other, it is possible to create some very powerful solutions.
regards
Cliff
Quote from: maclarkson on September 28, 2019, 09:10:07 PM
Nicolay
In SharePoint, you're not working with a relational database, just lists. Trying to get it to work as a relational database is impossible. I recommend you attempt to try to create the following relationship in SharePoint Objectlist>Linktable<Relationship. See if you can find anyone who can tell you how to do it!!!
I fail to see the problem.. :) Relational database IS just a set of lists (or tables, it does not really matter what you call them..)
You could open your Access database, click "Export to Sharepoint" ribbon button and you will get that exactly structure (in SharePoint)
Am I missing something here?
Quote from: cliff50 on September 26, 2019, 07:48:35 AM
Nikolay,
is it possible to retrieve a ""recordset"" from SharePoint ?
You can use the same code you use for Access, just the connection string will be different. I believe we discussed it just recently here?
http://visguy.com/vgforum/index.php?topic=8886.msg39034#msg39034
Nicolay,
The other problem with SharePoint is that you need a recurring licence for it. Access is also accessible to everyone.
Hay Yasin,
Would you prefer to convert the database over to standard one rather than a Web DB?
I was going to have a go at transferring over the code from your last DB.accdb database and template to this new one and then try to fix up some of the code to get it to work. I can potentially work of anything you have currently updated. I was also going to have a go at changing the form you created in your orginal to a search from. I figured by adding a search feild in there I could convert the current where statement into a WHERE...LIKE... and then use the value in the search box. That way it would only retrieve the items that cam up in the search. The second button I figured we could program the button to commit the any selected items in the search to the page, (Using the Selected Stencil object)?? what do you recon?
Nicolay,
The other problem with SharePoint is that you need a recurring license for it. so it means fewer people will have access to it.
Hay Yasin,
Would you prefer to convert the database over to a standard one rather than a Web DB?
I was going to have a go at transferring over the code from your last DB.accdb database and template to this new one and then try to fix up some of the code to get it to work. I can potentially work latest release. I was also going to have a go at changing the form you created in your orginal to a search from. I figured by adding a search field in there I could convert the current where statement into a WHERE...LIKE... and then use the value in the search box to derive the list. That way it would only retrieve the items that came up in the search. The second button I figured we could program the button to commit the any selected items in the search to the page, (Using the Selected Stencil object)?? what do you reckon?
Quote from: maclarkson on September 29, 2019, 10:50:34 AM
Nicolay,
The other problem with SharePoint is that you need a recurring license for it. so it means fewer people will have access to it.
Hay Yasin,
Would you prefer to convert the database over to a standard one rather than a Web DB?
I was going to have a go at transferring over the code from your last DB.accdb database and template to this new one and then try to fix up some of the code to get it to work. I can potentially work latest release. I was also going to have a go at changing the form you created in your orginal to a search from. I figured by adding a search field in there I could convert the current where statement into a WHERE...LIKE... and then use the value in the search box to derive the list. That way it would only retrieve the items that came up in the search. The second button I figured we could program the button to commit the any selected items in the search to the page, (Using the Selected Stencil object)?? what do you reckon?
I'm actually not familiar with web DBs. To my knowledge you need a Sharepoint address to create one, which I don't have.
To this point the 2nd button was just there to test the different routines.
... still struggling with the events issue. :(
Quote from: maclarkson on September 29, 2019, 10:50:34 AM
Nicolay, The other problem with SharePoint is that you need a recurring license for it. so it means fewer people will have access to it.
Dear Mark, please don't get me wrong, I am not a SharePoint fan or anything; SharePoint has it's own nasty problems. Also, it looks like currently, I'm getting further and further from Visio as well... I was just under impression that you were talking about enterprise architecture, the area targeted by tools like Orbus, ITP, P4B. It's just my experience, but a company that needs enterprise management, usually already either has SharePoint on-premise, or it is not a problem for such a company to withstand $5 per month subscription.
Quote from: Nikolay on September 29, 2019, 07:27:47 PM
Quote from: maclarkson on September 29, 2019, 10:50:34 AM
Nicolay, The other problem with SharePoint is that you need a recurring license for it. so it means fewer people will have access to it.
Dear Mark, please don't get me wrong, I am not a SharePoint fan or anything; SharePoint has it's own nasty problems. Also, it looks like currently, I'm getting further and further from Visio as well... I was just under impression that you were talking about enterprise architecture, the area targeted by tools like Orbus, ITP, P4B. It's just my experience, but a company that needs enterprise management, usually already either has SharePoint on-premise, or it is not a problem for such a company to withstand $5 per month subscription.
Completely agree, and its all good if your an enterprise. I on the other hand am not :( and I doubt too many people in this forum will have their own licence of SharePoint.
Yasin,
Web DB does not require SharePoint. The latest database I uploaded you will see that you can play around with it. I am however going to convert it.
I will convert it over, over the next couple of days and I will be using Cliffs database as a starting point. He has a lot of good code on his that I would like to try out. Are you OK with this?
Quote from: maclarkson on September 29, 2019, 11:57:35 PM
Are you OK with this?
Absolutely, we'll need anyway to find a way to colaborate. For my part, I'm still sticking with my own version - it is simpler for me right now.
Sweet,
Actually it looks like I got confused I am using your database, Yasin. do you know why when I try to open your database I get vislib.dll missing?
I wrote the solution with late binding, because I didn't want to update the references at each office version change.Now working with "withevents" I'm anyway forced to use early binding. I'll upload a new release asap.
I have created a Onedrive folder that you should be able to get access to where we can store all our versions.
here's the link, I'll also put this at the top of the thread
https://1drv.ms/u/s!Aqde3O9MB4omxxtPjYEyg27cgtFU?e=xEI2QD
I have asked paul herbert if he can open up a new forum named "VisioDB project" it could be a pain trying to collaborate in a single thread. If it gets too tough to do things in here we could potentially try some alternatives. I thought that Zoho Connect looked like a good one.
In the meantime can you tell me what the settings is in the settings table?
vApp
vDoc
vPath
Hay Yasine,
I got in touch with Chris, who is happy to create a new forum for us, but I said I would investigate using git hub. I found this thread of git hub that talks about exporting everything as text files using this utility https://github.com/timabell/msaccess-vcs-integration so you can put it into Git. I am in a mixed mind as to whether we should use git as it seems like a lot of work. exporting and importing our work through text files. Should we use it or go for a forum and we can use one drive for file storage?
How are you getting on with finding help on your code?
Mike
Hello Mike,
I think that Git would be a better idea, because we'd get an evolving project. With a simple shared directory, we'll end up with a bunch of versions that will get difficult to evaluate.
But let's work with it till we get stuck, we can move later on to Git.
I managed to solve the synchronization issue and am right now uploading my latest version.
Cheers,
Y.
Bang it into a new folder on that OneDrive and I am will be happy to put an update here and start a new version thread.
hay guess what I manage to get the visio template to source its drop down list from Access
Have you checked my upload?
I implemented already the dropdown feature. It's a prop field in the doc, being referenced by the shapes as format.
on the Visio side >> Zeichnung1.VSDM has a custom toolbar. Extras can be tickled onto the toolbar such as Visio based forms and widgets to manipulate the Access data tables.
Yacines main form button > Open Visio < should open the zeichnung1 vsdm (which in turn , should populate its custom tool bar).
my machine is set up for 64 bit applications. some reference library s may need checking on the Visio side.
uploaded just now.
WOW!! looks amazing!! but how does it work?
Could we do a recorded skype session or something?
What time is good for you?
Cliff and I are in australia.
I keep getting the vislib error and a 40036 error?
Can anybody tell me why this might be?
I'm in Germany (UTC +2). Me at 8am would be around 16pm your time. What about Saturday morning. We should however try the connection first (I don't trust my machine).
The vislib.dll error is a reference problem. Since I had to move from late to early binding. The code saves a reference to the visio library used. The error occurs when used with anoter Visio version.
To solve it: in die VBA IDE / Menu Extras / Refererences: uncheck the invalid link to the Visio library on top of the list and check the valid one further down the list. "Microsoft Visio xx Type Library".
The subform not finding its parent is a bug that just disappeared in my new update. I have no explanation for it.
Rgds, Y.
Cliff and I found the problem of vislib I have a missing ActiveX reference. How about 2ocklock on Sunday your time I think that's 10 o'clock our time.
Sunday 2pm is fine. I sent you a separate Skype invitation for testing for tomorrow around 7pm your time.
I have trialed the functionality described by Yacine in Explanations on VisioDB 2019 and for the most part, it works fine on my machine.
Yes it automatically draws the access data onto the Visio page. (boxes and connections)
Yes It deletes selected rows on Main form from the Visio page.
only error i experience occurs when a function tries to use a dedicated path to the database >>> path error C:\Users\gay5v1\desktop\... etc
I tried to resolve this in Settings_Local.
Hay Yasine,
I have Cliff decided to work on getting visio to work and we have uploaded a new version 6 to the drive
to get it to work copy the folder over to somewhere to your local drive or your desktop.
Then open up the vsdm. Go to Addins bar>Panel. We have not yet managed to get the keywords to work yet but the object type drop-down does. It accesses the database to get the data/ If you try dropping down to the logical application component it will populate the list box and allow you to select one or more items. use any of the buttons to see what happens.
Mike asked about how to reference doc values for fixed list fields in shapes.
Here's a drawing illustrating this architecture.
yes that is great...
@Yacine.... is there any reason why the vba code you have in the front end Access component, will not work in the Visio vba environment ?
I sense , Of the two access components "front end 190930" and "ref_YG 190924" ... the front end 190930 can be encoded into the Zeichnung1 vsdm ( even the GUI forms). while the back end>> ref YG 190924 can remain as an ODBC (Access or MYSQL).
The rationale for this is >> ... to keep Data tables and Forms separated. allowing for the data to be positioned as tabulated data structures in a back end ODBC, and the GUI and all code to reside within the vector graphic.
I appreciate your comments.
cheers
Cliff
Hi Cliff,
Yes there are many reasons why the Access code won't work in Visio.From an application external to Access, you need to explicitely open a connection to the database.(https://www.connectionstrings.com/access/) There is no currentDB from where you can start directly.
Here is some sample code showing how to retreive data from an Access DB (Access 2016 in my case)
Sub getData()
On Error GoTo errHandler
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DBFullName As String
'Replace the retreival of the DB by something more appropriate (document property, settings file, etc.)
DBFullName = ActiveDocument.Path + "VisioDB_Ref_YG_190924.accdb"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBFullName & ";"
Set rs = New ADODB.Recordset
TableName = "TableDefs"
Criteria = "Category = 'Object'"
SQLStr = "SELECT DISTINCT * FROM " & TableName & " WHERE " & Criteria
rs.Open SQLStr, cn, adOpenStatic, adLockOptimistic, adCmdText
If rs.RecordCount < 1 Then
MsgBox "no data found"
GoTo closeDB
End If
With rs
.MoveFirst
Do While Not .EOF
Debug.Print !FieldName; " "; !FieldLabel; " "; !FieldFormat; " "; !FieldValue 'or any other field of the table
.MoveNext
Loop
End With
closeDB:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set cn = Nothing
Exit Sub
errHandler:
Debug.Print "Error in getData: "; Err.Description
Resume closeDB
End Sub
Otherwise, keeping the references in external files makes sense.
Cheers,Yacine
Hi Yacine.
Yes the currentDB is void in the Visio environment, however...
I position the .vsdm file and the .addcb file in the same folder, thereafter , the >> <ThisDocument.Path&> targets the database.
coded in Visio environment the following sample:
Public Function UpDate_Obj_Name(ByVal ID As Integer, ByVal Name As String)
'Update the Name of this object in the database by object ID
Dim record As String
Dim DB As Database
Set DB = DBEngine.OpenDatabase(ThisDocument.Path & "VisioDB") 'Identify the Database
record = "UPDATE [Object] SET [Object].Name = '" & Name & "' " & _
"WHERE (((Object.ID)=" & ID & "));"
DB.Execute (record)
DB.Close
End Function
This method also requires references to be checked in the Visio references listing.
Prost !
That' right, namely the ADO library or "Microsoft ActiveX Data Objects __ Library"
Yacine \ Nickolay
in version 13, I believe is available to you on one drive.
In this version , all code is in Visio, all data is in access. (N.B. 64 bit Visio 2016 pro and 64 bit access used)
This version will perform to the loose specification.>> automatically updates data tables when object dropped on page .. automatically update data tables when connector dropped on page. automatically update object when shape data altered. * update connection(shape) via right click action menu.
However , the loose specification is for the solution to be >> all code in Visio professional and all data is in MYSQL .
Therefore , >> can you demonstrate how to write SQL query s in Visio VBA that shall select / append/ update/ delete data from a MySQL database please ?
Michael is providing a MySQL database to test with . however .. I haven't had much success ricocheting data back and forth to it using a vba coded macro. :(
Note: The Visio addin Database wizard can connect to Michaels MYSQL by creating a DSN for the cloud database and data transfer is seen to occur back and forth.
regards
Cliff
Simply put, we are using an ODBC connection to a MySQL Datasource. (I can give you IP address if you wish and set you up with a remote connection if you like), cliff and I cannot for the life of us figure out how to get this latest version to talk to the database. I have attached the database
May it be sql syntax? I.e. shouldn't it be `something` (backquotes) instead of [something] (square quotes) for mysql by default?
Or you have troubles connecting to the database, not selecting from it?
Nikolay,
both
1. connecting to it and;
2. selecting from it.
In the first instance, how can I be convinced I am connected to it , prior to attempting to select from it.
Can you demo or provide a link to a code sample showing the dims of connections and recordsets etc, and what should be checked in the references toolbox.
best regards
Cliff
It should be as straightforward as Access.
1. Make sure you have MySQL ODBC Driver installed, proper version (i.e. if you have Visio x64, the driver should be x64, and if you have Visio x86, the driver should be x86)
https://dev.mysql.com/downloads/connector/odbc/
2. Create data source for your database (also, proper version, i.e. x64 / x86)
(https://i.paste.pics/874ac8f5218c5b0108d945ecaa36dad0.png)
3. Write some VBA code to interact with it.
Set DB = DBEngine.OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=TEST")
record = "UPDATE Object SET Name='hello' where Object.ID=1"
DB.Execute record
DB.Close
Of course this assumes that you have the MySql database, with proper structure set up somehow.
Please note that syntax change - for mysql, you should use not [Object] but Object or `Object`
Thank you Nikolay,
The following code is in Visio coding environment and targets an Access database in the same folder, on the local machine.
Public Function GetLastObjectID() As Integer
'return the last and highest primary key number in the Object table
Dim DB As DAO.Database
Dim rstsource As DAO.Recordset
Dim num As Integer
On Error GoTo endfunction
Set DB = DBEngine.OpenDatabase(ThisDocument.Path & DBS) '' thisdocument & DBS is a string that equates to _> "C:\ Cliff\VisioDB\VisioDB.accdb"
Set rstsource = DB.OpenRecordset("SELECT tbl_Object.ID " & _
"FROM tbl_Object " & _
"ORDER BY tbl_Object.ID;")
If rstsource.RecordCount > 0 Then
With rstsource
.MoveLast
Do While Not .EOF
GetLastObjectID = !ID 'The entry for the last row of primary key column is retrieved from tbl_Object
.MoveNext
Loop
End With
End If
DB.Close
endfunction:
End Function
___________________________________________________________________________________________
Nikolay,
Do you think the following code will work the same as the above code, now that the data is moved into MySQL, on the same local machine ?
Is it kosher to dimension as MySQL as ->> Dim DB As DAO.Database correct ?
does MySQL have a ->> .OpenRecordset method ?
Public Function GetLastObjectID() As Integer
'return the last and highest primary key number in the Object table
Dim DB As DAO.Database
Dim rstsource As DAO.Recordset
Dim num As Integer
On Error GoTo endfunction
Set DB = DBEngine.OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=TEST")''
Set rstsource = DB.OpenRecordset("SELECT 'tbl_Object'.ID " & _
"FROM 'tbl_Object' " & _
"ORDER BY 'tbl_Object'.ID;")
If rstsource.RecordCount > 0 Then
With rstsource
.MoveLast
Do While Not .EOF
GetLastObjectID = !ID 'The entry for the last row of primary key column is retrieved from tbl_Object
.MoveNext
Loop
End With
End If
DB.Close
endfunction:
End Function
I think it should.
This code is not specific to Access or MySql, it uses a high-level Microsoft library for VBA called DAO (Data Access Objects?) to communicate with the database (that is where the "OpenDatabase" function coming from).
:D Thank you Nikolay !
that worked spot on.
result :
64 bit access database now transferred into MySQL at far end location (cloud).
all code on local machine Visio 64 bit .
sincere thanks
regards
Cliff
Yacine ..
attached is the image of the object stencil...
the additional shape data > InstantID is what I require to be added into the text field section of the original stencil..
Can you describe how to add the extra row into the object stencil in the text field section please.
Cliff
Hello Cliff,Sorry for answering your question so late (2019 ended for me on December 17th :) ).
So happy new year!
The routine you are looking for is "set_fields" in the module "VisioHelper" in the front-end DB.
Sub set_fields(shape As Object, dataset As String)
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim label_ As String
Dim val_ As String
Dim typ As Integer
Dim formt As String
Dim sortkey_ As String
Set DB = CurrentDb
strSQL = "SELECT * FROM TableDefs WHERE Category = '" & dataset & "' ORDER BY FieldSortKey;"
Set rs = DB.OpenRecordset(strSQL, dbOpenDynaset)
With rs
.MoveFirst
Do While Not .EOF
If !prop_user Then 'True = prop field, else user field
label_ = Nz(!fieldlabel, !FieldName)
val_ = Nz(!fieldvalue, "")
typ = Nz(!fieldtype, 0)
formt = Nz(!FieldFormat, "")
sortkey_ = Nz(!fieldSortKey, "")
setPropRow shape, !FieldName, label_, val_, typ, formt, sortkey_
Else
setUserRow shape, !FieldName, !fieldvalue
End If
.MoveNext
Loop
End With
End Sub
To run it from within Visio you will obviously need to re-write a couple of lines.
Best regards,
Happy New Year Yacine, :D
Thankyou for your reply !. However, I managed to use a previous method that you posted previously on forum (DisplayProps.vss) to generate a new stencil.
santa's elf has been very busy durch Weihnachten. :P
The code and along with a GUI is within Visio and the database is now a MySQL backend.
The Brief:
A Visio based Tool that could automate diagrams from a database.
Control the administration of Diagrams within an enterprise.
The loose spec on the current version.
Functionality:
manage an object (add\modify\delete objects on object table)
manage a diagram (add\modify diagram on diagram table)
manage connections between objects (add\delete connection on connection table)
Reports:
search for diagrams that contain an object from a list of objects.
search for diagrams that contain a connection from a list of connections.
Administration:
Date Author stamp on any entry change to diagram table.
attached image shows diagram output.
cheers
Cliff
nice, congrats.