• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 997
  • Last Modified:

Syntax error in constraint clause

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
janism747
Asked:
janism747
  • 7
  • 4
2 Solutions
 
Simon BallCommented:
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
 
Simon BallCommented:
and then create the new not null constraint.
0
 
Simon BallCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
janism747Author Commented:
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
 
Simon BallCommented:
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
 
Simon BallCommented:
so it should work if you use ado not implied dao using runsql..
0
 
janism747Author Commented:
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
 
Simon BallCommented:
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
 
janism747Author Commented:
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
 
janism747Author Commented:
Syntax was incorrect too. Not null works only when creating or changing column. Otherwise you have to use CHECK constraint.
0
 
Simon BallCommented:
excellent follow up.

that check part is useful to know.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now