Droping constraint

[Shnizles]

Hi , all
thank you for your time in advance ,
im trying to delete a constraint using sql query ,
i found that this should be the right syntax for that task:
ALTER TABLE Table2 DROP CONSTRAINT Relation1

the problem is i made the constraints using the access 2007 designer ,
and i got no idea what is the constraint index / name ("Relation1" in the
query) ,
i couldnt find it anywhere ,
please tell me how can i find the name of my tables relations , so i could
delete them if needed with that query above thanks.

 

[Josef Poetzl]

hi,

im trying to delete a constraint using sql query ,
i found that this should be the right syntax for that task:
ALTER TABLE Table2 DROP CONSTRAINT Relation1

the problem is i made the constraints using the access 2007 designer ,
and i got no idea what is the constraint index / name ("Relation1" in the
query) ,
i couldnt find it anywhere ,
please tell me how can i find the name of my tables relations , so i could
delete them if needed with that query above thanks.

some examples:

'1. DAO.Relation
Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = CurrentDb
For Each rel In db.Relations
Debug.Print rel.Name, rel.ForeignTable, rel.Table
Next

'2. MSysRelationships
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("select * from MSysRelationships")
Do While Not rst.EOF
Debug.Print rst.Fields("szRelationship"), _
rst.Fields("szObject"), rst.Fields("szReferencedObject")
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

'3. DAO.TableDef / Index
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

For Each tdf In db.TableDefs
For Each idx In tdf.Indexes
If idx.Foreign Then
Debug.Print idx.Name, tdf.Name
End If
Next
Next

'4. ADODB Schema
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = cnn.OpenSchema(adSchemaForeignKeys, _
Array(Empty, Empty, Empty, Empty, Empty, "Table2"))
' ^^PK_TABLE_NAME ^^ FK_TABLE_NAME
Do While Not rst.EOF
Debug.Print rst.Fields("FK_NAME"), _
rst.Fields("FK_TABLE_NAME"), rst.Fields("PK_TABLE_NAME")
rst.MoveNext
Loop
rst.Close



HTH
Josef

 

[Shnizles]

hey ,
thank you for the reply ,
but i was looking for a simple sql query syntax not code or anything
like the query i mentioned in the first post ,
do you know anything about it?

 

[Josef Poetzl]

hi,

but i was looking for a simple sql query syntax not code or anything
like the query i mentioned in the first post ,
do you know anything about it?

something similar as:
select szRelationship, szReferencedObject
from MSysRelationships
where szObject = 'Table2'


kind regards,
Josef

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Doood,

Put one of the code examples (I recommend the ADO example) in a VBA Sub
& run it in the Debug Window (ctrl-G). It will display the names of the
constraints in the Debug window. Take the name & place it in the ALTER
TABLE command you posted in your first post.

Here, I made a Sub for you from Josef's examples. Just paste it into a
VBA module & run it, like this (enter the following command in the Debug
window):

ConstraintNamesADO "Orders" <hit the Enter key>

Use your table name in place of "Orders."

Public Sub ConstraintNamesADO(strTable As String)

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = cnn.OpenSchema(adSchemaForeignKeys, _
Array(Empty, Empty, Empty, Empty, Empty, strTable))
' ^^PK_TABLE_NAME ^^ FK_TABLE_NAME
Debug.Print "FK_NAME", , "FK_TABLE_NAME", "PK_TABLE_NAME"
Do While Not rst.EOF
Debug.Print rst.Fields("FK_NAME"), _
rst.Fields("FK_TABLE_NAME"), rst.Fields("PK_TABLE_NAME")
rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set cnn = Nothing

End Sub


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbgh3oechKqOuFEgEQL0bwCg9Qk+gm5Fbskrx0vibHsMgJRA9bYAoOPR
H1kaQ2sVnUxY+N2TafgO330e
=Zgbl
-----END PGP SIGNATURE-----

 

[Shnizles]

Thanks for the help ,
im able to view all my db constraints now ,
i get a table looks like this:

ccolumn grbit icolumn szColumn szObject szReferencedColumn szReferencedObject szRelationship
1 4352 0 agentId AgentsConstraints agentNum Agent AgentAgentsConstraint
1 4352 0 agentId AgentsWeeklyConstraints agentNum Agent AgentAgentsWeeklyConstraints
1 4096 0 hId HolidayDates id Holiday HolidayHolidayDate
1 4352 0 acId AgentsWeeklyConstraints id ShiftsScheduling ShiftsSchedulingAgentsWeeklyConstraints
1 4352 0 teamNumber Agent teamNumber Team TeamAgent
1 4096 0 calendarId Holiday id Calendar CalendarHoliday


now i have tried to execute the following sql querys:
ALTER TABLE Holiday DROP CONSTRAINT CalendarHoliday
ALTER TABLE Calendar DROP CONSTRAINT CalendarHoliday


both cases ,i get a messege saying:
"object invalid or no longer set" which is not true i can still see the
relationship that im trying to drop.

whats wrong with my query?

 

[Josef Poetzl]

hi,

im able to view all my db constraints now ,
i get a table looks like this:

ccolumn grbit icolumn szColumn szObject szReferencedColumn szReferencedObject szRelationship
1 4352 0 agentId AgentsConstraints agentNum Agent AgentAgentsConstraints
1 4352 0 agentId AgentsWeeklyConstraints agentNum Agent AgentAgentsWeeklyConstraints
1 4096 0 hId HolidayDates id Holiday HolidayHolidayDates
1 4352 0 acId AgentsWeeklyConstraints id ShiftsScheduling ShiftsSchedulingAgentsWeeklyConstraints
1 4352 0 teamNumber Agent teamNumber Team TeamAgent
1 4096 0 calendarId Holiday id Calendar CalendarHoliday

now i have tried to execute the following sql querys:
ALTER TABLE Holiday DROP CONSTRAINT CalendarHoliday
ALTER TABLE Calendar DROP CONSTRAINT CalendarHoliday

both cases ,i get a messege saying:
"object invalid or no longer set" which is not true i can still see the
relationship that im trying to drop.

whats wrong with my query?

The first query is right. How do you execute the query?

VBA:
dim strSQL as string
strSQL = "ALTER TABLE Holiday DROP CONSTRAINT CalendarHoliday"
currentdb.execute strSQL, dbfailonerror


kind regards,
Josef

 

[Josef Poetzl]

hi,

ALTER TABLE Holiday
ADD CONSTRAINT
CalendarHoliday FOREIGN KEY ([calendarid]) REFERENCES Calendar ([id]) ON
DELETE CASCADE

and i get syntax error although i followed the exact syntax as suggested in
the ms access guides , how ever when i remove the " ON DELETE CASCADE"
part the relationship is being added ok ,

whats wrong in the query how can i enable cascade delete to my relationship

This is a problem with DAO.

dim strSQL as string
strSQL = "ALTER TABLE Holiday ...."

DAO:
currentdb.execute strSQL, dbfailonerror
=> error

ADODB:
currentproject.connection.execute strSQL
=> ok


kind regards,
Josef

 

[Shnizles]

Im using vb.net to excute the query ,
i get this error with OLEDB and ADODB connectors ,
they throw same error,
any other guess what it could be?

hi,

ALTER TABLE Holiday
ADD CONSTRAINT
CalendarHoliday FOREIGN KEY ([calendarid]) REFERENCES Calendar ([id]) ON
DELETE CASCADE

and i get syntax error although i followed the exact syntax as suggested in
the ms access guides , how ever when i remove the " ON DELETE CASCADE"
part the relationship is being added ok ,

whats wrong in the query how can i enable cascade delete to my relationship

This is a problem with DAO.

dim strSQL as string
strSQL = "ALTER TABLE Holiday ...."

DAO:
currentdb.execute strSQL, dbfailonerror
=> error

ADODB:
currentproject.connection.execute strSQL
=> ok


kind regards,
Josef