Sub UpdateWOTable()
'Open DB connection
Dim cnx As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strsql, SO, ItemGrp As String
Dim date_print As Date
Dim date_finish As Date
Dim date_proof As Date
Dim nb_poses, qty_proof, stock_proof, stock_pvc, qty_pvc, item_pvc As Integer
Set cnx = New ADODB.Connection
cnx.Provider = "Microsoft.Jet.Oledb.4.0"
'cnx.ConnectionString = "S:\Data Development\Local Tools\Scheduling\Databases\artwork offline queries.mdb"
cnx.ConnectionString = "S:\Data Development\Local Tools\Scheduling\Databases\PrintDB.mdb"
cnx.Open
'Update rows in Proofs Table with data from Proof worksheet
Sheets("Data").Select
Range("A1").Select
Dim LastRow As Integer
Range("A1").End(xlDown).Select
LastRow = ActiveCell.Row
Range("A1").Select
Range("A1").End(xlToRight).Select
ActiveCell.Offset(1, 1).Select
Dim i As Integer
i = ActiveCell.Column
'ActiveCell.Formula = "=IF(COUNTIF(AccessDB!A:A,A2)=0,""New"",IF(COUNTIF(AccessDB!A:A,A2)=1,""Update"",""""))"
'Cells(ActiveCell.Row, i).Select
'Selection.AutoFill Destination:=Range("AJ2:AJ" & LastRow), Type:=xlFillValues
Range("A2").Select
'Delete rows in Works Order Table
strsql = "DELETE * from tbl_WO"
rst.Open strsql, cnx
Do Until ActiveCell.Value = ""
If IsNull(ActiveCell.Offset(0, 9).Value) = True Then
date_print = DateValue("01/01/1900")
Else
date_print = ActiveCell.Offset(0, 9).Value
End If
If IsNull(ActiveCell.Offset(0, 16).Value) = True Then
date_finish = DateValue("01/01/1900")
Else
date_finish = ActiveCell.Offset(0, 16).Value
End If
If IsNull(ActiveCell.Offset(0, 18).Value) = True Then
date_proof = "12/01/1900"
Else
date_proof = ActiveCell.Offset(0, 18).Value
End If
If ActiveCell.Offset(0, 14).Value = "" Then
nb_poses = 0
Else
nb_poses = ActiveCell.Offset(0, 14).Value
End If
If ActiveCell.Offset(0, 11).Value = "" Then
item_pvc = 0
Else
item_pvc = ActiveCell.Offset(0, 14).Value
End If
If ActiveCell.Offset(0, 13).Value = "" Then
qty_pvc = 0
Else
qty_pvc = ActiveCell.Offset(0, 13).Value
End If
If ActiveCell.Offset(0, 15).Value = "" Then
stock_pvc = 0
Else
stock_pvc = ActiveCell.Offset(0, 15).Value
End If
If ActiveCell.Offset(0, 17).Value = "" Then
stock_proof = 0
Else
stock_proof = ActiveCell.Offset(0, 18).Value
End If
If ActiveCell.Offset(0, 19).Value = "" Then
qty_proof = 0
Else
qty_proof = ActiveCell.Offset(0, 19).Value
End If
'Insert Record into DB
strsql = "INSERT INTO tbl_WO (WO, Item, SN, Batch, Proof, Prod_Name, Sales, Item_group, Qty_sched, Date_print, Status, Item_PVC, PVC, Qty_PVC, Nb_poses, Stock_PVC, Date_finish, Stock_proof, Date_proof, Qty_conso_proof, ProdNotes, Unite)" & _
" VALUES ('" & ActiveCell.Value & "'," & ActiveCell.Offset(0, 1).Value & ", '" & ActiveCell.Offset(0, 2).Value & "','" & ActiveCell.Offset(0, 3).Value & "'" & _
"," & ActiveCell.Offset(0, 4).Value & ",'" & ActiveCell.Offset(0, 5).Value & "','" & ActiveCell.Offset(0, 6).Value & "','" & ActiveCell.Offset(0, 7).Value & "'" & _
"," & ActiveCell.Offset(0, 8).Value & ",#" & date_print & "#,'" & ActiveCell.Offset(0, 10).Value & "'," & item_pvc & "" & _
",'" & ActiveCell.Offset(0, 12).Value & "'," & qty_pvc & "," & nb_poses & "," & stock_pvc & "" & _
",#" & date_finish & "#," & stock_proof & ", #" & date_proof & "#," & qty_proof & ",'" & ActiveCell.Offset(0, 20).Value & "','" & ActiveCell.Offset(0, 21).Value & "')"
rst.Open strsql, cnx
ActiveCell.Offset(1, 0).Select
Loop
'Close Connections
Set rst = Nothing: Set cnx = Nothing
End Sub