Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Feb 2010
    Posts
    9

    Unanswered: Error establishing a relation in access database through sql statement

    Hi
    I Execute the following sql statement successfully for a Access Database.

    Code:
    CREATE TABLE Scores ( 
    	StudentCode Text(9) NOT NULL,
    	CourseCode Text(7) NOT NULL,
    	SemesterCode Text(3) NOT NULL,
    	Score Double
    );
    
    CREATE TABLE Courses ( 
    	CourseCode Text(7) NOT NULL,
    	CourseName Memo NOT NULL,
    	CourseTypeID Integer NOT NULL,
    	CourseCategoryID Integer NOT NULL
    );
    
    ALTER TABLE Scores ADD CONSTRAINT PK_Grades 
    	PRIMARY KEY (StudentCode, CourseCode, SemesterCode);
    
    ALTER TABLE Courses ADD CONSTRAINT PK_Courses 
    	PRIMARY KEY (CourseCode);
    
    ALTER TABLE Courses
    	ADD CONSTRAINT UQ_Courses_CourseCode UNIQUE (CourseCode);


    But Finally I Get Error message(Syntax error in CONSTRAINT clause.) on this satement that is at the end of my execution list.

    Code:
    ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses 
    	FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode)
    ON UPDATE CASCADE;

    And I don't know Why?
    Please Help me on how to establish a relation in access database through sql statement?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know you cannot specify the Action part (ON ...,etc.) on such a constraint in Access SQL. In others RDBMS this creates an implicit trigger and Access does not support triggers, so this:
    Code:
    ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses 
    	FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode);
    is accepted and works, while this:
    Code:
    ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses 
    	FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode);
            ON UPDATE CASCADE;
    is refused by Access and produces a syntax error message.
    Have a nice day!

  3. #3
    Join Date
    Feb 2010
    Posts
    9

    Thanks, But

    Thanks That was my problem But what should I do for establishing referential integrity between database tables.

    According to MSDN How to: Modify a Table's Design Using Access SQL*[Access 2007 Developer Reference]
    I thought it should works.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cascading updates and deletes isn't done with triggers in any RDBMS I know, and this option is available via the GUI. Also, the Access SQL docs I refer to include the same syntax. In Access 2007 this fails for me too. I don't remember it working in previous versions, but nor do I definitely recall it not working.
    Last edited by pootle flump; 02-12-10 at 05:07.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah right - I think I know the answer. This is something I had forgotten about.

    Some of the valid JET\ ACE DDL statements cannot be executed via the Access GUI. You have to execute them via ADO (or, confusingly, DAO according to this reference I've just found).

    Here is the kb article:
    How to use common Data Definition Language (DDL) SQL statements for the Jet database engine

    Here also are the DDL references I use. TMK they all still apply to Access 2007 and are much more thorough than the reference you posted.
    Fundamental Microsoft Jet SQL for Access 2000
    Intermediate Microsoft Jet SQL for Access 2000
    Advanced Microsoft Jet SQL for Access 2000

    This also explains why I could not remember experiencing this before - I deploy updates to my clients by executing DDL scripts via ADO. As such, the GUI limitiation is something I once knew but have never had to worry about since.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2009
    Posts
    340
    am interested to know from the original poster as to what is the reason one would set up or create an Access table/db via code?......

  7. #7
    Join Date
    Feb 2010
    Posts
    9

    establish a relation through SQL statement for access

    Thanks,
    ah from the article you referenced I noticed
    "Note: You cannot specify that you want "Cascade Updates" or "Cascade Deletes" with a relationship created using DDL. These features are available only when using the Microsoft DAO (Data Access Objects) interfaces via code or when using the Microsoft Access user interface"
    and of course you could not use DAO execute method again. and DAO supply this for you from CreateRelation Method so The only remaining way te establish a relation through SQL statement is using ADO -Which our Friends Suggest -,of course again i am not sure and I have not tested yet.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would expect it to work fine - there are actually a few DDL statements that can only be executed by ADO, and although I can't remember if it was this, I have certainly run a few myself.

    BTW - A small matter of terminology: a relationship is not the same as a relation.

    A relation is a key term in relational databases (in fact it is what the "relational" is derived from) and has nothing to do with relationships.

    A relation is a mathematical construct. Essentially, a table is the physical manifestation of a relation in a similar way that a building is the physical manifestation of a blueprint. Sort of.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2010
    Posts
    9

    re :reason one would set up or create an Access table/db via code?

    Quote Originally Posted by NTC View Post
    and in the world of programming you would not better to ask Why instead of How.
    I'm still curious WHY? In the world of programming, if there's a much easier way (such as NOT programmatically coding/modifying a db structure), I can't help but wonder why (even with a portable application), you've chosen a method which makes it much, much more difficult (not just for you, but for others who might inherit your design.)

    In 25 years of developing applications (including many, many portable ones for other companies), I have never, ever had the need to programmitically create/modify a structure. This may not mean much to you but for others who read this post, the answer to why is important (especially when there really isn't a need to complicate an application by do so.)

    I'm not trying to criticize, I'm just trying to understand why someone would make an application much more difficult than it needs to be.
    Last edited by pkstormy; 02-12-10 at 14:59.

  10. #10
    Join Date
    Feb 2010
    Posts
    9

    Setting the Attributies of relation from DDL

    Quote Originally Posted by pootle flump View Post
    I would expect it to work fine - there are actually a few DDL statements that can only be executed by ADO, and although I can't remember if it was this, I have certainly run a few myself.

    BTW - A small matter of terminology: a relationship is not the same as a relation.

    A relation is a key term in relational databases (in fact it is what the "relational" is derived from) and has nothing to do with relationships.

    A relation is a mathematical construct. Essentially, a table is the physical manifestation of a relation in a similar way that a building is the physical manifestation of a blueprint. Sort of.
    ah yes your right,
    I should say setting the Attributes of relation instead of saying establishing a relation in the previous post

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not quite (as I understand you).

    relation == table
    attribute == column
    tuple == row

    relationship == ??? I'm not sure. But the relationship is how we describe the association we establish between two tables when we create a foreign key.


    BTW - I am very much in favour of why you use DDL statements. It is a shame that Access makes them so hard to use.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by NTC View Post
    am interested to know from the original poster as to what is the reason one would set up or create an Access table/db via code?......
    I agree. I have yet to ever code in table creation/modification (even on portable applications).

    I sometimes (although rarily) see other developers do this in code and often wonder why when it's much easier to setup tables/relationships in the actual design (and then distribute) versus in code.

    Creating/modifying table structures in code leads to a complexity that I find unnecessary, even if the app is going to be distrubited. I instead key in on correct table structure before it's distributed and never found the need to modify structures. (the only time I've ever had to create/modify tables in code was for school projects many, many years ago.)
    Last edited by pkstormy; 02-12-10 at 13:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Feb 2010
    Posts
    9
    Quote Originally Posted by pootle flump View Post
    Not quite (as I understand you).

    relation == table
    attribute == column
    tuple == row

    relationship == ??? I'm not sure. But the relationship is how we describe the association we establish between two tables when we create a foreign key.


    BTW - I am very much in favour of why you use DDL statements. It is a shame that Access makes them so hard to use.
    first thank you for point of view in using DDL. and about the expressions I use
    I would tell that you are using academic expressions of database design and I am using the "DAO Defined" expressions here
    CDaoDatabase::CreateRelation (MFC)
    is tha way you would use instead of defining foreign keys and
    in the method below

    Code:
    void CreateRelation( 
       LPCTSTR lpszName, 
       LPCTSTR lpszTable, 
       LPCTSTR lpszForeignTable, 
       long lAttributes, 
       LPCTSTR lpszField, 
       LPCTSTR lpszForeignField  
    );
    lAttributes is for foreign key Referential Integrity modes
    of course this type of speech is not standard But I mean the method above
    is the only way for defining "Referential Integrity" through code because We could not use "Cascade Updates" or "Cascade Deletes" in our DDL.

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by worldperseus
    and in the world of programming you would not better to ask Why instead of How.
    I'm still curious WHY? In the world of programming, if there's a much easier way (such as NOT programmatically coding/modifying a db structure), I can't help but wonder why (even with a portable application), you've chosen a method which makes it much, much more difficult (not just for you, but for others who might inherit your design.)

    In 25 years of developing applications (including many, many portable ones for other companies), I have never, ever had the need to programmitically create/modify a structure. This may not mean much to you but for others who read this post, the answer to why is important (especially when there really isn't a need to complicate an application by do so.)

    I'm not trying to criticize, I'm just trying to understand why someone would make an application much more difficult than it needs to be (which would be the same question I or someone else would ask if we ended up troubleshooting your app). If it's for a school course, then I understand. Otherwise, I'm confused why you would do so.
    Last edited by pkstormy; 02-12-10 at 15:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Feb 2010
    Posts
    9

    A universal rule

    There is a universal rule (mostly in the field of engineering)

    for a definite job if something is becoming better and easier be sure of an other thing which is becoming more inflexible and harder.

    That is why programming languages like C is still alive (despite of java or C# or VB) or nevertheless the use of computer becoming easier but the complexity of it's background become more and more and the general knowledge from whole of it even for expert became impossible.

    How ever imagine that you decide to write a Application to store it's host computer file structure in index files(which are in database format in background) and so the users would be able to take different index files from diffident locations and transfer them with there friends. so if you are friendly with doing all database creations with in code from your App you would have no problem But if not you would have to choose another formless solution or learning from the first.

    for any other multi document Application that use a db format to store it's documents data that is true.

    again those who knows things from a deeper side won't look the things like others and failure results appear them more clearly.

    Again your on your own and no one will force you to change your manner ,the same that no one force me to think of this view.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •