Excel - Macro - MS Access - Simple CRUD example

Watch this example on YouTube

Sub connectToDB()
    Dim myConnection As ADODB.Connection
    Dim myCommand As ADODB.Command
   
    Set myConnection = New ADODB.Connection
   
    myConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\ExcelTest\TestDB.accdb;Jet OLEDB:Engine Type=5; Persist Security Info=False;"
    myConnection.ConnectionTimeout = 30
    myConnection.Open
   
    Set myCommand = New ADODB.Command
    myCommand.ActiveConnection = myConnection
   
    strSqlCreate = "CREATE TABLE MyNewTest " & _
    " (ID number, " & _
    " Name text Null, " & _
    " DateName text Null)"
    myCommand.CommandText = strSqlCreate
    myCommand.Execute
   
    strInsertCommand = "INSERT INTO MyNewTest Values(1, 'Frank', '1/1/2014')"
    myCommand.CommandText = strInsertCommand
    myCommand.Execute
   
    strUpdateCommand = "UPDATE MyNewTest Set Name = 'Bob', DateName = '1/1/2015' where ID = 1"
    myCommand.CommandText = strUpdateCommand
    myCommand.Execute
   
    strDeleteCommand = "Delete from MyNewTest"
    myCommand.CommandText = strDeleteCommand
    myCommand.Execute
   
    strDropCommand = "Drop Table MyNewTest"
    myCommand.CommandText = strDropCommand
    myCommand.Execute
   
 End Sub