VB.Net OleDbException "Syntax error in field definition"

Question

I'm trying to create a table in an Access database through an OleDbCommand in VB.Net with the following SQL:

CREATE TABLE InTemp (Month DATE, Description TEXT(255), Cost DOUBLE, Patron TEXT(255));

The code works fine in Access, but running it in VB.Net returns the OleDbException "Syntax error in field definition".

Full VB code:

Dim con As New OleDb.OleDbConnection
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " + DatabaseLocale
con.Open()
Dim createInTemp As New OleDb.OleDbCommand("CREATE TABLE InTemp (Month DATE, Description TEXT(255), Cost DOUBLE, Patron TEXT(255));", con)
createInTemp.ExecuteNonQuery()

I know there's no problem with the connection as it works elsewhere in my program.

Any help gladly appreciated!

James · Accepted Answer · 2014-01-15 11:41:23Z

5

I'd stab a guess at Month DATE being the problem here, Month is a function in MS Access so there's a good chance it's clashing. Try changing your query to

CREATE TABLE InTemp ([Month] DATE, [Description] TEXT(255), [Cost] DOUBLE, [Patron] TEXT(255))

Also, as far as I am aware DATE is a type specific to the app, not necessarily the data store. I would use the actual underyling type which is DATETIME (same could be said for TEXT).

See Microsoft Access Data Types.

answered Jan 15, 2014 at 11:41

James

82.4k19 gold badges172 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rusty595 Over a year ago

Thank you so much! I thought it'd be something dumb like that :P

Collectives™ on Stack Overflow

VB.Net OleDbException "Syntax error in field definition"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related