1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
|
Private Sub ImpWonos(bkfldr As Folder, oldbkfldr As Folder)
'*******************************************************************
'*
'* IMPORT ALL DROPPED WONO'S
'*
'*******************************************************************
Dim sql As String
Dim wp1 As Workspace
Set wp1 = DBEngine.Workspaces(0)
'On Error GoTo DeleteDups
reImport:
'******Delete all records for tempworf before inserting new backup's records******
wp1.BeginTrans
sql = "Delete * from tempworf"
CurrentDb.Execute sql
wp1.CommitTrans (dbForceOSFlush)
'******Insert new backup's records for processing*********
wp1.BeginTrans
sql = "INSERT INTO tempworf " _
& "SELECT [AHN03I#DAT].* " _
& "FROM [Text;fmt=FIXED;DATABASE=" & bkfldr.Path & "\;].[AHN03I#DAT]"
CurrentDb.Execute sql
'wp1.CommitTrans
'******Update open worf table with data from new backup*******
'wp1.BeginTrans
wp1.CommitTrans
wp1.BeginTrans
sql = "UPDATE OpenWorf AS a INNER JOIN tempworf AS b ON (a.Wono = b.Wono) AND (a.ISCd = b.ISCd) AND (a.MaintActvDsg = b.MaintActvDsg) AND (a.SvcDsgUIC = b.SvcDsgUIC) SET a.EquipSNLCNFld = b.EquipSNLCNFld, a.ItemNomenItemNounFld = b.ItemNomenItemNounFld, a.WpnEquipSysDsgCd = b.WpnEquipSysDsgCd, a.EndItemCd = b.EndItemCd, a.CondDsgWrnt = b.CondDsgWrnt, a.ORFTrnsCd = b.ORFTrnsCd, a.PD = b.PD, a.DateAcptOrd = b.DateAcptOrd, a.DateComplOrd = b.DateComplOrd, a.WrStaCdCompl = b.WrStaCdCompl, a.WrStaCd = b.WrStaCd, a.OrdDate = b.OrdDate, a.MilTimeDa = b.MilTimeDa, a.WONBMA = b.WONBMA, a.QntyToBeRpr = b.QntyToBeRpr, a.QntyNRTS = b.QntyNRTS, a.QntyRpr = b.QntyRpr, a.QtyCondem = b.QtyCondem, a.MHProjTen = b.MHProjTen, a.MHExpTen = b.MHExpTen, a.MHRmnTen = b.MHRmnTen, a.EquipUtlCd = b.EquipUtlCd, a.ProjCd = b.ProjCd, a.APC = b.APC, a.CondDsgSDCIndic = b.CondDsgSDCIndic, a.FailDetcDuringCd = b.FailDetcDuringCd, a.MalfuncDescr = b.MalfuncDescr, a.MaintRprCd = b.MaintRprCd, a.CondDsgSNT = b.CondDsgSNT, " _
& "a.EquipUseMeasCd1 = b.EquipUseMeasCd1 , a.UseAtSbmWR1 = b.UseAtSbmWR1, a.EquipUseMeasCd2 = b.EquipUseMeasCd2, a.UseAtSbmWR2 = b.UseAtSbmWR2, a.EquipUseMeasCd3 = b.EquipUseMeasCd3, a.UseAtSbmWR3 = b.UseAtSbmWR3, a.MaintSCDSvcDateOrd = b.MaintSCDSvcDateOrd, a.ShopSecCd = b.ShopSecCd, a.WONOrg = b.WONOrg, a.FILLER1 = b.FILLER1, a.RqrMaintCd = b.RqrMaintCd, a.EquipNo = b.EquipNo, a.EIId = b.EIId, a.EIPartNoFld = b.EIPartNoFld, a.EIEquipSNLCN = b.EIEquipSNLCN, a.MtrlDspoCd = b.MtrlDspoCd, a.ECC = b.ECC, a.CondDsgIsWO = b.CondDsgIsWO, a.MHExpTenCiv = b.MHExpTenCiv, a.MHExpTenKr = b.MHExpTenKr, a.MHExpTenLn = b.MHExpTenLn, a.MHExpTenMil = b.MHExpTenMil, a.DLbrCostCiv = b.DLbrCostCiv, a.DLbrCostKr = b.DLbrCostKr, a.DLbrCostLn = b.DLbrCostLn, a.DLbrCostMil = b.DLbrCostLn, a.IndLbrCost = b.IndLbrCost, a.TotEstRprPartCost = b.TotEstRprPartCost, a.CondDsgORF = b.CondDsgORF, a.DateEstWOComplOrd = b.DateEstWOComplOrd, a.DefLbrRateInd = b.DefLbrRateInd, a.CondDsgTransferable = b.CondDsgTransferable, " _
& "a.CondDsgWOAvgCalc = b.CondDsgWOAvgCalc, a.WONBMA2 = B.WONBMA2, a.FILLER2 = B.FILLER2, a.STATUSDATA1 = B.STATUSDATA1, a.STATUSDATA2 = B.STATUSDATA2, a.STATUSDATA3 = B.STATUSDATA3, a.STATUSDATA4 = B.STATUSDATA4, a.STATUSDATA5 = B.STATUSDATA5, a.STATUSDATA6 = B.STATUSDATA6, a.STATUSDATA7 = B.STATUSDATA7, a.STATUSDATA8 = B.STATUSDATA8, a.STATUSDATA9 = B.STATUSDATA9, a.STATUSDATA10 = B.STATUSDATA10, a.STATUSDATA11 = B.STATUSDATA11, a.STATUSDATA12 = B.STATUSDATA12, a.STATUSDATA13 = B.STATUSDATA13, a.STATUSDATA14 = B.STATUSDATA14, a.STATUSDATA15 = B.STATUSDATA15, a.STATUSDATA16 = B.STATUSDATA16, a.STATUSDATA17 = B.STATUSDATA17, a.STATUSDATA18 = B.STATUSDATA18, a.STATUSDATA19 = B.STATUSDATA19, a.STATUSDATA20 = B.STATUSDATA20"
CurrentDb.Execute sql
sql = "INSERT INTO OpenWorf " _
& "SELECT a.* " _
& "FROM tempworf a LEFT JOIN OpenWorf AS b ON (a.Wono = b.Wono) AND (a.ISCd = b.ISCd) AND (a.MaintActvDsg = b.MaintActvDsg) " _
& "WHERE ((b.Wono Is Null))"
CurrentDb.Execute sql
'*******Add new items from the backup to the open worf table
'wp1.CommitTrans
'wp1.BeginTrans
sql = "INSERT INTO worf " _
& "SELECT a.*, '" & Right(bkfldr.Name, 10) & "' AS transferdate " _
& "FROM openworf a " _
& "LEFT JOIN tempworf b ON a.MaintActvDsg = b.MaintActvDsg AND a.ISCd = b.ISCd AND a.Wono = b.Wono " _
& "WHERE (b.Wono IS NULL)"
CurrentDb.Execute sql, dbFailOnError
sql = "DELETE a.* " _
& "FROM Worf AS b INNER JOIN OpenWorf AS a ON (b.Wono = a.Wono) AND (b.ISCd = a.ISCd) AND (b.MaintActvDsg = a.MaintActvDsg) AND (b.SvcDsgUIC = a.SvcDsgUIC)"
CurrentDb.Execute sql
wp1.CommitTrans
Exit Sub
DeleteDups:
If Err.Number = 3022 Then
CurrentDb.Execute "delete a.* from worf a inner join [Text;fmt=FIXED;DATABASE=" & bkfldr.Path & "\;].[AHN03I#DAT] b on a.maintactvdsg = b.maintactvdsg and a.iscd = b.iscd and a.wono = b.wono"
'GoTo reImport
sql = "INSERT INTO worf " _
& "SELECT a.*, '" & Right(bkfldr.Name, 10) & "' AS transferdate " _
& "FROM [Text;fmt=FIXED;DATABASE=" & oldbkfldr.Path & "\;].[AHN03I#DAT] a " _
& "LEFT JOIN [Text;fmt=FIXED;DATABASE=" & bkfldr.Path & "\;].[AHN03I#DAT] b ON a.MaintActvDsg = b.MaintActvDsg AND a.ISCd = b.ISCd AND a.Wono = b.Wono " _
& "WHERE (b.Wono IS NULL)"
Resume
ElseIf Err.Number = 3058 Then
Debug.Print
' CurrentDb.Execute DCount("WONO", "[Text;fmt=FIXED;DATABASE=" & bkfldr.Path & "\;].[AHN03I#DAT]")
Else
MsgBox "ERROR - " & Err.Number & " " & Err.Description & vbCrLf & BoxFldr & " " & bkfldr & " enditem", vbCritical
End If
End Sub
|
