How do you access data from a Access Database from Visio using VB.

[Vondre Daniel]

I'm using Visio 2002 and I want to double-click on a shape in the drawing
and have it query an Access Database for specific information. I'm not sure
how you set up the connection string in vba to the database. I need this to
work for an internal and external database. I've been trying to write code in
vba an place that function/procedure in shape's shapesheet under the events
group. Can someone please assist in this tasks. Thanks!

 

[Al Edlund]

you might try something like this

' this is used by the cell changed event to allow us to just change a
' single cell in a record, rather than full record updates. work in
' progress

Public Sub subDiscreteFieldUpdate(strTable As String, strIndex As String,
strGUID As String, strField As String, strValue As String)
Dim intResult As Integer
Dim str_db_filename As String
Dim db As Database
Dim visDocument As Visio.Document
Dim visPage As Visio.Page
Dim SaveErr As Long
Dim errDB As ADODB.Error

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim strProvider As String
Dim strSource As String
Dim strConn As String
Dim strSelect As String

strProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
strSource = "Data Source="
Dim strProviderED As String
strProviderED = ";"

On Error GoTo DiscreteField_Exit

'first we have to find out which database is associated with this
document
Set visDocument = Visio.ActiveDocument
Set visPage = visDocument.Pages.item("Project Definition")
str_db_filename = visDocument.Path &
visPage.PageSheet.Cells("prop.database_file.value").ResultStr("")

strConn = strProvider & strSource & str_db_filename & strProviderED
cnn.Open strConn

strSelect = "SELECT * FROM " & strTable & " Where " & strIndex & " =
" & strGUID
rst.Open strSelect, cnn, adOpenKeyset, adLockOptimistic

If (rst.BOF And rst.EOF) Then
' Debug.Print "err discrete field update " & strGUID & " record
not found"
GoTo DiscreteField_Exit
End If

' MsgBox strValue
If strField = "propcost" Then
' Debug.Print "propcost " & Int(strValue)
rst.Fields(strField).Value = Int(strValue)
Else
rst.Fields(strField).Value = strValue
End If
rst.Update


DiscreteField_Exit:
rst.Close
cnn.Close
DoEvents

Exit Sub

DiscreteField_Err:
SaveErr = Err.Number
If SaveErr <> 0 Then
Debug.Print "Err in subDiscreteFieldUpdate is " & Err & " " &
Err.Description
Debug.Print strGUID & " " & strField & " " & strValue

For Each errDB In cnn.Errors
Debug.Print "DB Update " & " " & strGUID & " " & strField & " "
& strValue
Debug.Print "DB Description: " & errDB.Description
Debug.Print "DB Number: " & errDB.Number & " (" & _
Hex$(errDB.Number) & ")"
Debug.Print "JetErr: " & errDB.SQLState
Next
' Resume DiscreteField_Exit:
End If

End Sub

 

[Vondre Daniel]

Thanks! I'm an apprentice to VBA, therefore I was only able to follow certain
segments of this code. When you program Visio do you always have to have data
in the user-defined cells and the custom properties? The reason I ask is
because I have neither. I have a drawing that I created and I want to
retreive information from the database based off of doulble clicking that
particular shape! Is that possible? Thank you in advance!

 

[Al Edlund]

there is an underlying question that has to be answered first and that is
how to identify a doubleclicked object to the database, i.e. the unique key
that attempts to guarantee that the correct record is accessed. In this
example we use a GUID that can be assigned with vba when the object is
created.
The second part of the question as to whether you require either a user
field or a custom property is up to you as the developer and what you want
to do with the data when it is retrieved. If you don't use a guid then what
attribute of the object will you use to identify it?
Al

 

[Vondre Daniel]

On the shape itself, I have it identified as what it is and what I would be
looking for in the database. (e.g. For instance one shape is labeled "ser1",
which is unique itself throughout the database) I want to the code behind the
scene of that shape, "ser1", to go out and query that Access Database for
"ser1". So I was thinking that in the code for every shape I can query it to
get "ser1" or "ser2", etc. Retreiving every piece of data belonging to that
unique character.