Microsoft Access Create table can't use DEFAULT syntax

Hi,
from Notepad ++ connecting to MS-access db using connection type ODBC, Driver={Microsoft Access Driver (*.mdb, *.accdb)}
I'm trying to create a table.
If I use
CREATE TABLE Persons (
LastName varchar(255)
);
I will not get an error and the table will be created.
But if I use
CREATE TABLE Persons (
LastName varchar(255) DEFAULT 'Sandnes'
);
I get the following error:
Execute: ODBC Error (-3551): SQLExecDirectW Client: Microsoft Access database engine Server: 12.00.0000 SQL State: 42000 [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.; CREATE TABLE Persons ( LastName varchar(255) DEFAULT 'Sandnes' )

It seems that I am not using the right connection pls see article
https://stackoverflow.com/questions/14964553/create-table-syntax-error-ms-access-sql-view

So I tried to connect using ADO.net with System.Data.OleDb but get a connection failed.

Can anyone enlighten me on how to connect with ADO.net ?? please

By default the Microsoft Access database engine does not support the use of DEFAULT value in CREATE TABLE syntax. This is the root cause of the issue. Has nothing to do with the connection type.

But there is a solution to this issue. Click Tools -> Options -> Tables/Queries -> menu; select (At the bottom right:) Sql Server Compatible Syntax - turn option on for this database.

CREATE TABLE documents (docid NUMBER);
ALTER TABLE documents ADD COLUMN membersOnly NUMBER DEFAULT 0;

Hi,
Thanks for getting back to me. Am using Access 2013 so I went in search of the settings you recommended. Went to File--> options --> object designers --> SQL server compatible syntax (ANSI 92) --> clicked this database

Ran the SQL code as per instructions and am getting error
Execute: ODBC Error (-3554): SQLExecDirectW Client: Microsoft Access database engine Server: 12.00.0000 SQL State: 42000 [Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.; ALTER TABLE documents ADD COLUMN membersOnly NUMBER DEFAULT 0

Sorry to be a pain but could you look at
https://msdn.microsoft.com/en-us/library/bb243857(v=office.12).aspx
There is a part at the end
The DEFAULT statement can be executed only through the Access OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.

Any change you could help me out further?

No pain. Happy to help if I can.

I did a quick search of MSDN, and I think for ADO.NET connection you would need to select ADO.NET connection type, select System.Data.OleDb for data provider, then click "Show Raw Connection String" and in the raw settings box enter additional properties, for example,

Provider=Microsoft.ACE.OLEDB.12.0
Data Source=C:\Data\MyDatabaseFile.accdb

Depending on the Access or Windows version installed, you may need to specify Microsoft.Jet.OLEDB.4.0 as the provider.

If neither of that works for you (sorry I'm not able to test it locally), you may need to obtain a true ADO.NET provider for Access, and not a generic System.Data.OleDb. Perhaps. this one can do the trick
https://marketplace.visualstudio.com/items?itemName=CDATASOFTWARE.AccessADONETProvider

Last edited by SysOp on Mon Dec 04, 2017 8:48 am; edited 1 time in total

You bloody ripper !!!!
Thanks so much. I really thought I was going to have to sit and click away in that clunky UI of Access.

For anyone who needs it the only thing missing was the ; marks at the end. Other then that just as sysOp said.
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C"\path_to_app;

And to check to see if the provider is installed just run the following code in power shell
(New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION

If not installed can check out this thread
https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine?forum=vstsdb

Thanks again that is just fantastic

Awesome. Thank you very much for describing the complete solution. I'm sure other users will find it very helpful.