VisioDB Project - Visio Database Project Releases

[cliff50]

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.

[maclarkson]

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.

[Yacine]

Mike asked about how to reference doc values for fixed list fields in shapes.
Here's a drawing illustrating this architecture.

[cliff50]

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

[Yacine]

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)

Code

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

[cliff50]

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 !

[Yacine]

That' right, namely the ADO library or "Microsoft ActiveX Data Objects __ Library"

[cliff50]

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

[maclarkson]

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

[Nikolay]

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?

[cliff50]

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

[Nikolay]

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)


3. Write some VBA code to interact with it.

Code

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`

[cliff50]

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

[Nikolay]

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).

[cliff50]

  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