Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52

    CHECK CONSTRAINT causing Syntax Error?

    Good morning,


    I am teaching myself SQL by reviewing articles and videos, etc..
    One thing I have gotten stuck on is the CHECK Constraint.

    I have a table, Jobs, where I am trying to verify that all start dates are BEFORE all end dates for each job.
    This is what I have:

    Code:
     ALTER TABLE Jobs
    ADD CONSTRAINT JobsCheckDates
    CHECK (StartDate < EndDate);
    But, every time I run it, Access pops up saying, Syntax error in CONSTRAINT CLAUSE. Then, it highlights CHECK on the third line.
    I have tried researching what I am doing wrong, but I can't seem to figure it out. Perhaps I need to add a column, JobsCheckDates?
    Could someone please help me out?

    Thank you,
    Aishlinn

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Are you working with SQL server? It seems you want to pass this SQL to the server. So, you would need to include it in quotes and assign it to a string variable. Then assign the variable to a passthrough query.

  3. #3
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by ItsMe View Post
    So, you would need to include it in quotes and assign it to a string variable. Then assign the variable to a passthrough query.
    Thank you! Any chance you care to elaborate on that a little bit? Because I am brand new at this and don't understand anything you just said. I'm sorry.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Where are your tables and data located? Is the data somewhere other than the Access file you created? In other words, are you connecting to an external source from your Access file?

  5. #5
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by ItsMe View Post
    Where are your tables and data located? Is the data somewhere other than the Access file you created? In other words, are you connecting to an external source from your Access file?
    No, I have tables set up in Access and I created a blank query and went into SQL view, typed in the code above, click view/run and got the error message.

    I have been watching 'Dr. Daniel Soper: Database Lessons' videos on youtube and kind of creating tables/queries along the way to follow along and this is the only one I have not been able to get working so far.

  6. #6
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Now that I am looking, I guess I may have posted this in the wrong section? I am not really sure, I'm trying to figure it all out and paying for classes isn't an option for me right now.. :/

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    OK, cool. There is this thing called Transact SQL. It is a language that is specific to Microsoft SQL Server. The thread you started is in the SQL section and would typically be for SQL server questions. What you have there is specific to SQL server.

    Aside from that, I will guess that the issue you are experiencing is that Access does not recognize, CHECK. Access has its own flavor of SQL and is not Transact SQL. For me, it is hit and miss discovering which Transact SQL keywords, operators, etc. work in Access. It seems like you just discovered one.

    In Access, most design changes are not done via SQL. Instead, we rely on Wizards and Designers. So, things like constraints are typically done via a graphical interface.

    I hope this info helps, and welcome to the forum!

  8. #8
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by ItsMe View Post
    I hope this info helps, and welcome to the forum!
    As a matter of fact this is a huge help. To be totally honest I was wondering if I was just wasting my time because its SO much easier to just manipulate the data in Design View using specific criteria. I couldn't figure out why I was doing all this work and learning all these codes when I could, WAY MORE EASILY, just click a few buttons in design view and have Access figure this out for me. I am sure that this SQL training will come in handy someday, but in the meantime I have been wondering why people would choose to sit here and hand type everything instead of clicking a few buttons to get the same result? Thank you again!

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    There is a definite benefit to understanding how to do such things. There are three basic groups of SQL. There are also others. The three common ones are DML, DDL, and DCL. Where the SQL is either, retrieving and or modifying data, modifying database structure, or defining constraints and permissions, respectively.
    http://blog.sqlauthority.com/2008/01...-and-examples/

    For instance, understanding DDL and DCL could help a software engineer when developing software. They may be provided a script that would create a database that their software would need to work with and depend on.

    Different RDBMS' will provide different tools to facilitate DML, DDL, and DCL script.

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You can perform a lot of SQL operations through VBA.
    I wonder if you tried to run something like this in VBA, if it would work.

  11. #11
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by JoeM View Post
    You can perform a lot of SQL operations through VBA.
    I wonder if you tried to run something like this in VBA, if it would work.
    I started trying to learn VBA when I first started training in Access (Steve Bishop YouTube) and a bunch of people told me to start learning SQL first. So that is what I am doing.
    So much of this is over my head at this point. I feel more confused than ever.

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    There is a lot to learn with Access, VBA, and SQL. It isn't quite as intuitive (or "as simple", in many ways) as Excel.
    The learning curve is a bit steeper. It is very tough to try to jump into advanced tasks without mastering the basics first.

  13. #13
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by JoeM View Post
    There is a lot to learn with Access, VBA, and SQL. It isn't quite as intuitive (or "as simple", in many ways) as Excel.
    The learning curve is a bit steeper. It is very tough to try to jump into advanced tasks without mastering the basics first.
    Absolutely, which is what I have been TRYING to focus my attention on. However, it is hard to decipher what is actually considered "the basics" when there is SO much to know.

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Absolutely, which is what I have been TRYING to focus my attention on. However, it is hard to decipher what is actually considered "the basics" when there is SO much to know.
    Having a good understanding of Relational Database Theory and the Rules of Normalization is essential for good database design.
    Then a good introduction to using Access would be good.
    Then, add SQL and VBA after that.

    Keep in mind, that it is NOT a quick process you can be expected to pick up and master in a week or two.

  15. #15
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by JoeM View Post
    Having a good understanding of Relational Database Theory and the Rules of Normalization is essential for good database design.
    Then a good introduction to using Access would be good.
    Then, add SQL and VBA after that.

    Keep in mind, that it is NOT a quick process you can be expected to pick up and master in a week or two.
    I am so glad you said that because this is EXACTLY what I have been doing and taking ridiculous notes, probably too many. But for now, I am on SQL and following along as best I can. I wish there was some kind of quiz online that I could test my skills. Some way to assess whether I am ready to move on to the next step or not. But as far as I can tell there isnt, so I am kind of just crossing my fingers and hoping for the best. At least if there was an objective and I had to create.. this that and the other thing.. to see if I could do it. THEN move on. If you know of anything like that, please let me know. Thank you for your help!!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-26-2014, 12:52 PM
  2. Replies: 4
    Last Post: 02-03-2014, 12:16 PM
  3. Replies: 2
    Last Post: 01-11-2014, 03:56 PM
  4. Access SQL Syntax For Creating a Table With a Default Constraint
    By crystalclear in forum Database Design
    Replies: 3
    Last Post: 09-22-2013, 02:24 AM
  5. Replies: 5
    Last Post: 12-15-2011, 11:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums