Visio linking shapes to data

Started by cliff50, August 30, 2019, 06:12:38 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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

#1
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.
Yacine

cliff50

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

#3
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:
DBEngine.OpenDatabase("", dbDriverComplete, False, "ODBC;DATABASE=MyDatabase;DSN=MyDSN")

You can also connect without DSN, like this:
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 ;D

Yacine

Quote from: Nikolay on September 01, 2019, 10:14:14 AM
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 ;D

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?
Yacine

Nikolay

I didn't really assume any advice (except for the connection string) :P I just wanted to inform  ;D 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 ;D
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 ;D
Maybe he's going to migrate the Access database to an Azure SQL database? :o I'm not sure if DAO it's gonna work with it - but well, maybe it will ;D

Yacine

Yacine

cliff50

 :) bless you  !  :D

"Maybe he's going to migrate the Access database to an Azure SQL database? :o""
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). :o  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  :D
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.