Trouble inputting code from Erwin in access (mostly with datatypes)

Question

This is quite a basic level question sorry, I've only been doing this a couple of weeks. Our task is to create a database in both Oracle and Access after generating our model in Erwin. The model has been created in Erwin, and we generated the SQL code directly from Erwin to input in Oracle. Now I'm trying to modify the code to input it into Access (changing VARCHAR2 to VARCHAR, NUMBER to INTEGER etc). However I'm running into a lot of trouble with consistent errors.

Here is a portion of the SQL code (there are 21 similar tables)

CREATE TABLE ASSUMPTION
(
    ASSUMPTION_ID        NUMBER(25) NOT NULL ,
    COUNTY               VARCHAR2(23) NOT NULL ,
    CLAIM_HISTORY        VARCHAR2(200) NULL ,
    ADDITIONAL_PROPERTY  VARCHAR2(200) NULL ,
    REBUILD_COST         DECIMAL(6,2) NULL ,
    MAIN_RESIDENCE       VARCHAR2(3) NOT NULL ,
    CURRENT_INSURANCE_COVER VARCHAR2(200) NULL ,
    CRIMINAL_HISTORY     VARCHAR2(200) NULL ,
    BUSINESS_USE         VARCHAR2(3) NOT NULL ,
    CUSTOMER_ACCEPTANCE  VARCHAR2(3) NOT NULL ,
    QUOTE_ID             NUMBER(25) NOT NULL 
);



ALTER TABLE ASSUMPTION
    ADD CONSTRAINT  XPKASSUPMTION PRIMARY KEY (ASSUMPTION_ID,QUOTE_ID);

The changes I've made render it like this

CREATE TABLE ASSUMPTION
(
    ASSUMPTION_ID        INTEGER(25) NOT NULL ,
    COUNTY               VARCHAR(23) NOT NULL ,
    CLAIM_HISTORY        VARCHAR(200) NULL ,
    ADDITIONAL_PROPERTY  VARCHAR(200) NULL ,
    REBUILD_COST         CURRENCY NULL ,
    MAIN_RESIDENCE       VARCHAR(3) NOT NULL ,
    CURRENT_INSURANCE_COVER VARCHAR(200) NULL ,
    CRIMINAL_HISTORY     VARCHAR(200) NULL ,
    BUSINESS_USE         VARCHAR(3) NOT NULL ,
    CUSTOMER_ACCEPTANCE  VARCHAR(3) NOT NULL ,
    QUOTE_ID             INTEGER(25) NOT NULL 
);

followed by the same

ALTER TABLE ASSUMPTION
    ADD CONSTRAINT  XPKASSUPMTION PRIMARY KEY (ASSUMPTION_ID,QUOTE_ID);

I'd be really grateful if people could point out where I'm going wrong. It consistently returns a syntax error.

Also it's necessary to require an acceptance or 'yes' in the 'customer_acceptance box' in order for them to continue on accessing the rest of the form. Should I be trying to specify that in this initial assumption box? Is it possible to just pick up the Erwin file and open the code it contains directly in Oracle or Access or to transform it automatically rather than by hand?

Thanks.

Answer 1

There are a number of things going on here.

First, the integer(25) fields. These are id fields and therefore presumably non-volatile. I would recommend that you change these fields to text(25) with a numeric only domain restriction. This way you will avoid comparability problems between different platforms. A side question, does it really need to be this big? If it does you might want to consider using a UUID. That will give you a 16 bit text value that:

• Has a larger range then integer(25)
• Is pretty much guaranteed to be unique across platforms

Second, It looks like you are trying to manually change the generated Oracle code. I'd recommend that instead you go back into the model, change the the target database to access and then just forward engineer the access code for you.

I hope this helps and good luck.