VisioDB Project - Visio Database Project Releases

Started by maclarkson, September 19, 2019, 12:14:41 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

cliff50

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

Yacine

#31
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

cliff50

#32
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

cliff50

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

maclarkson

#34
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.

maclarkson

Hi Cliff,

I found your code. Right Click on the object > Code

cliff50

@ 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

Nikolay

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?

Nikolay

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

maclarkson

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?

maclarkson

#40
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?

Yacine

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. :(
Yacine

Nikolay

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.

maclarkson

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.

maclarkson

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?