How to use Excel VBA to add data to SQL DB Table

Steps to use Excel VBA / Macro to insert records into SQL Database Table.

 

1. Launch the Visual Basic Editor from Excel and add reference to the "Microsoft ActiveX Data Objects Library" from Tools > References

 

2. In the Editor window, create a new module and add the following code to create the connection settings for the SQL Server

Dim oConn As ADODB.Connection

Private Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=SQLOLEDB; " & _
            "Data Source=DBServerName; " & _
            "Initial Catalog=DBInstanceName;" & _
            "User ID=sa; Password=sa;"
End Sub

3. Add the following code, to use the connection object created in Step2 and generate the sql insert statement to add records to the SQL Database table

Dim rs As ADODB.Recordset

Public Sub InsertData()
    ConnectDB
    Set rs = New ADODB.Recordset
   

    Dim strSql As String    
    strSql = "insert into table1 values (‘a’,’b’)"     ‘Put your SQL Insert statement here
    rs.Open strSql, oConn, adOpenDynamic, adLockOptimistic

   

    CloseDBConn

End Sub

Private Sub CloseDBConn()
    oConn.Close
End Sub

Thats all we need here !