0
OleDbCommand oleDbCmd = new OleDbCommand();
OleDbConnection bookConn = Sqlhelper.Conncect_Mdb();
oleDbCmd.Connection = bookConn;
oleDbCmd.CommandText = "ALTER TABLE doc_comp ADD COLUMN versioncode NUMBER DEFAULT 0";
oleDbCmd.ExecuteNonQuery();
bookConn.Close();

Here is my code for alter table in ms access,it throws error Syntax error in table-level validation expression.This code works fine for without adding 'DEFAULT 0'. I am using MS ACCESS 2007.Tried with this but I cant set using tools.

Community
  • 1
  • 1
Rashmi S
  • 267
  • 1
  • 7
  • 20

2 Answers2

0

Your code works for me if I use the following connect string:

static public OleDbConnection Conncect_Mdb()
{
    const string oledb = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=scratch.accdb";     
    var conn = new OleDbConnection(oledb);
    conn.Open();
    return conn;
}

There are older drivers, specially the ones that run over ODBC that require you to indicate which sql support you need in the driver. An example of such seting in an Odbc connectionstring is ExtendedAnsiSQL=1.

If your setup doesn't have the Microsoft Access Database Engine 2010, which also support Access 2007, you can download and install the redistributable from the Microsoft Download.

rene
  • 41,474
  • 78
  • 114
  • 152
  • my connection string String connParam = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath + ";Persist Security Info=False" ; – Rashmi S Sep 07 '15 at 05:29
  • Change the provider to the newer driver (if you don't have it yet, download and install first) – rene Sep 07 '15 at 06:02
  • How can I check that the provider is properly installed. I am using Office 2013, uninstalled 2010. I have used your exact connection string and cannot execute queries. – htm11h Nov 06 '15 at 15:40
  • @htm11h You can check the registry by finding `Microsoft.ACE.OLEDB` under HKEY_CLASSES_ROOT. If you're building x64 you might try AnyCPU instead in case you only have the 32bits drivers installed – rene Nov 06 '15 at 17:42
0

This answer does not pertain to the specific question, but it does answer EXACTLY for the error message posted in the TITLE....

Syntax error in table-level validation expression in MS ACCESS

I received this exact error and the fix was to remove a semi colon from the end of the query statement.

I am using the OP's query to present the solution...

oleDbCmd.CommandText = "ALTER TABLE doc_comp ADD COLUMN versioncode NUMBER DEFAULT 0**;**";

Should be,

oleDbCmd.CommandText = "ALTER TABLE doc_comp ADD COLUMN versioncode NUMBER DEFAULT 0";

Remove the semi colon surrounded by asterisks from the query statement. This resolved this EXACT error for me.

htm11h
  • 1,739
  • 8
  • 47
  • 104