Visio Professional has the ability to link to various data sources, and one of the most popular is SQL Server. The DATA\Link Data to Shapes … function will present a UI to choose from a table or view, but does not provide any stored procedures. Therefore, this article present a VBA macro that demonstrates how a stored procedure can be added as a refreshable data recordset in Visio.
This macro uses the uspGetManagerEmployees procedure from the AdventureWorks sample database ( see https://msftdbprodsamples.codeplex.com/releases ). It expects one integer parameter, and returns a list of employees for a given manager’s business entity id. You will need to enter your own Data Source and Initial Catalog values.
Public Sub AddOrRefreshFromStoredProc()
On Error GoTo errHandler
Dim dds As Visio.DataRecordset 'The data recordset
Dim ary() As String 'Array to hold the p key columns
Dim SQLConnStr As String 'The connection string
Dim SQLCommStr As String 'The Command string
Dim datasetName As String 'The dataset name
SQLConnStr = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=True;" & _
"Initial Catalog=AdventureWorks2014;" & _
"Data Source=ARUBA\SQLExpress2014x8;" & _
"Use Procedure for Prepare=1;"
SQLCommStr = "EXEC dbo.uspGetManagerEmployees "
Dim busEntity As Variant
busEntity = InputBox("Which business entity to you want to retrieve for?", SQLCommStr, 2)
If IsNumeric(busEntity) = False Then
MsgBox "You must enter an integer", vbCritical, SQLCommStr
GoTo exitHere
End If
SQLCommStr = SQLCommStr & CStr(busEntity)
ary() = Split("BusinessEntityID", ";")
datasetName = "uspGetManagerEmployees for " & CStr(busEntity)
'Check if the recordset exists already
For Each dds In Visio.ActiveDocument.DataRecordsets
If dds.CommandString = SQLCommStr Then
dds.Refresh
GoTo exitHere
End If
Next
Set dds = Visio.ActiveDocument.DataRecordsets.Add( _
SQLConnStr, SQLCommStr, _
VisDataRecordsetAddOptions.visDataRecordsetDelayQuery, _
datasetName)
dds.SetPrimaryKey VisPrimaryKeySettings.visKeySingle, ary()
dds.Refresh
Visio.ActiveWindow.Windows.ItemFromID( _
visWinIDExternalData).Visible = True
exitHere:
Exit Sub
errHandler:
MsgBox Err.Description
Resume exitHere
End Sub
The macro will simply ask for the stored procedure parameter to be entered:
The stored procedure will then be executed an a data recordset will be added to the document.
This recordset can be refreshed in the normal manner, or by re-running the macro with the same parameter.