Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
  • Welcome
  • Products
  • Support
  • About
  • More
  • Cancel
MicroStation
  • Product Communities
  • More
MicroStation
MicroStation Wiki Reading Elements From A Microsoft Access Database With VBA [CS]
    • Sign in

    • +MicroStation Wiki
    • +Administration Wiki
    • +Annotations Wiki
    • +Bentley View Wiki
    • +MicroStation PowerDraft
    • -Programming Wiki
      • A class to help create and modify text element
      • A Complete Example
      • A MicroStation VBA Example With Bentley ProjectWise
      • AddIn Development Using VB.NET
      • C# .NET Template with IPrimitiveCommandEvents Class
      • Capturing Graphics in Dynamic Views
      • Compiling MDL Applications
      • Database Manipulation
      • Debugging Native Code MDL Applications
      • Developing Code in VBA
      • Developing MicroStation Applications For DWG Files
      • Drag and Drop in MicroStation
      • Error: "Cannot save changes to VBA project 'Support' because it is read-only"
      • Getting And Setting Elements Using Graphic Groups In VBA [CS]
      • Getting Started with Visual Basic
      • How To Write A Recursive Routine In MicroStation VBA [CS]
      • Introducing Segment3D Methods In MicroStation V8 2004 Edition
      • Known issues in MDL and MicroStationAPI
      • Launching VBA Applications As Initapps Or Dgnapps [CS]
      • Learning MicroStation Addins Step by Step
      • MDL - Getting Started With XAttributes In MicroStation V8 XM Edition
      • MDL - Native Code Application Development
      • MDL Or MicroStation BASIC Choosing The Right Tool [TN]
      • MFC Dialog And Native Window Support
      • Microsoft Office VBA Patch Utility
      • MicroStation BASIC FAQ
      • MicroStation BASIC Limits [FAQ]
      • MicroStation Developer Documentation and Example Code
      • MicroStation Programming Advice
      • MicroStation SDK
      • MicroStation V8 Programming Tools Readme
      • MicroStation V8 VBA Programming Resources [CS]
      • MicroStation V8 XM Edition View Code Updates
      • MicroStation VBA Resources Revisited [CS]
      • Migrating Dimension Code To MicroStation V8
      • Migrating MDL Applications To Native Code
      • Mouse Wheel Events And The Visual Basic 6.0 IDE
      • Porting MDL Applications To MicroStation V8
      • Reading Elements From A Microsoft Access Database With VBA [CS]
      • Running MDL Applications
      • Scanning For MicroStation V8 Elements In VBA [CS]
      • Unleash A Workspace Wizard Automating Workspace Creation With MicroStation V8 And VBA [CS]
      • Using VBA To Detect The Current Or Last Used Command
      • Using VBA To Programmatically Export A VBA Project [CS]
      • Using VBA To Programmatically Import A VBA Projects Components And References [CS]
      • VBA -- Creating A Complex Application
      • VBA Interface Error: failed creating the comp manager - 0x80040583
      • VBA interface error: unable to get IDE
      • vba recording
      • Working With Levels In MicroStation VBA [CS]
      • Writing An MDL Application To Output Custom Placemarks To Google Earth
      • [V8i C++] PointCloud IPointCloudChannelDisplayHandler
    • +Visualization Wiki

     
     Questions about this article, topic, or product? Click here. 

    Reading Elements From A Microsoft Access Database With VBA [CS]


     

    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!

     

     

    • Archived Client Server
    • client server
    • client server 2004
    • Share
    • History
    • More
    • Cancel
    • Elisa McGraw Created by Elisa McGraw
    • When: Fri, Jul 17 2009 5:42 PM
    • Dan Koval Last revision by Bentley Colleague Dan Koval
    • When: Mon, Aug 26 2013 9:35 AM
    • Revisions: 13
    • Comments: 1
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2022 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies