I am at a point where I can save the Excel document and the Access table deletes records.
Still having difficulty having the table update new records from the excel sheet. Below is the code written for the update process. Please let me know if I am missing anything.**Please be aware I have changed table names and field names
'creation of SQL deletion string
strDeleteStmt = "DELETE FROM Table_" & Year & " WHERE Owner = " & "'" & Owner & "'"
'MsgBox (strDeleteStmt)
'run SQL delete statement
Cnn.Execute (strDeleteStmt)
'loops through table length
For c = 1 To .ListRows.Count
project = .DataBodyRange.Columns(1).Rows(c).Value 'assign project value from each table row
Workbook = .DataBodyRange.Columns(2).Rows(c).Value 'assign workbook value from each table row
name = .DataBodyRange.Columns(3).Rows(c).Value 'assign name value from each table row
correction = .DataBodyRange.Columns(4).Rows(c).Value 'assign correction value from each table row
If project = "" And Len(Workbook) <> 0 And Len(name) <> 0 Then
.DataBodyRange.Columns(1).Rows(c).Value = "Project Not Specified" 'assign project value to "Project Not Specified" if row value is blank
project = .DataBodyRange.Columns(1).Rows(c).Value
.DataBodyRange.Columns(1).Rows(c).Font.Color = RGB(255, 0, 0)
End If
If correction = "" And Len(project) <> 0 And Len(BudgetWorkbook) <> 0 And Len(name) <> 0 Then
.DataBodyRange.Columns(4).Rows(c).Value = "N"
correction = .DataBodyRange.Columns(4).Rows(c).Value
End If
January = .DataBodyRange.Columns(5).Rows(c).Value 'assign january value from each row
February = .DataBodyRange.Columns(6).Rows(c).Value 'assign february value from each row
March = .DataBodyRange.Columns(7).Rows(c).Value 'assign march value from each row
April = .DataBodyRange.Columns(8).Rows(c).Value 'assign april value from each row
may = .DataBodyRange.Columns(9).Rows(c).Value 'assign may value from each row
June = .DataBodyRange.Columns(10).Rows(c).Value 'assign june value from each row
July = .DataBodyRange.Columns(11).Rows(c).Value 'assign july value from each row
August = .DataBodyRange.Columns(12).Rows(c).Value 'assign august value from each row
September = .DataBodyRange.Columns(13).Rows(c).Value 'assign september value from each row
October = .DataBodyRange.Columns(14).Rows(c).Value 'assign october value from each row
November = .DataBodyRange.Columns(15).Rows(c).Value 'assign november value from each row
December = .DataBodyRange.Columns(16).Rows(c).Value 'assign december value from each row
'SQL Insert statement creation
strInsertStmt = "INSERT INTO TABLE_" & Year & "(Owner,Project,Workbook,Name,"
strInsertStmt = strInsertStmt + "Correction,January,February,March,April,May,June, July,August,September,October,November,December,Up datedBy,UpdatedDateTime)"
strInsertStmt = strInsertStmt + "VALUES('" & Owner & "','" & project & "','" & Workbook
strInsertStmt = strInsertStmt + "','" & name & "','" & correction & "','" & January
strInsertStmt = strInsertStmt + "','" & February & "','" & March & "','" & April & "','" & may
strInsertStmt = strInsertStmt + "','" & June & "','" & July & "','" & August & "','" & September
strInsertStmt = strInsertStmt + "','" & October & "','" & November & "','" & December & "','" & UserName & "','" & UpdatedDateTime & "')"
'call BlankEntryCheck
BlankEntryCheck project, Workbook, name, correction, January, February, March, April, may, June, July, August, September, October, November, December, blankcheck
' If duplicateCheck = True Or blankcheck = False Then
If blankcheck = False Then
Cnn.Execute (strInsertStmt)
' Else
' MsgBox "Exiting Insert"
' Exit Sub
End If
Next c
End With
'close database connection
Call CloseConnection
I appreciate any insight or direction, this has been a huge painpoint!