Microsoft Access - CHECK errors

Question

these are the requirements that we have to use SQL to code and we have to build in a check on the table.

I can't find a helpful answer --

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,

CONSTRAINT [data_totals_test] CHECK (data_totals between 1 and 5 )
);

error message:

syntax error in constraint clause

if i do it like this

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,

CHECK (data_totals between 1 and 5 )
);

or this

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,

CHECK (data_totals > 1 and data_totals < 5 )
);

i get error message in both cases --

syntax error in field definition

if i take out the , after INT then I get the error message:

syntax error in Create Table Statement.

also tried this:

create a table this way (successfully)

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT
);

tried using alter:

alter table data ADD CHECK (data_totals > 1 );

got the same error:

syntax error in field definition

please advise.

Are you executing those statements using `CurrentProject.Connection.Execute`? That's required for those types of statements. — Erik A, Apr 06 '18 at 17:03
possible duplicate of https://stackoverflow.com/questions/1660512/is-it-possible-to-create-a-check-constraint-in-access-and-or-dao — MichaelEvanchik, Apr 06 '18 at 17:08
@ErikvonAsmuth I will be honest, i am not sure what that means. I am in a project in access -- and i am clicking on Run from a sql query window. — Elizabeth, Apr 06 '18 at 17:09
@MichaelEvanchik i did see another question but nothing was clear or helpful in the solution and im sorry for the duplication of post but i am stuck with this — Elizabeth, Apr 06 '18 at 17:10
@Elizabeth, in VBA, you will need to execute this, after you just create the table without the constraint — MichaelEvanchik, Apr 06 '18 at 17:11
@MichaelEvanchik (apologies for not knowing more) but if i am not sure how to do that where can i learn more. my instructions are to use sql to create a table with these constraints — Elizabeth, Apr 06 '18 at 17:13
Possible duplicate of [Is it possible to create a check constraint in access and/or DAO?](https://stackoverflow.com/questions/1660512/is-it-possible-to-create-a-check-constraint-in-access-and-or-dao) — ashleedawg, Apr 06 '18 at 17:17
@Elizabeth smack the person who is making you use Microsoft access — MichaelEvanchik, Apr 06 '18 at 17:19
@ashleedawg as i mentioned in the previous comment, i tried looking for other quires but couldn't find a solution to how to script this. since my visual basic knowledge is supper is negligible i'm trying to sort this out in sql. — Elizabeth, Apr 06 '18 at 17:19
@MichaelEvanchik (as lovely as that would be its an educational institution so it would not go far). — Elizabeth, Apr 06 '18 at 17:20
i think you can do it in SQL hang in there, i dont have it installed having trouble to help — MichaelEvanchik, Apr 06 '18 at 17:20
does this not work CREATE TABLE data ( data_name varchar(10) unique, data_totals INTEGER, check (id < 5), constraint Mypk primary key (id) ) yes i know we have to do > 1 but just try this first — MichaelEvanchik, Apr 06 '18 at 17:21
i cant help you accept trial an error, control G opens another way to enter code — MichaelEvanchik, Apr 06 '18 at 17:26

MichaelEvanchik · Accepted Answer · 2018-04-06T18:37:16.010

2

try this https://support.office.com/en-us/article/customize-design-settings-for-objects-in-your-database-b6e0baa0-15d4-47ae-84c2-e0c46dcea23b

to set to ANSI 92 SQL

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT CHECK (data_totals > 1 and data_totals < 5)
);

alternatively

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,
CONSTRAINT CHK_data_totals CHECK (data_totals>1 AND data_totals<5)
);

edited Apr 06 '18 at 18:37

answered Apr 06 '18 at 18:05

MichaelEvanchik

1,748
1
13
23

also what is the access version, fyi between is <= and >= fyi and not the same – MichaelEvanchik Apr 06 '18 at 18:07
data reserved word? – MichaelEvanchik Apr 06 '18 at 18:21
technically its a ValidationRule , let me spin up a vm – MichaelEvanchik Apr 06 '18 at 18:22
please change to https://support.office.com/en-us/article/customize-design-settings-for-objects-in-your-database-b6e0baa0-15d4-47ae-84c2-e0c46dcea23b – MichaelEvanchik Apr 06 '18 at 18:36
SQL Server compatible syntax (ANSI 92) This database Select this option when you want to run queries against Microsoft SQL Server databases. You must use ANSI-92 syntax for all queries when you select this option. Existing queries written using the older ANSI-89 standard (the default for Access) might not run, or might return unexpected results. – MichaelEvanchik Apr 06 '18 at 18:37
otherwise u will need to do it via the designer of the table visually or ado va code – MichaelEvanchik Apr 06 '18 at 18:40
1

thank you please mark as answer with the star and push the up button so the community understands it was the answer – MichaelEvanchik Apr 06 '18 at 18:43

Microsoft Access - CHECK errors

1 Answers1