This Client Server article is republished in its entirety from 2004 for reference purposes.
By Bentley Technical Support Group 03 May 2004
If you have element information stored in a Microsoft Access database and want to programmatically add it to your design file, this article will illustrate an easy way to use Active X Data Objects (ADO) with VBA to accomplish your goal.
First, let's look at the database. In this example, we will work with Line elements, but you could use any element just as easily.
In your Access database, you have the following numeric Data Types as Field Names: Type, Color, Level, Lineweight, Linestyle, StartpointX, StartpointY, StartpointZ, EndPointX, EndPointY, and EndpointZ.
Figure 1
Figure 2
Figure 3
Figure 4
Figure 5 Note: The author is using Access XP for this example. However, you should be able to achieve the same results using Access 2000.
Now, set up your DSN (Data Source Name). In Windows XP, go to Start > Settings < Control Panel < Administrative Tools < Data Sources (ODBC). This will appear differently Windows 2000.
Now select the Microsoft Access Driver {*.mdb}.
Type "myDSN" as the Datasource Name.
Press the Select Button, go to the directory where the database is located, and select it.
That's all there is to it. Now your DSN is set up and you can start writing the source code:
Option Explicit
Sub dbRead()
'Declare all my variables.
Dim intCol As Integer
Dim ElmType As MsdElementType
Dim oEl As Element
Dim startpnt As Point3d
Dim endpnt As Point3d
Dim strCon As String
Dim rsStr As String
Dim rs As ADODB.Recordset
Dim oConn As ADODB.Connection
'Set connection string to my DSN.
strCon = "myDSN"
'Set connection and recordset objects.
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Connect to my database.
oConn.Open (strCon)
'Set the SQL query for the recordset string.
rsStr = "Select * from tblElements"
'Open my recordset.
rs.Open rsStr, oConn
'Do until end of recordset
Do Until rs.EOF
For intCol = 0 To rs.Fields.Count - 1
'Check my element type, I am only using line elements here.
ElmType = rs.Fields("type")
If ElmType = 3 Then
startpnt.X = rs.Fields("startPointX")
startpnt.Y = rs.Fields("startPointY")
startpnt.Z = rs.Fields("startPointZ")
endpnt.X = rs.Fields("endPointX")
endpnt.Y = rs.Fields("endPointY")
endpnt.Z = rs.Fields("endPointZ")
' create line element
Set oEl = CreateLineElement2(Nothing, startpnt, endpnt)
' set symbology values
Set oEl.Level = ActiveDesignFile.Levels.Find(rs.Fields("level"))
oEl.Color = rs.Fields("color")
oEl.LineWeight = rs.Fields("lineweight")
oEl.LineStyle = ActiveDesignFile.LineStyles.Find(rs.Fields('linestyle'))
' add line to design file
ActiveModelReference.AddElement oEl
' display line
oEl.Redraw
End If
Next
'Move to the next recordset.
rs.MoveNext
Loop
'Close my recordset
rs.Close
'Close my connection.
oConn.Close
End Sub
This is a very easy way to add elements from a database to a design file with VBA. This can be used with any database. This type of functionality can be used to write elements to a database, as well. ADO opens a whole knew realm of ways for storing drawing information.
Client Server Archive
MicroStation Desktop TechNotes and FAQs
Bentley's Technical Support Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!