VBA Update from Excel to SQL Table Contents.

[troyw]

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 PEGA_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

[Bob Phillips]

It would probably look something like


Sub Button1_Click()
Dim conn As New ADODB.Connection
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
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)

Update dbo.ROCS
conn.Execute "Set DATE_RECEIVED = " & sDATE_RECEIVED & _
" ,LOC_ID = " & sLOC_ID & _
" ,ROLLOUT_REGION = " & sROLLOUT_REGION & _
" ,DISCONNECTION_DATE = " & sDISCONNECTION_DATE & _
" ,ADDRESS = " & sADDRESS & _
" ,ADBOR = " & sADBOR & _
" ,BSA_STATUS = " & sBSA_STATUS & _
" ,SERVICE_CLASS = " & sSERVICE_CLASS & _
" ,RECONNECTION_REASON = " & sRECONNECTION_REASON & _
" ,PRODUCT_TYPE = " & sPRODUCT_TYPE & _
" ,DIRECTOR_NAME = " & sDIRECTOR_NAME & _
" ,NBN_TRANS_FNN = " & sNBN_TRANS_FNN & _
" ,NBN_TRANS_DATE = " & sNBN_TRANS_DATE & _
" ,RECONNECTED_PAIR = " & sRECONNECTED_PAIR & _
" ,OLD_PATH_ID = " & sOLD_PATH_ID & _
" ,NEW_PATH_ID = " & sNEW_PATH_ID & _
" ,TLS_ID = " & sTLS_ID & _
" ,COMPLETION_DATE = " & sCOMPLETION_DATE & _
" ,COMMENTS = " & sCOMMENTS & _
" ,ASSET_TRANSFERRED = " & sASSET_TRANSFERRED & _
" ,STATUS = " & sSTATUS & _
" WHERE PP_REFERENCE = " & .Range("A2").Value

MsgBox "PPS updated."

conn.Close
Set conn = Nothing
End With
End Sub

[snb]

I'd suggest to use VBA:


sub M_snb()
sn = sheet1.columns(1).specialcells(4).cells(1).resize(,23)
End Sub

[paulked]

@ troyw

Just in case you didn't know! In the Dim line for your strings, only the last one is defined as a String, the rest are Variants eg in



Dim a, b, c As Integer
Dim sA, sB, sC As String


a, b, sA & sB are defined as Variant, c as Integer and sC as String. Although not necessary, if you want to define them 'properly' you should use



Dim a As Integer, b As Integer, c As Integer
Dim sA As String, sB As String, sC As String