Excel - Macro - MSSQL - Simple CRUD example

Watch this example on YouTube

Sub Test()
    Dim myConnection As ADODB.Connection
    Dim myCommand As ADODB.Command
    Set myConnection = New ADODB.Connection
   
    myConnection.ConnectionString = "Driver={SQL Server native Client 11.0}; Server=(LocalDB)\v11.0;AttachDBFileName=C:\Users\livingroomadmin\TEST.mdf;Trusted_Connection=Yes;"
    myConnection.ConnectionTimeout = 15
    myConnection.Open
   
    Set myCommand = New ADODB.Command
    myCommand.ActiveConnection = myConnection
   
    qCreate = "Create Table TestTable " & _
      "(ID int, " & _
      " Name nVarchar(20) Null, " & _
      " TransDate datetime Null)"
    myCommand.CommandText = qCreate
    myCommand.Execute
   
    qInsert = "Insert Into TestTable Values (1, 'Frank', '1/1/2015')"
    myCommand.CommandText = qInsert
    myCommand.Execute
   
    sUpdate = "Update TestTable " & _
      " Set Name = 'Bob', TransDate = '1/1/2016' Where ID = 1"
    myCommand.CommandText = sUpdate
    myCommand.Execute
   
    sDrop = "Drop Table TestTable"
    myCommand.CommandText = sDrop
    myCommand.Execute
      
End Sub