This Client Server article is republished in its entirety from 2004 for reference purposes.

By Bentley Technical Support Group
03 May 2004

Reading Elements

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.

See Also

Client Server Archive

MicroStation Desktop TechNotes and FAQs

Comments or Corrections?

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!