INSERT INTO statement from Excel to SQL Server Table using VBA

Question

The code below is meant to simply add rows of information from an Excel sheet to a table in SQL Server. It is tediously written where it goes row by row and cell by cell. It runs with no error pop-ups, but when I check the table for my data, there is nothing there.

I have been querying in SQL to check by using a WHERE clause and the 'Analyst' field should match my name. Nothing pops up.

Why is my data not showing up in the table? AND I am open to hear anyone's suggestions on better ways to do this. Thanks!

Public Sub ConnectToDB()
    Dim DBCONT As Object
    Dim strConn As String
    Dim Server_Name As String
    Dim Database_Name As String
    Dim Table_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim strSQL As String
    Dim rs As Object
    Dim Fields As String
    Dim LastRowAudit As Long
    Dim i As Long

    Dim sAuditType, sClaimeReceivedDate, sDateAssigned, sDateCompleted, sAnalyst, sCustomer, sID, sAffiliate, sFacility, sDEA, sAcctNumber, sWholesaler, sVendor, sProduct, sNDC, sRef, sClaimedContract, sClaimedContractCost, sContractPriceStartDate, sContractPriceEndDate, sCatalogNumber, sInvoiceNumber, sInvoiceDate, sChargebackID, sContractIndicator, sUnitCost, sWAC, sPotentialCreditDue, sQTY, sSpend, sIpDshIndicator, sDSHorHRSANumber, sUniqueGPOCode, sComment, sResCode, sCorrectCost, sCRRBCM, sCRRBRebill, sCRRBDate As String

'   SET ALL VARIABLES
    Server_Name = "I have this in my actual code" ' Enter your server name here
    Database_Name = "I have this in my actual code" ' Enter your database name here
    Table_Name = "I have this in my actual code"
    User_ID = "I have this in my actual code" ' enter your user ID here
    Password = "I have this in my actual code" ' Enter your password here


    WkbName = ThisWorkbook.Name
    SheetName = "Audit Data" '  WHERE RS IS


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'   SET SQL STRING

    strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
    " ([Audit],[Audit Type],[Claim Received Date],[Date Assigned],[Date Completed]," & _
    " [Analyst],[Customer],[ID],[Affiliate],[Facility],[DEA],[Acct Number],[Wholesaler],[Vendor],[Product],[NDC],[Ref],[Claimed Contract]," & _
    " [Claimed Contract Cost],[Contract Price Start Date],[Contract Price End Date],[Catalog Number],[Invoice Number],[Invoice Date]," & _
    " [Chargeback ID],[Contract Indicator],[Unit Cost],[WAC],[Potential Credit Due],[Qty],[Spend],[IP-DSH indicator Y/N]," & _
    " [DSH and/or HRSA Number],[Unique GPO Code],[Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])" & _
    " VALUES ('" & sAudit & "', '" & sAuditType & "', '" & sClaimeReceivedDate & "', '" & sDateAssigned & "', '" & sDateCompleted & "', '" & sAnalyst & "', '" & sCustomer & "', '" & sID & "', '" & sAffiliate & "', '" & sFacility & "', '" & sDEA & "', '" & sAcctNumber & "', '" & sWholesaler & "', '" & sVendor & "', '" & sProduct & "', '" & sNDC & "', '" & sRef & "', '" & sClaimedContract & "', '" & sClaimedContractCost & "', '" & sContractPriceStartDate & "', '" & sContractPriceEndDate & "', '" & sCatalogNumber & "', '" & sInvoiceNumber & "', '" & sInvoiceDate & "', '" & sChargebackID & "', '" & sContractIndicator & "', '" & sUnitCost & "', '" & sWAC & "', '" & sPotentialCreditDue & "', '" & sQTY & "', '" & sSpend & "', '" & sIpDshIndicator & "', '" & sDSHorHRSANumber & "', '" & sUniqueGPOCode & "', '" & sComment & "', '" & sResCode & "', '" & sCorrectCost & "', '" & sCRRBCM & "', '" & sCRRBRebill & "', '" & sCRRBDate & "')"


    Debug.Print strSQL

'   SET TO CONNECTION VARIABLES
    Set DBCONT = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

'   LOOP THROUGH AND APPEND TO TABLE
    ThisWorkbook.Worksheets("Audit Data").Select
    LastRowAudit = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    With ActiveSheet

    DBCONT.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"



        For i = 2 To LastRowAudit
            sAudit = Cells(i, 1)
            sAuditType = Cells(i, 2)
            sClaimeReceivedDate = Cells(i, 3)
            sDateAssigned = Cells(i, 4)
            sDateCompleted = Cells(i, 5)
            sAnalyst = Cells(i, 6)
            sCustomer = Cells(i, 7)
            sID = Cells(i, 8)
            sAffiliate = Cells(i, 9)
            sFacility = Cells(i, 10)
            sDEA = Cells(i, 11)
            sAcctNumber = Cells(i, 12)
            sWholesaler = Cells(i, 13)
            sVendor = Cells(i, 14)
            sProduct = Cells(i, 15)
            sNDC = Cells(i, 16)
            sRef = Cells(i, 17)
            sClaimedContract = Cells(i, 18)
            sClaimedContractCost = Cells(i, 19)
            sContractPriceStartDate = Cells(i, 20)
            sContractPriceEndDate = Cells(i, 21)
            sCatalogNumber = Cells(i, 22)
            sInvoiceNumber = Cells(i, 23)
            sInvoiceDate = Cells(i, 24)
            sChargebackID = Cells(i, 25)
            sContractIndicator = Cells(i, 26)
            sUnitCost = Cells(i, 27)
            sWAC = Cells(i, 28)
            sPotentialCreditDue = Cells(i, 29)
            sQTY = Cells(i, 30)
            sSpend = Cells(i, 31)
            sIpDshIndicator = Cells(i, 32)
            sDSHorHRSANumber = Cells(i, 33)
            sUniqueGPOCode = Cells(i, 34)
            sComment = Cells(i, 35)
            sResCode = Cells(i, 36)
            sCorrectCost = Cells(i, 37)
            sCRRBCM = Cells(i, 38)
            sCRRBRebill = Cells(i, 39)
            sCRRBDate = Cells(i, 40)



            DBCONT.Execute strSQL

         Next i
    End With

    Call CloseDB

    MsgBox i & " Lines Imported."

End Sub

Sub CloseDB()
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    DBCONT.Close
    Set DBCONT = Nothing
End Sub

Answer 1

Reconsider your heavy Excel migration to SQL Server for several solutions:

1. SQL Server: Save Excel data into text format (.txt, .tab, .csv) and use any number of SQL Server's bulk handling operations: bcp utility, OPENROWSET, OPENDATASOURCE, import wizard, and BULK INSERT. Place this process in a stored procedure to be run inside database.

   Below are two equivalent OPENROWSET examples on Excel workbooks where no individual values are handled but whole table operations:

   -- OLEDB PROVIDER
   INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...) 
   SELECT COl1, Col2, Col3, ... 
   FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0; Database=C:\Path\To\Excel\Workbook.xlsx; HDR=YES; IMEX=1', [Sheet$]);
   
   -- ODBC DRIVER
   INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...) 
   SELECT COl1, Col2, Col3, ...
   FROM OPENROWSET('MSDASQL',
        'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
         DBQ=C:\Path\To\Excel\Workbook.xlsx', 'SELECT * FROM [Sheet$]');
   

2. MS Access: Use Excel's database sibling, Access, as medium between the source and destination sources. Specifically, have Excel sheet and SQL Server table as linked tables where you can run a simple append query:

   INSERT mySQLServerTable (Col1, Col2, Col3, ...) 
   SELECT Col1, COl2, Col3 ... 
   FROM myExcelTable
   

3. MS Excel: If you have to run in Excel, use parameterization with ADO Command object and avoid the need to concatenate VBA values to SQL code with numerous quote enclosures.

   ... same as above...
   
   ' PREPARED STATEMENT
   strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
            "    ([Audit], [Audit Type], [Claim Received Date], [Date Assigned], [Date Completed]," & _
            "     [Analyst], [Customer], [ID], [Affiliate], [Facility], [DEA], [Acct Number], [Wholesaler]," & _
            "     [Vendor], [Product], [NDC], [Ref], [Claimed Contract], [Claimed Contract Cost]," & _
            "     [Contract Price Start Date], [Contract Price End Date], [Catalog Number], [Invoice Number], [Invoice Date]," & _
            "     [Chargeback ID], [Contract Indicator], [Unit Cost],[WAC], [Potential Credit Due]," & _
            "     [Qty], [Spend],[IP-DSH indicator Y/N], [DSH and/or HRSA Number], [Unique GPO Code]," & _ 
            "     [Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])" & _
            " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," _
            "         ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
   
   DBCONT.Open ...
   
   Set sh = ActiveSheet
   
   For i = 2 To LastRowAudit
       Set cmd = CreateObject("ADODB.Command")
   
       With cmd
         .ActiveConnection = DBCONT   ' CONNECTION OBJECT
         .CommandText = strSQL        ' SQL STRING     
         .CommandType = adCmdText
   
         ' BINDING PARAMETERS
         .Parameters.Append .CreateParameter("sAuditParam", adVarChar, adParamInput, 255, sh.Cells(i, 1))
         .Parameters.Append .CreateParameter("sAuditTypeParam", adVarChar, adParamInput, 255, sh.Cells(i, 2))
         .Parameters.Append .CreateParameter("sClaimeReceivedDateParam", adVarChar, adParamInput, 255, sh.Cells(i, 3))
   
          '... rest of parameters
   
         .Execute                     ' RUN ACTION
       End With
   
       Set cmd = Nothing
   Next i
   

Answer 2

You already built strSQL using empty variables, and you never update it after that, so it remains that way.

You need to insert the field values each time through the loop.

Move this:

strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
'etc etc

to just before

DBCONT.Execute strSQL