Hi.
I currently have a VBA Script that will create new records within a SQL table directly from excel.
What I was hoping if there was a way to modify the below so it can change from a create to a modify/update of exisitng records in the SQL table.
The unique key will be PP_REFERNCE in all cases
So for example what the front end user will do is populate the PP_REFERENCE ID in cell A2 and then update the cells that they want changed in the SQL table.
PP_REFERENCE DIRECTOR_NAME DIRECTOR_STATUS
1234 DAVE APPROVED
4567 MARK PENDING
So what ever was in DIRECTOR_NAME before will now be replaced by the above from excel in the existing SQL table against the PP_REFERENCE
Code
Sub Button1_Click()
Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim sPP_REFERENCE, sDATE_RECEIVED, sLOC_ID, sROLLOUT_REGION, sDISCONNECTION_DATE, sADDRESS, sADBOR, sBSA_STATUS, sSERVICE_CLASS, sRECONNECTION_REASON, sPRODUCT_TYPE, sDIRECTOR_NAME, sDIRECTOR_STATUS, sNBN_TRANS_FNN, sNBN_TRANS_DATE, sRECONNECTED_PAIR, sOLD_PATH_ID, sNEW_PATH_ID, sTLS_ID, sCOMPLETION_DATE, sCOMMENTS, sASSET_TRANSFERRED, sSTATUS As String
With Sheets("Sheet1")
'Open a connection to SQL Server
conn.Open "Provider=SQLOLEDB;Data Source=W00000PC0U35U3\SQLEXPRESS;Initial Catalog=ROC;Integrated Security=SSPI;"
'Skip the header row
iRowNo = 2
'Loop until empty cell in PP_REFERENCE
Do Until .Cells(iRowNo, 1) = ""
sPP_REFERENCE = .Cells(iRowNo, 1)
sDATE_RECEIVED = .Cells(iRowNo, 2)
sLOC_ID = .Cells(iRowNo, 3)
sROLLOUT_REGION = .Cells(iRowNo, 4)
sDISCONNECTION_DATE = .Cells(iRowNo, 5)
sADDRESS = .Cells(iRowNo, 6)
sADBOR = .Cells(iRowNo, 7)
sBSA_STATUS = .Cells(iRowNo, 8)
sSERVICE_CLASS = .Cells(iRowNo, 9)
sRECONNECTION_REASON = .Cells(iRowNo, 10)
sPRODUCT_TYPE = .Cells(iRowNo, 11)
sDIRECTOR_NAME = .Cells(iRowNo, 12)
sDIRECTOR_STATUS = .Cells(iRowNo, 13)
sNBN_TRANS_FNN = .Cells(iRowNo, 14)
sNBN_TRANS_DATE = .Cells(iRowNo, 15)
sRECONNECTED_PAIR = .Cells(iRowNo, 16)
sOLD_PATH_ID = .Cells(iRowNo, 17)
sNEW_PATH_ID = .Cells(iRowNo, 18)
sTLS_ID = .Cells(iRowNo, 19)
sCOMPLETION_DATE = .Cells(iRowNo, 20)
sCOMMENTS = .Cells(iRowNo, 21)
sASSET_TRANSFERRED = .Cells(iRowNo, 22)
sSTATUS = .Cells(iRowNo, 23)
'Generate and execute sql statement to import the excel rows to SQL Server table
conn.Execute "insert into dbo.ROCS (PP_REFERENCE, DATE_RECEIVED, LOC_ID, ROLLOUT_REGION, DISCONNECTION_DATE, ADDRESS, ADBOR, BSA_STATUS, SERVICE_CLASS, RECONNECTION_REASON, PRODUCT_TYPE, DIRECTOR_NAME, DIRECTOR_STATUS, NBN_TRANS_FNN, NBN_TRANS_DATE, RECONNECTED_PAIR, OLD_PATH_ID, NEW_PATH_ID, TLS_ID, COMPLETION_DATE, COMMENTS, ASSET_TRANSFERRED, STATUS) values ('" & sPEGA_REFERENCE & "', '" & sDATE_RECEIVED & "', '" & sLOC_ID & "', '" & sROLLOUT_REGION & "', '" & sDISCONNECTION_DATE & "', '" & sADDRESS & "', '" & sADBOR & "', '" & sBSA_STATUS & "', '" & sSERVICE_CLASS & "', '" & sRECONNECTION_REASON & "', '" & sPRODUCT_TYPE & "', '" & sDIRECTOR_NAME & "', '" & sDIRECTOR_STATUS & "', '" & sNBN_TRANS_FNN & "', '" & sNBN_TRANS_DATE & "', '" & sRECONNECTED_PAIR & "', '" & sOLD_PATH_ID & "', '" & sNEW_PATH_ID & "', '" & sTLS_ID & "', '" & sCOMPLETION_DATE & "', '" & sCOMMENTS & "', '" & sASSET_TRANSFERRED & "', '" & sSTATUS & "')"
iRowNo = iRowNo + 1
Loop
MsgBox "PPS imported."
conn.Close
Set conn = Nothing
End With
End Sub