title: VBA ADO Excel-2010 Refrence table with SQL insert
body: <p>I have code to wright an insert from one excel workbook to an external workbook. When I insert a row it works but the table the data is in doesn't increment. Is there any way other then opening the workbook to have to row increment as I insert a new row from my VBA code from a external workbook????</p>
  
  <pre><code> Public Function insertComment()
     Dim tablename As String
     tablename = "sheet1"
  
  
     Dim cmd As ADODB.Command
     Dim conn As ADODB.connection
     Dim prm1 As ADODB.Parameter
     Dim prm2 As ADODB.Parameter
     Dim prm3 As ADODB.Parameter
     Dim connectionString As String
     Dim SQL As String
  
     Set conn = New ADODB.connection
     Set cmd = New ADODB.Command
  
     DBPath = "path\to\file\workbook1.xlsm"
     connectionString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" &amp; DBPath &amp; ";HDR=Yes';"
    SQL = "INSERT INTO [" &amp; tablename &amp; "$] ([QuestionID], [EvidenceID], [Data]) VALUES (?, ?, ?)"
  
      'Open connection
      conn.Open connectionString
  
     'Set command text
     cmd.CommandText = SQL
     cmd.CommandType = adCmdText
  
     'Set connection
     cmd.ActiveConnection = conn
  
     'Set paramters in SQL query
      Set prm1 = cmd.CreateParameter("@p1", adInteger, adParamInput)
      cmd.Parameters.Append prm1
      cmd.Parameters("@p1").Value = 1
  
     Set prm2 = cmd.CreateParameter("@p2", adInteger, adParamInput)
     cmd.Parameters.Append prm2
     cmd.Parameters("@p2").Value = 2
  
     Set prm3 = cmd.CreateParameter("@p3", adInteger, adParamInput)
     cmd.Parameters.Append prm3
     cmd.Parameters("@p3").Value = 3
  
  
     cmd.Execute
     conn.Close
  End Function
  </code></pre>