Check constraint in ms access

Question

I am trying to execute this statement in ms access (SQL view) but this shows an error on check constraint and on numeric(3,2) .. when I remove (3,2) from numeric and remove last 2 constriant check then this executes successfully ... but I want to add last check constraint in table.. when I add again then this shows an error ..

 CREATE  TABLE COMPUTER(    
    SerialNumber        Int                 NOT NULL,
    Make                Char(12)            NOT NULL,
    Model               Char(24)            NOT NULL,
    ProcessorType       Char(24)            NULL,
    ProcessorSpeed  Numeric(3,2)        NOT NULL,
    MainMemory          Char(15)            NOT NULL,
    DiskSize            Char(15)            NOT NULL,
    CONSTRAINT      COMPUTER_PK             PRIMARY KEY(SerialNumber),
    CONSTRAINT      MAKE_CHECK              CHECK(Make IN ('Dell', 'Gateway', 'HP', 'Other')),
    CONSTRAINT      SPEED_CHECK             CHECK(ProcessorSpeed BETWEEN 1.0 AND 4.0)
    );

what should I do ? i have MS ACCESS 2007

@GordonLinoff I tried on MS Access 2013 too, but the same error. — TimeToCode, Oct 15 '20 at 13:56

score 1 · Answer 1 · answered Oct 15 '20 at 11:15

Try running the CREATE TABLE using VBA/ADO i.e. in the Visual Basic Editor:

Sub CreateMyTable()
    Dim cnn As New ADODB.Connection
    Set cnn = CurrentProject.Connection
    cnn.Execute ("CREATE TABLE ... plus the rest of your create here")
    Set cnn = Nothing
End Sub

There are (regrettably) inconsistencies between what you can define using the Access SQL View and what you can define via VBA/ADO. In this case Numeric(3, 2) won't work in the Access SQL View, I believe and I don't think the constraints will work either. I'm assuming you have set the database option to be SQL-92 ANSI compatible.

Check constraint in ms access

1 Answers1