CIM Lab 4 Adding Fields to a Database Northwind by elc18177


									CIM Lab 6: Downloading Bill of Material from Solidworks into ParentChildRelations table in :

We will be adding some features to the form created in Lab 5. When this form is fully working, the user
will press a button on the form which will open a “File Open” dialog box. The user will then select a
SolidWorks assembly. Then the software will automatically read the name of the assembly and the
children. It will create part names for the assembly and the parts. Then it will create the parent child
relations between the assembly and the parts. Lastly, it will show the assembly in the
ParentChildRelations form.

The steps are as follows:

Opening up the Database that was created in Lab 5

Open the form: ParentChildRelections in Design View

Add a Microsoft Common Dialog Box Control to the form: Under the Design tab select Active X
Controls and select Microsoft Common Dialog Box Control
Right click on the newly created common dialog control, select properties and change the name of the
control to : ComDia

Add a command button to the form, select Report Operation – Open Report
Click next:
For the button’s caption, type in Read SolidWorks Assembly, then click Next.

Type anything in the command line, then click Finish

Change the name on the button to Read Solidworks Assembly:
Change the name of the Command Button to ReadAssembly:

Right click on the newly created command button.

Select Event| OnClick :
Remove the existing code for that event and replace with the following code on the On Click event for the
command button:

  Dim swApp As SldWorks.SldWorks
  Dim swModel As SldWorks.ModelDoc2 ' a SolidWorks model document
  Dim fullAssemLoc As String
  Dim longstatus As Long, longwarnings As Long
  Dim docName As String ' the save name chosen by the user for the assembly without the ".sldasm"
  Dim FinalDirLoc As String ' save-to location,
  Dim fullDrawLoc As String
  Dim swConf As SldWorks.Configuration
  Dim swRootComp As SldWorks.Component2

  Set dicComps = CreateObject("Scripting.Dictionary")

  With ComDia
    .CancelError = False
    .InitDir = "c:\"
    .Filter = "Assembly (*.sldasm)|*.sldasm"
     fullAssemLoc = .FileName
  End With

  Dim posn1 As Integer
  Dim posn2 As Integer

  If Not fullAssemLoc = "" Then
     'txtLocation.Text = fullAssemLoc
     fullDrawLoc = Mid$(fullAssemLoc, 1, Len(fullAssemLoc) - 6) + "slddrw"
     posn1 = InStrRev(fullAssemLoc, "\")
     posn2 = InStrRev(fullAssemLoc, ".")
     FinalDirLoc = Left$(fullAssemLoc, posn1)
     docName = Mid$(fullAssemLoc, posn1 + 1, (posn2 - posn1) - 1)
  End If

  Set swApp = CreateObject("SldWorks.Application")
  Set swModel = swApp.OpenDoc6(fullAssemLoc, swDocASSEMBLY, 0, "", longstatus, longwarnings)
  Set swModel = swApp.ActivateDoc2(docName, False, longstatus)
  Set swModel = swApp.ActiveDoc
  Set swConf = swModel.GetActiveConfiguration
  Set swRootComp = swConf.GetRootComponent
  TraverseComponent swRootComp

  swApp.Visible = True
Add the following lines to the top of the code:

        Option Explicit
        Dim dbs As Database
        Dim sFileName As String, lPrevAssy(10) As Long, iDepth As Integer
        Dim objACAD As Object, iDwgCount As Integer, bAssyIDHasFocus As Boolean
        Dim swApp As SldWorks.SldWorks, swDoc As SldWorks.ModelDoc2
        Dim dicComps As Object

The top of the code should now look like this:

At the bottom of the code, add the following two subroutines:

Sub TraverseComponent(swComp As SldWorks.Component2)

  Dim vChildComp        As Variant
  Dim swChildComp        As SldWorks.Component2
  Dim swCompConfig        As SldWorks.Configuration
  Dim i            As Long

  vChildComp = swComp.GetChildren
  For i = 0 To UBound(vChildComp)
    Set swChildComp = vChildComp(i)
    If Not swChildComp Is Nothing Then
       Dim path As String
       Dim curCount As Integer
       path = swChildComp.GetPathName
       If dicComps.Exists(path) Then
          curCount = dicComps.Item(path)
          dicComps.Item(path) = curCount + 1
          curCount = 0
          dicComps.Add swChildComp.GetPathName, curCount + 1
       End If
    End If
    TraverseComponent swChildComp
  Next i
End Sub

Sub PrintData()
  Dim keys As Variant
  Dim i As Integer
  keys = dicComps.keys
  For i = 0 To UBound(keys)
    Dim compName As String
    Dim vStrings As Variant
    vStrings = Split(keys(i), "\")
    compName = vStrings(UBound(vStrings))
    vStrings = Split(compName, ".")
    compName = vStrings(0)
    Debug.Print compName & " -> " & dicComps.Item(keys(i))
End Sub

Open up visual basic and click on View | Immediate window.
Add the references: - note that the version of Solidworks will be based on the version installed on the


Add Solidworks Constant type library, Solidworks Type Library, sldFuncFeat Type Library, and
Solidworks Extensibility Type library (note that if it does not show up in the list, you can click browse and
add it)
The final references should look like this

Testing the code thus far.

Save and close the form. Open up a window showing the visual basic code and show the Immediate
Window (View | Immediate Window)

Open another window showing the form in Form View.Click on the Read SolidWorks Assembly button
and open a pre-existing assembly.

Switch over to the visual basic code and the names of the parts in the assembly should appear in the
immediate window. The names of the children should be showing up in the immediate window as
Now we are going to add functionality to the visual basic to create records in the Products table for the
assembly and each component in the assembly. Then we will create parent child relations between the
assembly and the parts in the ParentChildRelations table. Lastly, we will show the assembly in the
ParentChildRelations form.

For this part, I will give you the lines of Visual Basic code, and you must figure out where to put them in
your program.

First, since we are going to be accessing the database directly using visual basic, we will need to
dimension a variable to represent the database. Make sure that the following line of code exists at the top
of the visual basic code – it should already be there from earlier work. This line creates a variable to
represent the database:

Dim dbs As Database

Second, we need to initialize the variable dbs with setting it to point to the current database using the
following line of code:

Set dbs = CurrentDb

This needs to be put somewhere in the subroutine ReadAssembly_Click before we try to write
anything to the database.

Third, we will create a record in the Products table corresponding to the assembly name with the
following line of code:

dbs.Execute "insert into Products (ProductName) VALUES ('" & docName & "');"

The function dbs.Execute will execute a line of SQL (Structured Query Language), which is the language
used to talk directly to databases. In this situation, we are using the insert into command. More
information about the insert into command is available at

This line of code needs to be put into the ReadAssembly_Click subroutine sometime after the
program has figured out a value for docName

Fourth, now that we have created a record with the information about the assembly, we need to get the
ID of that newly created record for later use when we add parent child relations. To do that, the following
two lines of code need to be added.

Dim ID As Long

ID = DLookup("[ProductID]", "Products", "[ProductName] LIKE """ & docName &
Fifth, we will need to pass this ID down to PrintData subrouting. Modify the call to the PrintData
subrourtine so it looks like this:

PrintData (ID)

Sixth, the declaration for the PrintData subroutine needs to be modified to accept the variable we are
passing it as follows:

Sub PrintData(ParentID As Long)

This way the ID for the parent will get passed to the PrintData subroutine.

Seventh, we need to create records the Products table for each of the components (children) in the
assembly using the following line of code.
dbs.Execute "insert into Products (ProductName) VALUES ('" & compName & "');"

Make sure this line of code is inside a loop that goes through all the components and make sure it is after
the line that assigns a value to compName.

Eighth, for each recorded created in the Products table, we need to get the ID of the newly created record
so that we can create relations between the parent and the children. The following lines of code
accomplish that.

Dim ID As Long

ID = DLookup("[ProductID]", "Products", "[ProductName] LIKE """ & compName &

Ninth, the ParentChildRelations table also stores information about the quantity of each component, so
we need to capture that information using that following lines of code.

Dim Quantity As Single

Quantity = dicComps.Item(keys(i))

Tenth, we need to create record in the ParentChildRelations table with the information about the parent,
the child, and the quantity using the following lines of code:

dbs.Execute "insert into ParentChildRelations (HufParent, HufChild,
Quantity) VALUES (" & ParentID & "," & ID & "," & Quantity & ");"

Eleventh, after we have looped through all the components, we can close out the connection to the
database using the following line:

Twelfth, the last thing we do now that all the information is stored in the database, is to show assembly in
our form using the following lines of code:

ParentComboBox = ID



Make sure you save your form as it will also save all the VB code

Now you can test this code by clicking on the Read SolidWorks Assembly button on the form.

To show that it is working, please demonstrate it to me. Also print out a screen shot of the form
with the assembly information contained, also print out your Visual Basic Code.

To top