count and constraints (1 Viewer)

Hi,

Can anyone please tell me if we can a select statement to find the constraints. To be clear, will this work in Access, it works in oracle

SELECT u2.table_name
FROM user_constraints u1, user_constraints u2
WHERE u1.table_name = 'PART'
AND u2.r_constraint_name = u1.constraint_name


And also if I want to find out the count of the constraints, or say the count of records in a table is this the correct way, because it is not working for me.

Dim StrSQL As String

StrSQL = " SELECT COUNT(*)"
StrSQL = StrSQL & " FROM PART "

Me!Cons_Number_txt = StrSQL


Me!Cons_Number_txt is a text field in the form and I want to display the count of the record sin PART table in that particular field. Please let me know how I can do that.

Thanks

Me!Cons_Number_txt = DCOUNT("Primary Key Field Name","PART")
Count in access requires a field name, not the good astrik. I usually just use the primary key to function the same.

SELECT u2.table_name
FROM user_constraints u1
Inner Join user_constraints u2 as
u2.r_constraint_name = u1.constraint_name
WHERE u1.table_name = 'PART'

Access uses a new version (STD) of sql, alibet heavley altered than Oracle.

Hi,

Thanks for your reply.

I have one more question, when we use the user_constrains , I get a set of tables that are related, how do I read them one by one to do a dynamic deletion on each table

Like say the code that you posted before returns 4 tables and they are

plant
supplier
order
sales

Now I want to delete all these tables one by one using a delete SQL query. like
StrSQL = "Delete FROM <table from the user_constraints>"
'StrSQL = StrSQL & " WHERE PLANT_CODE = '" & PT_CODE & "' "

How I do that? Can I use array's here, if so how do I do that?


Thanks once again

I would use IN with a subselect, just like in Oracle. If you needed to use a cursor type navigation you need to revert to VBA and ADO.