asked on

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

Si Ball

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

Si Ball

and then create the new not null constraint.

SOLUTION

Si Ball

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

janism747

ASKER

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

Si Ball

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

Si Ball

so it should work if you use ado not implied dao using runsql..

janism747

ASKER

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

ASKER CERTIFIED SOLUTION

Si Ball

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

janism747

ASKER

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

janism747

ASKER

Syntax was incorrect too. Not null works only when creating or changing column. Otherwise you have to use CHECK constraint.

Si Ball

excellent follow up.

that check part is useful to know.