1

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.

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).

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.

Then we get to the code that is problematic:

  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

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:

INSERT INTO Addendum_A_Preview ([variablename1] , [variablename2], [variablename3], [variablename4], [variablename5], [variablename6], [variablename7], [variablename8]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

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).

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

Please help. I'll be beating my head against the keyboard until someone answers.

Calling 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))

Last line calls the code in question.

EDIT: Solution ended up being my table name was not in brackets. Thanks all!

12
  • @Steve, Yes, as noted, the Cleanup simply adds the brackets since I need to be flexible in regards to what columns they might be including (internal users, but it's for a generic import process where a non-programmer user is previewing the data in Access). My accDataAdapter.InsertCommand.CommandText in my test data is like this after: INSERT INTO Addendum_A_Preview ([variablename1] , [variablename2], [variablename3], [variablename4], [variablename5], [variablename6], [variablename7], [variablename8]) VALUES (?, ?, ?, ?, ?, ?, ?, ?) Commented Apr 4, 2017 at 15:48
  • Can you show where dt is instantiated? (Also, you're correct regarding the '?' values.) Are you 100% positive the data types align from the DataTable to the Database? (Dates in access are a nightmare sometimes.) Commented Apr 4, 2017 at 15:59
  • Try and insert a single row into the database after creating the table automatically. Commented Apr 4, 2017 at 16:04
  • Editing question now Commented Apr 4, 2017 at 16:05
  • 1
    You shouldn't need to manually "clean up" the InsertCommand; you can use the QuotePrefix and QuoteSuffix properties of the CommandBuilder object to do that for you (ref: here). Commented Apr 4, 2017 at 16:16

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.