Visio 2003 Database Link to Diagram

[MitzyGoose]

Hi! We're using Visio 2003 and are not allowed to upgrade to Visio 2007.
We have network diagrams with literally hundreds of shapes that we'd like to
link to a SQL database. I've been using the Database Wizard but found that
it had some issues that were also noted by other folks in other internet
postings.
1. You can link the shapes and everything will be fine. Then,
mysteriously, the database refresh will fail saying that the table doesn't
exist, when clearly it does. If you manually refresh each shape, it'll work,
but after the error occurs you can't refresh all the shapes on the document.
At one point, I was able to create a diagram that refreshed, but if you
deleted ONE particular object, it would fail. If you "undo" the delete, the
whole document refresh would start working again.

2. If you move the Access database to another location, the links will
break and all items will need to be relinked again!

3. If you add fields to the database table, the items which were linked to
that table/query will not display the new fields. You have to manually
relink all the objects to bring in the new fields.

For a diagram with hundreds of links, manually relinking is not an option.
Also, I noticed that Visio seems to "remember" the database associated with
the items, even if you change all of the ODBC Connection strings on every
linked shape.

I read that someone had these problems and coded his own VBA code to read
the database, then update the custom properties based on the fields in the
table/query. He said that he used the database wizard to build
user.odbcconnection. I'm guessing that he also used the select record
feature. But for refreshing, he clearly stated that he did his own database
queries and updated the custom properties.

I am having difficulty coding the database calls myself because Visio
doesn't seem to recognize ADODB objects. Is there an add-on that I need to
install so that Visio will allow me to open an ODBC datasource based on a DSN
and open the recordset myself so that I can put all fields into custom
properties and update the values myself?

 

[AlEdlund]

visio vba works just fine with adodb for accessing MS Access.
al

'
' get a single rack within a row
'
Public Sub dbSelectCabinets_rckId()

On Error GoTo ErrHandler

Dim errDB As ADODB.Error
Dim rstReturn As ADODB.Recordset
Set rstReturn = New ADODB.Recordset

' pRckId is a global property so that I can
' pass it in as a property
Dim strSelect As String
strSelect = "SELECT tblCabinets.*" _
& " FROM tblCabinets" _
& " WHERE (tblCabinets.rckId) = '" & pRckId & "';"


If pAdoDbConn Is Nothing Then
Set pAdoDbConn = New ADODB.Connection
End If

'pAdodbRecords is a a local property to get at the
' returned recordset after we close the connection
If pAdoDbRecords Is Nothing Then
Set pAdoDbRecords = New ADODB.Recordset
End If

pAdoDbRecCt = 0


' connect to the current connection string
' this is saved in a global in the module so that I can retarget
pAdoDbConn.Open pAdoDbConnStr

rstReturn.Open strSelect, pAdoDbConn, adOpenKeyset, adLockOptimistic


If (rstReturn.BOF And rstReturn.EOF) Then
CloseSession
GoTo ExitHandler
Else
Set pAdoDbRecords = rstReturn
End If

' build my own variable to save the returned recordset size
Dim intX As Integer
Do While Not pAdoDbRecords.EOF
pAdoDbRecCt = pAdoDbRecCt + 1
pAdoDbRecords.MoveNext
Loop

If pAdoDbRecCt = 1 Then
MoveRecordToProperties
End If

ExitHandler:
Exit Sub

ErrHandler:

pAdoDbLastErr = Err.Number
MsgBox " dbSelectCabinets_rckId = " & Err.Description
OleDbErr "dbSelectCabinets_rckId", Err

End Sub

 

[MitzyGoose]

Thanks...I finally found out that you have to go into the Visual Basic
window, Tools, References and choose Microsoft ActiveX Data Objects so that
the ADODB references will not cause a compiler error. I kept looking for
the references in the Visio window.

 

[Betty Carson]

There is some information about Database in Visio at the Visio Toolbox website.
Microsoft Office Visio 2007 diagrams takes real-time data Embed together and programs in Visio from a wrod doc will translate into visual Aero Glass effects, themes, and transparencies.

http://bit.ly/7IJTDX



MitzyGoos wrote:

Visio 2003 Database Link to Diagram
21-Aug-08

Hi! We're using Visio 2003 and are not allowed to upgrade to Visio 2007.
We have network diagrams with literally hundreds of shapes that we'd like to
link to a SQL database. I've been using the Database Wizard but found that
it had some issues that were also noted by other folks in other internet
postings.
1. You can link the shapes and everything will be fine. Then,
mysteriously, the database refresh will fail saying that the table doesn't
exist, when clearly it does. If you manually refresh each shape, it'll work,
but after the error occurs you can't refresh all the shapes on the document.
At one point, I was able to create a diagram that refreshed, but if you
deleted ONE particular object, it would fail. If you "undo" the delete, the
whole document refresh would start working again

2. If you move the Access database to another location, the links will
break and all items will need to be relinked again

3. If you add fields to the database table, the items which were linked to
that table/query will not display the new fields. You have to manually
relink all the objects to bring in the new fields

For a diagram with hundreds of links, manually relinking is not an option.
Also, I noticed that Visio seems to "remember" the database associated with
the items, even if you change all of the ODBC Connection strings on every
linked shape

I read that someone had these problems and coded his own VBA code to read
the database, then update the custom properties based on the fields in the
table/query. He said that he used the database wizard to build
user.odbcconnection. I'm guessing that he also used the select record
feature. But for refreshing, he clearly stated that he did his own database
queries and updated the custom properties

I am having difficulty coding the database calls myself because Visio
doesn't seem to recognize ADODB objects. Is there an add-on that I need to
install so that Visio will allow me to open an ODBC datasource based on a DSN
and open the recordset myself so that I can put all fields into custom
properties and update the values myself?

Previous Posts In This Thread:

Visio 2003 Database Link to Diagram
Hi! We're using Visio 2003 and are not allowed to upgrade to Visio 2007.
We have network diagrams with literally hundreds of shapes that we'd like to
link to a SQL database. I've been using the Database Wizard but found that
it had some issues that were also noted by other folks in other internet
postings.
1. You can link the shapes and everything will be fine. Then,
mysteriously, the database refresh will fail saying that the table doesn't
exist, when clearly it does. If you manually refresh each shape, it'll work,
but after the error occurs you can't refresh all the shapes on the document.
At one point, I was able to create a diagram that refreshed, but if you
deleted ONE particular object, it would fail. If you "undo" the delete, the
whole document refresh would start working again

2. If you move the Access database to another location, the links will
break and all items will need to be relinked again

3. If you add fields to the database table, the items which were linked to
that table/query will not display the new fields. You have to manually
relink all the objects to bring in the new fields

For a diagram with hundreds of links, manually relinking is not an option.
Also, I noticed that Visio seems to "remember" the database associated with
the items, even if you change all of the ODBC Connection strings on every
linked shape

I read that someone had these problems and coded his own VBA code to read
the database, then update the custom properties based on the fields in the
table/query. He said that he used the database wizard to build
user.odbcconnection. I'm guessing that he also used the select record
feature. But for refreshing, he clearly stated that he did his own database
queries and updated the custom properties.

I am having difficulty coding the database calls myself because Visio
doesn't seem to recognize ADODB objects. Is there an add-on that I need to
install so that Visio will allow me to open an ODBC datasource based on a DSN
and open the recordset myself so that I can put all fields into custom
properties and update the values myself?

visio vba works just fine with adodb for accessing MS Access.
visio vba works just fine with adodb for accessing MS Access.
al

'
' get a single rack within a row
'
Public Sub dbSelectCabinets_rckId()

On Error GoTo ErrHandler

Dim errDB As ADODB.Error
Dim rstReturn As ADODB.Recordset
Set rstReturn = New ADODB.Recordset

' pRckId is a global property so that I can
' pass it in as a property
Dim strSelect As String
strSelect = "SELECT tblCabinets.*" _
& " FROM tblCabinets" _
& " WHERE (tblCabinets.rckId) = '" & pRckId & "';"


If pAdoDbConn Is Nothing Then
Set pAdoDbConn = New ADODB.Connection
End If

'pAdodbRecords is a a local property to get at the
' returned recordset after we close the connection
If pAdoDbRecords Is Nothing Then
Set pAdoDbRecords = New ADODB.Recordset
End If

pAdoDbRecCt = 0


' connect to the current connection string
' this is saved in a global in the module so that I can retarget
pAdoDbConn.Open pAdoDbConnStr

rstReturn.Open strSelect, pAdoDbConn, adOpenKeyset, adLockOptimistic


If (rstReturn.BOF And rstReturn.EOF) Then
CloseSession
GoTo ExitHandler
Else
Set pAdoDbRecords = rstReturn
End If

' build my own variable to save the returned recordset size
Dim intX As Integer
Do While Not pAdoDbRecords.EOF
pAdoDbRecCt = pAdoDbRecCt + 1
pAdoDbRecords.MoveNext
Loop

If pAdoDbRecCt = 1 Then
MoveRecordToProperties
End If

ExitHandler:
Exit Sub

ErrHandler:

pAdoDbLastErr = Err.Number
MsgBox " dbSelectCabinets_rckId = " & Err.Description
OleDbErr "dbSelectCabinets_rckId", Err

End Sub


Thanks...
Thanks...I finally found out that you have to go into the Visual Basic
window, Tools, References and choose Microsoft ActiveX Data Objects so that
the ADODB references will not cause a compiler error. I kept looking for
the references in the Visio window.

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
..NET Constructor Applied
http://www.eggheadcafe.com/tutorial...2bd-2efad5224a3f/net-constructor-applied.aspx