title: Copy System.Data.Datatable to Access DB Programmatically Vb.net
body: <p>I've looked through an awful lot of questions/answers here, I'm sure this has to have been asked before but I can't find it as it applies to my exact situation.</p>
  
  <p>I have a system.data.datatable from a generic source (could be anything), and I need to be able to export it to Access. The original source may in fact be sql, excel, etc., but it's been stored in a system.data.datable successfully (no problem).</p>
  
  <p>In code, I create a new Access DB .accdb file, create the table by looping through the columns and translating the data types. This executes successfully.</p>
  
  <p>Then we get to the code that is problematic:</p>
  
  <pre><code>  Try
  
          dt.TableName = tableName
          Using accConnection As New OleDb.OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;", dbPath))
  
              'first create table
              CreateTable(accConnection, dt, tableName)
  
              'set up dataadapter to copy data over
              Dim selectCommand As String = String.Format("SELECT * From {0}", tableName)
              Dim accDataAdapter As New OleDb.OleDbDataAdapter(selectCommand, accConnection)
              'accDataAdapter.TableMappings.Add(tableName, tableName)
  
              Dim accCommandBuilder As New OleDb.OleDbCommandBuilder(accDataAdapter)
              accDataAdapter.InsertCommand = accCommandBuilder.GetInsertCommand
              'accDataAdapter.UpdateCommand = accCommandBuilder.GetUpdateCommand
              CleanUpInsertCommand(accDataAdapter.InsertCommand)
              'CleanUpUpdateCommand(accDataAdapter.UpdateCommand)
  
              accDataAdapter.Update(dt.Copy)
  
          End Using
  
      Catch ex As Exception
          Utility.HandleExceptionWithDefaults(ex, String.Format("Copying {0} to {1}", tableName, dbPath))
          Return False
      End Try
  </code></pre>
  
  <p>This seems to work fine up until the final .Update(dt.copy) call, where I get an insert syntax error. The 'CleanUpInsertCommand' simply adds brackets to the column names (since they may include spaces, keywords, or whatever a user may have entered), and does not appear to be the cause. Here is the InsertCommand.CommandText in my current test after manipulation prior to the update call:</p>
  
  <pre><code>INSERT INTO Addendum_A_Preview ([variablename1] , [variablename2], [variablename3], [variablename4], [variablename5], [variablename6], [variablename7], [variablename8]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  </code></pre>
  
  <p>Which looks fine, as far as I know, since I assume the update fills the values '?' with the appropriate value during the Update call. If I'm wrong, please correct me (the update call is a bit of a blackbox to me, I'd rather not breakout reflector on it).</p>
  
  <p>I can't see what's causing the issue though, and I'm getting this error:
  Exception: Syntax error in INSERT INTO statement.
  Type: System.Data.OleDb.OleDbException</p>
  
  <p>Please help. I'll be beating my head against the keyboard until someone answers.</p>
  
  <p>Calling Code:</p>
  
  <pre><code>    Dim dt As DataTable = DirectCast(gridData.DataSource, DataTable)
      Dim dbPath As String = String.Format("{0}\{1}.accdb", FileIO.SpecialDirectories.Desktop, _outputFileName) 'FileIO.SpecialDirectories.Desktop, _outputFileName)
  
      AccessHelper.CreateNewDatabase(dbPath)
      AccessHelper.AddTableToDatabase(dbPath, dt, _outputFileName.Replace(" "c, "_"c))
  </code></pre>
  
  <p>Last line calls the code in question.</p>
  
  <p>EDIT: 
  Solution ended up being my table name was not in brackets. Thanks all!</p>