![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
HenryMaher
Joined: 15 Oct 2015 Posts: 11
|
|
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
|
|
Sun Dec 03, 2017 7:16 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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;
|
|
Sun Dec 03, 2017 2:01 pm |
|
![](templates/GreenTech/images/spacer.gif) |
HenryMaher
Joined: 15 Oct 2015 Posts: 11
|
|
|
|
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?
|
|
Sun Dec 03, 2017 9:27 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
Mon Dec 04, 2017 2:39 am |
|
![](templates/GreenTech/images/spacer.gif) |
HenryMaher
Joined: 15 Oct 2015 Posts: 11
|
|
|
Mon Dec 04, 2017 7:52 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Awesome. Thank you very much for describing the complete solution. I'm sure other users will find it very helpful.
|
|
Mon Dec 04, 2017 8:48 am |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|