Plotting Access Table's Contents into a Visio document

[pepenacho]

Hello:

I am trying to pick up a discussion that I had in another thread, but where
we were unable to clarify a question.

I am doing some research on how to link Access data into Visio, so that my
visio structure chart can be updated from
Access.

I am NOT looking for a chart of all my tables, plotted into a visio
(article: "Visio-Based Database Modeling in Visual Studio .NET")

I am looking for an output into a Visio chart, from data (structure)
contained in a table. I then need to be able to update that chart from the
original access table. I understand that formatting may not be updatable and
that's ok. However, if someone is aware of how that may be automated, I would
appreciate any information for that as well.

I am really just looking to be be pointed to an appropriate article/write-up
on knowledgebase ( or on msdn.microsoft.com/library) so that I can read up on
it. I basically do not know the topic I should be searching for. Microsoft's
descriptions always deviate (sooner or later) from the "simple."

Thanks,
pepe

 

[John Marshall, MVP]

Since the result would be in the form of a table, wouldn't Access or Word be
a better platform.

I use the following in Access to populate an Access table with a description
of the tables within my database.

Public Sub AccessTablesAndFields()

Dim db As Database
Dim rst As Recordset
Dim dbs As DAO.Database, fld As DAO.Field, flds As DAO.Fields, tdf As
DAO.TableDef

Dim fldCur As Field
Dim strAllowZeroLength As String, strAttributes As String, strCaption As
String
Dim strColumnWidth As String, strDecimalPlaces As String, strDefault As
String
Dim strDescription As String, strFieldType As String, strFormat As String
Dim strLookupSQL As String, strOrdinalPosition As String, strRowSource As
String
Dim strSize As String, strTableName As String
Dim i As Integer, j As Integer, k As Integer
'0 Value
'1 Attributes
'2 CollatingOrder
'3 Type
'4 Name
'5 OrdinalPosition
'6 Size
'7 SourceField
'8 SourceTable
'9 ValidateOnSet
'10 DataUpdatable
'11 ForeignName
'12 DefaultValue
'13 ValidationRule
'14 ValidationText
'15 Required
'16 AllowZeroLength
'17 FieldSize
'18 OriginalValue
'19 VisibleValue
'20 ColumnWidth
'21 ColumnOrder
'22 ColumnHidden
'23 DisplayControl
'24 IMEMode
'25 IMESentenceMode
'26 UnicodeCompression

Set dbs = CurrentDb()

CurrentDb.Execute "DELETE * FROM AccessTables", dbFailOnError

Set rst = dbs.OpenRecordset("AccessTables", dbOpenDynaset)

For Each tdf In dbs.TableDefs
strTableName = tdf.Name
If Left(strTableName, 4) <> "MSys" Then
Set flds = tdf.Fields
For Each fld In flds

strDescription = " "
strCaption = " "
strLookupSQL = " "
strSize = " "
strOrdinalPosition = " "
strDefault = " "
strAttributes = " "
strAllowZeroLength = " "
strColumnWidth = " "
strDecimalPlaces = " "
strFormat = " "
strRowSource = " "

strFieldType = FieldTypeName(fld.Type)
For j = 0 To fld.Properties.Count - 1
Select Case fld.Properties(j).Name
Case "Description": strDescription = fld.Properties(j).Value
Case "Caption": strCaption = fld.Properties(j).Value
Case "Rowsource": strLookupSQL = fld.Properties(j).Value
Case "Size": strSize = fld.Properties(j).Value
Case "OrdinalPosition": strOrdinalPosition =
fld.Properties(j).Value
Case "DefaultValue": strDefault = fld.Properties(j).Value
Case "Attributes": strAttributes = fld.Properties(j).Value
Case "AllowZeroLength": strAllowZeroLength =
fld.Properties(j).Value
Case "ColumnWidth": strColumnWidth = fld.Properties(j).Value
Case "DecimalPlaces": strDecimalPlaces =
fld.Properties(j).Value
Case "Format": strFormat = fld.Properties(j).Value
Case "RowSource": strRowSource = fld.Properties(j).Value
End Select
Next j

With rst
.AddNew
!TableName = tdf.Name
!FieldName = fld.Name
!FieldType = strFieldType
!FieldReq = fld.Required
!FldDescription = strDescription
!FldCaption = strCaption
!FldLookupSQL = strLookupSQL
!FldSize = Val(strSize)
!FldOrdinalPosition = strOrdinalPosition
!FldDefault = strDefault
!FldAttributes = strAttributes
!FldAllowZeroLength = strAllowZeroLength
!FldColumnWidth = strColumnWidth
!FldValidationRule = fld.ValidationRule
!FldDecimalPlaces = strDecimalPlaces
!FldFormat = strFormat
!FldRowSource = strRowSource

.Update
End With

Next fld
End If
Next tdf

End Sub


John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[pepenacho]

John, thanks for the response, but again, I'm not plotting tables in a
database. I think we missed the point here.

I have a table, in which the data has an oganizatinal structure to it. That
structure is large and unfeasable to maintain in any other way, but in a
table. It also needs to be represented visually on a Visio flow chart. Those
are all musts, because the structure will be periodically updated - these
updates will be hard to do by hand in Visio.

The idea is to update the the information in the access table, from which
the Visio itself will pull the updates. The anology is: a link in an Excel
worksheet to a query in Access, so that the spreadsheet can be updated with
data from Access.

Is this possible Visio and if so where can I read up on this? I have a
feeling that it must be.

Thanks,
Pepe