VBA Update from Excel to SQL Table Contents.

[troy79]

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

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

 

[Fluff]

Cross posted VBA Update from Excel to SQL Table Contents.

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[RoryA]

Also posted on just about every other Excel forum out there.