Visio linking shapes to data

[cliff50]

hello Visio coding artisans,

I know how to write VBA code in the SDK environment of Visio to source data from an Access database.

How different would the following code be,  if the query was toward a SQL database. ?
Could you provide a sample of the code please.

TIA 


following example of VBA coded query on a 64 bit MS access database. -> (NetTracker.accdb)

Public Function GraphicPermission() As Boolean
Dim rstsource As Recordset
Dim db As Database
Dim ID As String
Set db = DBEngine.OpenDatabase(ThisDocument.path & "NetTracker.accdb")

ID = modLocking.GetUserName
GraphicPermission = True

Set rstsource = db.OpenRecordset("SELECT tblUser.UserID, tblUser.Username, tblUser.Level " & _
"FROM tblUser " & _
"WHERE (((tblUser.UserID)= '" & ID & "'));")

num = EnumerateSet(rstsource)
GraphicPermission = False
 
If rstsource.RecordCount = 1 Then
    With rstsource
        .MoveFirst
        Do While Not .EOF
            If !Level = 2 Then
                GraphicPermission = True
             End If   
                .MoveNext
           Loop
            .Close
    End With
Else
GraphicPermission = False
End If
db.Close

End Function

[Yacine]

Hi Cliff,
What do you mean by SQL database (mySQL, SQL Server, ...), since Access is also a "SQL DB"?
Specifiying the DB will help googling and finding the right connection string.
Rgds,
Y.

[cliff50]

Hi Yacine,
my apologies , on reflection -> I haven't been precise in my question.

I am operating Visio Pro 2016. 64 bit application. Microsoft Access 64 bit. Windows 10.

My question relates to linking Visio shapes to ->  Microsoft SQL Server.

I understand that both Microsoft Access and Microsoft SQL server are both ODBC-compliant applications.

In the Visio VBA coding environment, I have written code to query Microsoft Access Databases.
However , I am now curious to know, how different the code would be if the target database was -> Microsoft SQL server.

I suspect(I hope), the only alteration in example code will be the line  -> Set db = DBEngine.OpenDatabase(ThisDocument.path & "NetTracker.accdb")

I also suspect there are other references among the Visio document references tab, that will need to be selected.

I find the Visio/Access/VBA combination to be very useful for what I do.
But now I am curious to entertain a Visio/Microsoft SQL Server/VBA combination.

attached image of Create new Data Source window ... SQL Server.

I value your opinions.
Cliff
 

[Nikolay]

You seem to be correct, the only change should be this line unless you used some Access-specific SQL queries or features:
DBEngine.OpenDatabase(ThisDocument.path & "NetTracker.accdb")

For SQL Server:

Code

DBEngine.OpenDatabase("", dbDriverComplete, False, "ODBC;DATABASE=MyDatabase;DSN=MyDSN")

You can also connect without DSN, like this:

Code

Set db = DBEngine.OpenDatabase("", dbDriverComplete, False, "ODBC;DRIVER={SQL Server};SERVER=MyServer;DATABASE=MyDatabase;DSN=;")

Please note that this is not a common way to connect to a data source in Visio; There is a special user-friendly "Link Data to Shapes" feature (under the "Data" ribbon tab)
But given that you use DAO (aka "Data Access Objects" library, that is more or less dead for the past two decades), I assume you have your reasons to do it your way

[Yacine]

Please note that this is not a common way to connect to a data source in Visio; There is a special user-friendly "Link Data to Shapes" feature (under the "Data" ribbon tab)
But given that you use DAO (aka "Data Access Objects" library, that is more or less dead for the past two decades), I assume you have your reasons to do it your way

Can't agree with your advice. Connecting via code gives you so much more flexibility.
And whether DAO or ADO, who cares as long as it works?

[Nikolay]

I didn't really assume any advice (except for the connection string) I just wanted to inform  that there is a dedicated "Data" tab with this shiny "Link data to shapes" feature. I hope that M$ guys did not implement it just for giggles
Also there were there major updates to DAO it since 1997? (or was it 2012?).. It may be not the best choice if you are starting a new project
Maybe he's going to migrate the Access database to an Azure SQL database? I'm not sure if DAO it's gonna work with it - but well, maybe it will

[Yacine]

[cliff50]

  bless you  ! 

"Maybe he's going to migrate the Access database to an Azure SQL database? ""
if that is possible , yes please .

I am using Access as a data repository .. just relational tables ... nil by way of code in the MS access environment.

My assumption is the data tables can exist in any ODBC compliant data repository, that MS Visio coding can burrow into.

For what its worth, my MS Access data base hasn't bottomed out yet or developed any cracks, it just keeps hanging in there (for the past decade or more).   albeit a 32 bit to 64 bit migration.

Would migration to SQL server or Azure SQL database bring any noticeable  improvements that would help me forget about the dent in my hip pocket?

cheers and thanks again guys.
Cliff


 

[Nikolay]

If it works, dont fix it 
I think changing database makes sense if there is an issuue with it. Access engine is a well-known, supported, solid, free database engine, and has some pretty decent  UI. Unless there are performance problems, I wouldn't touch it.