CODEWITHSUNDEEP
c#
SyncMaster
SyncMaster

Reputation: 9916

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

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

Upvotes: 3

Views: 42940

Answers (6)

Yura
Yura

Reputation: 1

You should always write names of the 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();

Works perfect.

Upvotes: 0

Jim Lahman
Jim Lahman

Reputation: 2757

On my computer, Windows 7 sp1 Professional 64-bit, I found Microsoft ADO Ext. 2.8 for DDL and Security in C:\Program Files\Common Files\System\ado\msadox28.dll.

It is also found as a reference:

enter image description here

which is included as ADOX in the references

enter image description here

By default, columns are created as text[255]. Here are a few examples to create columns as different datatypes.

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

I found this list of datatypes to create and read access database fields

Access Text = adVarWChar

Access Memo = adLongVarWChar

Access Numeric Byte = adUnsignedTinyInt

Access Numeric Integer = adSmallInt

Access Numeric Long Integer = adInteger

Access Numeric Single Precision = adSingle

Access Numeric Double Precision = adDouble

Access Numeric Replicatie-id = adGuid

Access Numeric Decimal = adNumeric

Access Date / Time = adDate

Access Currency = adCurrency

Access AutoNumber = adInteger

Access Yes / No = adBoolean

Access HyperLink = adLongVarWChar

Upvotes: 0

Julien Poulin
Julien Poulin

Reputation: 13015

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.

Upvotes: 17

Fredrik Mörk
Fredrik Mörk

Reputation: 158289

"KEY" and "VALUE" are reserved words in MS Access. If you wish to use those names for your fields you will need to enclose them in square brackets:

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

Upvotes: 1

lc.
lc.

Reputation: 116448

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

Upvotes: 2

Steve
Steve

Reputation: 8511

I suspect the problem is using the word KEY as a column name - this is probably a reserved word. Try using different column names.

Upvotes: 1

Related Questions