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

Code: Select all

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

[HansV]

Welcome to Eileen's Lounge!

I'll work on it, but it might take a while.

[HansV]

Try this code:

Code: Select all

Sub Button1_Click()
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sPP_REFERENCE As String
    Dim sDATE_RECEIVED As String
    Dim sLOC_ID As String
    Dim sROLLOUT_REGION As String
    Dim sDISCONNECTION_DATE As String
    Dim sADDRESS As String
    Dim sADBOR As String
    Dim sBSA_STATUS As String
    Dim sSERVICE_CLASS As String
    Dim sRECONNECTION_REASON As String
    Dim sPRODUCT_TYPE As String
    Dim sDIRECTOR_NAME As String
    Dim sDIRECTOR_STATUS As String
    Dim sNBN_TRANS_FNN As String
    Dim sNBN_TRANS_DATE As String
    Dim sRECONNECTED_PAIR As String
    Dim sOLD_PATH_ID As String
    Dim sNEW_PATH_ID As String
    Dim sTLS_ID As String
    Dim sCOMPLETION_DATE As String
    Dim sCOMMENTS As String
    Dim sASSET_TRANSFERRED As String
    Dim 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 "UPDATE dbo.ROCS 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 & _
                "', DIRECTOR_STATUS='" & sDIRECTOR_STATUS & "', 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='" & sPP_REFERENCE & "'"
            iRowNo = iRowNo + 1
        Loop

        MsgBox "PPS imported."

        conn.Close
        Set conn = Nothing
    End With
End Sub