Database for BOM 4

[dogarila]

I would like to write a VB program that will read a BOM from assembly drawings and will transfer the information into a database.

The assembly drawings have manufactured parts and purchased parts. Some of the manufactured parts are common to more than one assembly but they have the same drawing/part number.

If anyone has already worked on something similar please give me some hints to start into the right direction. I have some ideeas but maybe there is something that I am missing.

1. Is it better to start the application outside SW or from SW?

2. How do you think shold I organize the database? One huge table? Several smaller tables? One table per assy?

3. To keep track of the common manufatured parts is it a good ideea to have an index that combine the part number field with the assembly number field?

4. Any particular problems that I should by aware of?

Thank you

Andrew

 

[redwoodly]

hi -

I'm working on something close.
We are creating our parts lists in excel and my database reads in the parts lists to put all the data in the db and determines the 'where used', etc. and tracks all changes to - thus the db gets into tracking parts and revisions, etc. I develop mostly in ACCESS.

as to one big table - no, suggest you don't do that.
you might want to bone up on database design and the 3 first rules of normalization. If you don't want to get that deep into db design - try and keep each table to one "subject" matter - e.g., PARTS TABLE, EMPLOYEE TABLE, VENDOR TABLE. index each with a unique ID.
The key to db design - is you do NOT want to repeat information in a database - i.e., if I change the VENDOR name in one place - it should change it EVERYWHERE - so you store the name once (in the VENDOR TABLE) and use the index IDs in all the other tables (such as PURCHASE ORDERS...you'd store the applicable index number for the vendor there). does that make sense?

One of the best ways to determine the db rqmts is to figure out what OUTPUTS you want from the db - what is it that you're going to want this db to provide for you?
a REPORT of ......? and that will tell you what data you need to capture.

I have two places I've found on ideas:

one db that already does what you're working on: called PARTS & VENDORS (they have a sample download) at

http://www.trilogydesign.com/

And THE ACCESS WEB - has some sample BOM code,etc:

http://www.mvps.org/access/modules/mdl0027.htm

and queries for BOM:

http://www.mvps.org/access/queries/qry0023.htm

good luck!
laurel

 

[dsi]

Here is some sample code to extract the info from the BOM in a drawing document. This was customized for certain columns, but can be easily tweaked to accomodate your needs. Once the array is full, do what you wish with the data.

Option Explicit
Option Base 1

Dim swApp As Object
Dim swPart As Object
Dim swView As Object
Dim swBOM As Object

Public Type BOM_Data
    Rev As String
    Item As String
    Qty As String
    Desc As String
    Wgt As String
    Matl As String
    Spec1 As String
    Spec2 As String
    PartNo As String
End Type
Public LineItem() As BOM_Data

Sub ReadBOM()
    Dim ret As Variant
    Dim i As Integer
    iItems As Integer
    
    'Attach to SolidWorks
    On Error Resume Next
    Set swApp = GetObject(, "SldWorks.Application")
    If Err.Number <> 0 Then
        MsgBox &quot;Can not Find SldWorks.Application&quot; & vbCrLf & _
               &quot;ErrNo: &quot; & Err.Number & &quot;  ErrMsg: &quot; & Err.Description _
               , vbOKOnly, &quot;Error in ExportBOM()&quot;
        Err.Clear
        Exit Sub
    End If
    
    'Get Document
    Set swPart = swApp.ActiveDoc
    
    'Get Drawing Template (first view)
    Set swView = swPart.GetFirstView
    
    'Get the BOM
    Set swBOM = swView.GetBomTable
    
    'Find the BOM - must find the view that contains the BOM
    Do While swBOM Is Nothing And Not swView Is Nothing
        Set swView = swView.GetNextView
        Set swBOM = swView.GetBomTable
    Loop
    If swBOM Is Nothing Then
        Screen.MousePointer = vbDefault
        MsgBox &quot;Can NOT find the BOM on the current drawing!&quot;
        GoTo CleanUp
    End If
    
    'Attach to the BOM
    ret = swBOM.Attach2
    If ret = False Then
        MsgBox &quot;Error Attaching to BOM&quot;
        Exit Sub
    End If
    
    'Put the BOM table in an array
    iItems = swBOM.GetRowCount - 1
    ReDim LineItem(iItems)
    For i = 1 To iItems
        LineItem(i).Rev = swBOM.GetEntryText(i, 0)
        LineItem(i).Item = swBOM.GetEntryText(i, 1)
        LineItem(i).Qty = swBOM.GetEntryText(i, 2)
        LineItem(i).Desc = swBOM.GetEntryText(i, 3)
        LineItem(i).Wgt = swBOM.GetEntryText(i, 4)
        LineItem(i).Matl = swBOM.GetEntryText(i, 5)
        LineItem(i).Spec1 = swBOM.GetEntryText(i, 6)
        LineItem(i).Spec2 = swBOM.GetEntryText(i, 7)
        LineItem(i).PartNo = swBOM.GetEntryText(i, 8)
    Next i
    'Detach from the BOM
    swBOM.Detach
    
    MsgBox &quot;BOM Exported Successfully!&quot;
CleanUp:
    Set swApp = Nothing
    Set swPart = Nothing
    Set swView = Nothing
    Set swBOM = Nothing
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.