Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Syntax error in constraint clause

Posted on 2011-05-03
11
Medium Priority
?
952 Views
Last Modified: 2012-05-11
Hi, I use MS Access 2007. I have to open password protected database and add constraint NOT NULL. I am using this code but it gives syntax error.
I cannot use ALTER COLUMN clause because the filed is part of relationships.
Set accRT = CreateObject("Access.Application")
accRT.OpenCurrentDatabase path, , "password"

accRT.DoCmd.RunSQL "ALTER TABLE table1 ADD CONSTRAINT repnn2 NOT NULL (Customer);"

accRT.Quit
Set accRT = Nothing

Open in new window

0
Comment
Question by:janism747
  • 7
  • 4
11 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35510790
Whats the actual error you are getting?

you could remove the relationship in the mdb?

http://support.microsoft.com/kb/291539

ALTER TABLE Table2 DROP CONSTRAINT Relation1
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35510800
and then create the new not null constraint.
0
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 2000 total points
ID: 35510838
or here is some dao i found in google:

http://allenbrowne.com/func-dao.html#DeleteRelationDAO




Function DeleteRelationDAO()
    DBEngine(0)(0).Relations.Delete "tblDaoContractortblDaoBooking"
End Function

and then create a new one:

Function CreateRelationDAO()
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
   
    'Initialize
    Set db = CurrentDb()
   
    'Create a new relation.
    Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")
   
    'Define its properties.
    With rel
        'Specify the primary table.
        .Table = "tblDaoContractor"
        'Specify the related table.
        .ForeignTable = "tblDaoBooking"
        'Specify attributes for cascading updates and deletes.
        .Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
       
        'Add the fields to the relation.
        'Field name in primary table.
        Set fld = .CreateField("ContractorID")
        'Field name in related table.
        fld.ForeignName = "ContractorID"
        'Append the field.
        .Fields.Append fld
       
        'Repeat for other fields if a multi-field relation.
    End With
   
    'Save the newly defined relation to the Relations collection.
    db.Relations.Append rel
   
    'Clean up
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
    Debug.Print "Relation created."
End Function
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:janism747
ID: 35511267
See error in attachment.
Isn't there a way without dropping relations? I just hope there is a more simple workarround for this problem. Is deleting relations, changing field and adding relations the only way?
err.png
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35511406
googled that:
You entered an SQL statement that includes an invalid CONSTRAINT clause.

Possible causes:

A reserved word or argument name is misspelled or missing.
Punctuation is incorrect.

another article shows that ADO code to make constraints works, but DAO does not:

http://bytes.com/topic/access/answers/791223-syntax-error-constraint-clause

Public Sub CreateTableWithDRIConstraint()
 
     'ADO connection performs operation flawlessly
     CurrentProject.Connection.Execute _
         "CREATE TABLE tblChild (keyID Long, CONSTRAINT FK_keyID FOREIGN KEY (keyID)" & _
         " REFERENCES tbl(keyID) ON DELETE CASCADE);"
     'DAO connection throws an error
     CurrentDb.Connection.Execute _
         "CREATE TABLE tblChild (keyID Long, CONSTRAINT FK_keyID FOREIGN KEY (keyID)" & _
         " REFERENCES tbl(keyID) ON DELETE CASCADE);"
 
 End Sub

this article shows how to do adodb db connection:

http://waltermilner.com/downloads/VBA/ADO%20VBA%20Programming%20in%20Access.ppt
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35511409
so it should work if you use ado not implied dao using runsql..
0
 

Author Comment

by:janism747
ID: 35511587
I'm not sure I can connect and open password protected access 2007 file using ADO because it's not mdb but accdb format.
I'll give a try recreating relations and let you know results.
Thanks for helping.
Dim cn As New ADODB.Connection
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=C:\DataTb.accdb"
cn.Properties("Jet OLEDB:Database Password") = "password"
cn.Open

Open in new window

err1.png
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 35513243
sorry i didn't realise it was accdb.  don;t know anything about that format.


http://www.access-programmers.co.uk/forums/showthread.php?t=128982

recommends using this connection:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabaseName.accdb;"
0
 

Author Comment

by:janism747
ID: 35684158
Yes, connection works fine now. I changed syntax of constraint clause and it works ok now too. CHECK constraint does not work with DAO but works with ADO.
BTW, recreation of relations was good idea too.
Dim cn As New ADODB.Connection
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.ConnectionString = "Data Source=C:\DataTb.accdb"
cn.Properties("Jet OLEDB:Database Password") = "password"
cn.Open
cn.Execute "ALTER TABLE Repo ADD CONSTRAINT repnn2 CHECK(Instrument Is Not Null);"
cn.Close
Set cn = Nothing

Open in new window

0
 

Author Closing Comment

by:janism747
ID: 35685643
Syntax was incorrect too. Not null works only when creating or changing column. Otherwise you have to use CHECK constraint.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35687579
excellent follow up.

that check part is useful to know.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question