SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Microsoft Access Create table can't use DEFAULT syntax

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Microsoft Access Create table can't use DEFAULT syntax
Author Message
HenryMaher



Joined: 15 Oct 2015
Posts: 11

Post Microsoft Access Create table can't use DEFAULT syntax Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
HenryMaher



Joined: 15 Oct 2015
Posts: 11

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
HenryMaher



Joined: 15 Oct 2015
Posts: 11

Post Reply with quote
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
Mon Dec 04, 2017 7:52 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
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


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.