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

Question

I need to create a table in an MS access database. Consider that "ConfigStructure.mdb" is my database name, and I need to create a table in this database in C #.

How can i do this? I tried with the code below, but it does not work.

        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 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 ";

from

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

I think the reason is that "KEY" and "VALUE" are reserved keywords in Access or SQL.

Answer 2

KEY VALUE . , , , "KEY", PRIMARY KEY CREATE TABLE.

(, [KEY]), ( ).

Answer 3

, KEY - , , . .

Answer 4

"KEY" "VALUE" MS Access. , :

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

Answer 5

Windows 7 sp1 Professional 64-bit, Microsoft ADO Ext. 2.8 DDL C:\Program Files\Common Files\System\ado\msadox28.dll.

:

ADOX

[255]. .

table.Columns.Append("PartNumber", ADOX.DataTypeEnum.adVarWChar, 6); // text[6]
table.Columns.Append("AnInteger", ADOX.DataTypeEnum.adInteger); // Integer 

= adVarWChar

Access Memo = adLongVarWChar

= adUnsignedTinyInt

= adSmallInt

= adInteger

Access Numeric Single Precision = adSingle

= adDouble

Access Numeric Replicatie-id = adGuid

= adNumeric

/ = adDate

= adCurrency

Access AutoNumber = adInteger

/ = adBoolean

HyperLink = adLongVarWChar

Answer 6

You should always write the names of tables and columns in these []. Your example:

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();

It works great.