How to create a table in a MS access database in C#

Question

I need to create a table in MS access database. Consider, 'ConfigStructure.mdb' being my database name and i need to create a table in this database in C#.

How can i do this? I tried with the below code but its not working.

        OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + frmMain.strFilePath + "\\ConfigStructure.mdb");
        myConnection.Open();
        string strTemp = " KEY Text, VALUE Text ";
        OleDbCommand myCommand = new OleDbCommand();
        myCommand.Connection = myConnection;
        myCommand.CommandText = "CREATE TABLE table1(" + strTemp + ")";
        myCommand.ExecuteNonQuery();
        myCommand.Connection.Close();

This is the error that i get,

"System.Data.OleDb.OleDbException: Syntax error in field definition
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)"

Answer 1

Replace

string strTemp = " KEY Text, VALUE Text ";

with

string strTemp = " [KEY] Text, [VALUE] Text ";

I think the reason for this is that 'KEY' an 'VALUE' are reserved keywords in Access or SQL.

Answer 2

KEY and VALUE are both reserved words. Particularly it is probably getting hung up on "KEY" because you can specify PRIMARY KEY as a constraint in a CREATE TABLE command.

Try using different column names or surrounding them by brackets (e.g. [KEY]) if you really want to use them (not suggested).