Database for BOM
Database for BOM
(OP)
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
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






RE: Database for BOM
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
RE: Database for BOM
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 "Can not Find SldWorks.Application" & vbCrLf & _
"ErrNo: " & Err.Number & " ErrMsg: " & Err.Description _
, vbOKOnly, "Error in ExportBOM()"
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 "Can NOT find the BOM on the current drawing!"
GoTo CleanUp
End If
'Attach to the BOM
ret = swBOM.Attach2
If ret = False Then
MsgBox "Error Attaching to BOM"
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 "BOM Exported Successfully!"
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.