'CHARACTERISTIC
'Firstly, check to see if the characteristic already exists, a characteristic being
'a matching Charname and PartId combo within the Characteristic table.
dbAdd = True
charname(x) = Replace(charname(x), "&", "and")
charname(x) = Replace(charname(x), "#", "")
charname(x) = Replace(charname(x), Chr$(34), "")
sourcesqlstatement = "Select CharId, PartId, CharName from Characteristic where PartId = " & partid & " And CharName = '" & charname(x) & "'"
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
dbAdd = False
vcharid = rst.Fields("CharId")
Exit Do
rst.MoveNext
Loop
End If
rst.Close
If dbAdd Then 'If the characteristic does not exist in the table, then
' add a new record to the characteristic table
sql = "INSERT INTO Characteristic (PartId, CharName, LastEditDate, Target, USL, LSL, CharDataId, SubgroupSize, DecimalPlaces, ChartType, ShowHistogram, ShowLSL, ShowUSL, ShowTarget, ShowControlChart, IsRainbow, ValidateEntry, CurveFit, ValidateUpper, ValidateLower, ValidateRatio, ValidateSampleSize, BatchSize, BatchType, PlotLastCount, PlotLastType) VALUES (" & partid & ", '" & charname(x) & "', '" & Now & "', '" & target(x) & "', '" & USL(x) & "', '" & LSL(x) & "',0, '" & subgroupsize & "', 6, 3, 0, 1, 1, 1, 1, -1, 1, 1, 2.225074E-308, 2.225074E-308, 2.225074E-308, 0, 0, 0, 0, 0)"
cnn.Execute sql
Else 'If the characteristic does exist in the table, then
'just update the target, USL, and LSL of the existing record
sql = "UPDATE Characteristic SET Target = " & target(x) & ", " & "USL = " & USL(x) & ", " & "LSL = " & LSL(x) & " where CharId = " & vcharid
cnn.Execute sql
DoEvents
End If
dbAdd = True
'TRACE CODES
'Firstly, open TraceCode table and check to ensure that no
'duplicate TraceCodeName and TraceId combos are inserted for each tracevalue(t, x)
For t = 1 To tracecount
whileflag = 1
dbAdd = True
If whileflag = 1 Then
sourcesqlstatement = "Select TraceId, TraceCodeName from TraceCodes where TraceCodeName <> ''"
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
Value = rst.Fields("TraceCodeName")
tid = rst.Fields("TraceId")
If tid = traceid(t) And (Trim(UCase(Value)) = Trim(UCase(operator(x))) Or Trim(UCase(Value)) = Trim(UCase(serialnumber(x))) Or Trim(UCase(Value)) = Trim(UCase(trial(x))) Or Trim(UCase(Value)) = Trim(UCase(cam(x))) Or Trim(UCase(Value)) = Trim(UCase(check(x))) Or Trim(UCase(Value)) = Trim(UCase(grinder(x)))) Then
dbAdd = False
Exit Do
End If
rst.MoveNext
Loop
rst.Close
End If
Select Case t
Case 1
'insert OPERATOR
tracevalue(t, x) = operator(x)
If Len(operator(x)) > 0 And dbAdd = True Then
sql = "INSERT INTO TraceCodes (TraceId, TraceCodeName, ActiveStatus) VALUES ('" & traceid(t) & "', '" & operator(x) & "', 0)"
cnn.Execute sql
End If
Case 2
tracevalue(t, x) = serialnumber(x)
'insert SERIALNUMBER
If Len(serialnumber(x)) > 0 And dbAdd = True Then
sql = "INSERT INTO TraceCodes (TraceId, TraceCodeName, ActiveStatus) VALUES ('" & traceid(t) & "', '" & serialnumber(x) & "', 0)"
cnn.Execute sql
End If
Case 3
tracevalue(t, x) = trial(x)
'insert TRIAL
If Len(trial(x)) > 0 And dbAdd = True Then
sql = "INSERT INTO TraceCodes (TraceId, TraceCodeName, ActiveStatus) VALUES ('" & traceid(t) & "', '" & trial(x) & "', 0)"
cnn.Execute sql
End If
Case 4
tracevalue(t, x) = cam(x)
'insert CAM
If Len(cam(x)) > 0 And dbAdd = True Then
sql = "INSERT INTO TraceCodes (TraceId, TraceCodeName, ActiveStatus) VALUES ('" & traceid(t) & "', '" & cam(x) & "', 0)"
cnn.Execute sql
End If
Case 5
tracevalue(t, x) = check(x)
'insert CHECK
If Len(check(x)) > 0 And dbAdd = True Then
sql = "INSERT INTO TraceCodes (TraceId, TraceCodeName, ActiveStatus) VALUES ('" & traceid(t) & "', '" & check(x) & "', 0)"
cnn.Execute sql
End If
Case 6
tracevalue(t, x) = grinder(x)
'insert GRINDER
If Len(grinder(x)) > 0 And dbAdd = True Then
sql = "INSERT INTO TraceCodes (TraceId, TraceCodeName, ActiveStatus) VALUES ('" & traceid(t) & "', '" & grinder(x) & "', 0)"
cnn.Execute sql
End If
End Select
End If
DoEvents
Next t
'DATA
'First, get the last DataId in the present Data table, to later determine where the new
'data was inserted in the table. It will serve as
'an indication of where to start inserting the SubgroupLock values.
'The last DataId value will be asssigned to the variable lastDataId. This will assist in
'determining the SubgroupLockNumber and SubroupItem number.
sourcesqlstatement = "Select DataId From Data"
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic
If rst.RecordCount > 0 Then
rst.MoveLast
Do While Not rst.EOF
lastDataId = rst.Fields("DataId")
Exit Do
Loop
Else
lastDataId = 1 'jrm 5/15/2003
End If
rst.Close
'Secondly, obtain the proper partid
sourcesqlstatement = "Select PartId From Part Where PartName = '" & temppartname & "'"
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic
rst.MoveFirst
dbAdd = True
Do While Not rst.EOF
partid = rst.Fields("PartId")
rst.MoveNext
Loop
rst.Close
'Thirdly, obtain the CharId and the SubgroupSize, based on CharName and the PartId
sourcesqlstatement = "Select CharId, SubgroupSize From Characteristic Where PartId = " & partid & " And CharName = '" & charname(x) & "'"
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic
If rst.RecordCount > 0 Then
rst.MoveFirst
dbAdd = True
Do While Not rst.EOF
Charid = rst.Fields("CharId")
subgroupsize = rst.Fields("SubgroupSize")
'subgroupItemNumber = n Mod subgroupsize
rst.MoveNext
Loop
End If
rst.Close
'Fourthly, insert the data into the database
ddatetime(x) = Replace(ddatetime(x), Chr$(34), "")
sql1 = "INSERT INTO Data (CharId, [Value], [DateTime]"
sql2 = ") VALUES (" & Charid & ", " & dvalue(x) & ", '" & ddatetime(x) & "'"
'add necessary trace info to the insert query
For Y = 1 To tracecount
temptracevalueY = Replace(tracevalue(Y, x), "&", "and")
temptracevalueY = Replace(temptracevalueY, Chr$(34), "")
temptracevalueY = Replace(temptracevalueY, "#", "")
sql1 = sql1 & ", Trace" & Y & ", TraceId" & Y
sql2 = sql2 & ", '" & temptracevalueY & "', " & traceid(Y)
Next Y
sql2 = sql2 & ")"
sql = sql1 & sql2
cnn.Execute sql
'Fifthly, open the newest data record that contained the same CharId, and insert the appropriate Subgroup Item number, as well as the appropriate SubgroupLock value
sourcesqlstatement = "Select DataId, CharId, SubgroupItem, SubgroupLock From Data Where CharId = " & Charid & " And DataId > " & lastDataId & " ORDER BY DataId"
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic
SubgroupItem = 0
rc = rst.RecordCount
If rc > 0 Then
rst.MoveFirst
rst.MoveLast
Do While Not rst.EOF
rst!SubgroupLock = rst!DataId
rst!SubgroupItem = 0
Exit Do
Loop
rst.MoveFirst
rst.Close
End If
'display to the user where the program is at
pbimport.Min = 0
pbimport.Max = fc
DoEvents
Label5.Caption = "Total number of directories being imported: " & dircount & "; Presently on directory: " & presentdir & ";" & Chr(13) & "Total number of files in present directory: " & fc & "; Presently on file: " & partcount & ";" & Chr(13) & "Present directory: " & directory & ";" & Chr(13) & "Present file name: " & file(UBound(file)) & ";" & Chr(13) & "Present part name: " & temppartname & ";" & Chr(13) & "Number of data items in present file: " & xcount & " Presently on data item: " & x & "."
pbimport.Value = partcount
Next x
cnn.Close
Exit Sub
End Sub