Read constraint from table (1 Viewer)

Petr Danes

Registered User.
Local time
Today, 11:59
Joined
Aug 4, 2010
Messages
150
I have a constraint on a table, which I created in Jet SQL, using Add Constraint. I don't know what it says now. How do I read the text of the constraint? The ADOX library seemed the most promising for this, but I have looked at the ADOX object, and see nothing.
 

Cronk

Registered User.
Local time
Today, 21:59
Joined
Jul 4, 2013
Messages
2,770
Open the Access table in design view and click on the Indexes button to show the constraints (indexes and properties).
 

Petr Danes

Registered User.
Local time
Today, 11:59
Joined
Aug 4, 2010
Messages
150
That just lists indexes, no? Are there not check constraints possible for the table that don't show up in the GUI?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:59
Joined
Feb 28, 2001
Messages
26,996
For Access, you can have several kinds of constraints.

1. In Table Design view, if you select a field, then at the bottom of the table design panel is a list of properties including a field constraint such as ">0" or some other simple math. Math in this level of constraint ONLY applies to the one field that is selected. You cannot have a cross-field constraint easily in that property. (Not impossible, just not easy.) There is also the "Required" constraint (can't be left blank) and the "Indexed" constraint that can include "Unique."

2. In Table Design view, you can see indexes. It is possible to have index constraints such as "Unique" and you can have compound constraints that require a combination of fields in the table to be unique even though individually the fields are not unique. This most often occurs when you have compound keys.

3. In the Relationships window you can have cross-table constraints via Relational Integrity such that a value has to exist in Table X before you can store a dependent entry in table Y; essentially, parent/child requirements where a parent must exist in order to have a child. (Kind of like in real life...)

4. In Table Design view you can define table-level data macros that are used to enforce a constraint. They look like macros but are not managed from the Macro collection. Since RunCode is a possible Macro action, you might be able to put complex VBA constraints there. Look up Data Macro to see more. They can be event based or not, depending on how they are set up.

5. The trickiest constraints come from data entry forms where VBA code in the form's Class Module accepts or rejects a given entry attempt.

Of these, the hardest to find is the VBA code in a class module because it is not easily seen from Table Design view. It only applies if your database is secured in a way that does not allow end users to see tables directly but rather they can only access the tables through appropriate (non-datasheet) forms.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:59
Joined
Feb 28, 2001
Messages
26,996
Thanks, Sonic8. I've never used one that shows up in the Connection column. I thought that DDL would place its constraints in the FieldDef items associated with the table but your link shows me a feature I never used, perhaps because I never used DDL in that particular way.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:59
Joined
Sep 12, 2006
Messages
15,613
DAO might be your friend.

I expect this sort of thing is stored as a property of the DAO tabledef object. It's probably a created property. You would have to iterate all the properties to find the one you are looking for. After you know the property you are looking for you can use it directly.

Code:
dim  prp as property
for each prp in currentdb.tabledefs("mytable").properties
    msgbox prp.name & "  " & prp.value
    'or use code to write this to a text file, or store it in a local table
next

not sure whether prp.value syntax is correct, but that sort of thing.
 

Cronk

Registered User.
Local time
Today, 21:59
Joined
Jul 4, 2013
Messages
2,770
There is a table property called "ValidationRule" that holds any table level contstraint.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:59
Joined
Sep 12, 2006
Messages
15,613
going on from what cronk said, then you are proably looking for a property of the field in the table.

Currentdb.tabledefs("MyTable").fields("MyField").properties("PropertyName") - but it might not be obvious exactly what the property name is.
 

Cronk

Registered User.
Local time
Today, 21:59
Joined
Jul 4, 2013
Messages
2,770
No, you need to check the table property "ValidationRule" as the rule is table based, not single field based.
eg
Code:
debug.print currentdb.tabledefs("MyTable").properties("ValidationRule")
==> [Amount]>=[MinimumValue]
This if for the case where there was a table with fields Amount and MinimumValue, and the ValidationRule in the table properties set to
[Amount]>=[MinimumValue]

AFAIK, you can't set a validation rule involving reference to another field in the field's validation rule.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:59
Joined
Sep 12, 2006
Messages
15,613
@cronk

that makes sense. I hadn't checked - I am just used to handling DAO properties.
 

Users who are viewing this thread

Top Bottom