extracting the constraints

[srikanthr]

how do i get the constraints associated with a table. is there any
system table which stores the constraint details. Atleast how do I know
if a column is nullable or not

[Allen Browne]

A column is nullable if its Required property is False:
Currentdb.TableDefs("MyTable").Fields("MyField").R equired
To see if it can accept a zero-length string:
Currentdb.TableDefs("MyTable").Fields("MyField").A llowZeroLength

Other contraints are stored in the Indexes of the table. For example the
Primary Key has its Primary property True:
Currentdb.TableDefs("MyTable").Indexes(0).Primary

For an example of looping through the Fields of the TableDef, listing names
and examining a property (Description in this case), see:
http://allenbrowne.com/func-06.html

For a utility that examines an Access database and identifies tables that
lack a primary key, foreign keys are that nullable, and so on, see
Database Issue Checker Utility
at:
http://allenbrowne.com/AppIssueChecker.html
The code might be informative if you are seeking to programmatically examine
the structure of a database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<sr*******@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

how do i get the constraints associated with a table. is there any
system table which stores the constraint details. Atleast how do I know
if a column is nullable or not

[Lyle Fairfield]

sr*******@gmail.com wrote:

how do i get the constraints associated with a table. is there any
system table which stores the constraint details. Atleast how do I know
if a column is nullable or not

You could run these and see if one gives you the info you need:

Option Compare Database
Option Explicit

Sub temp1()
Dim f As ADODB.Field
Dim r As ADODB.Recordset
Set r =
CurrentProject.Connection.OpenSchema(adSchemaRefer entialConstraints)
With r
Debug.Print .Fields.Count
While Not .EOF
Debug.Print "*****"
For Each f In .Fields
If Not IsNull(f.Value) Then
Debug.Print f.Name & ": " & f.Value
End If
Next f
.MoveNext
Wend
End With
End Sub

Sub temp2()
Dim f As ADODB.Field
Dim r As ADODB.Recordset
Set r =
CurrentProject.Connection.OpenSchema(adSchemaCheck Constraints)
With r
Debug.Print .Fields.Count
While Not .EOF
Debug.Print "*****"
For Each f In .Fields
If Not IsNull(f.Value) Then
Debug.Print f.Name & ": " & f.Value
End If
Next f
.MoveNext
Wend
End With
End Sub

Sub temp3()
Dim f As ADODB.Field
Dim r As ADODB.Recordset
Set r =
CurrentProject.Connection.OpenSchema(adSchemaConst raintColumnUsage)
With r
Debug.Print .Fields.Count
While Not .EOF
Debug.Print "*****"
For Each f In .Fields
If Not IsNull(f.Value) Then
Debug.Print f.Name & ": " & f.Value
End If
Next f
.MoveNext
Wend
End With
End Sub

[srikanthr]

Thanks a lot for the info. I guess the solutions given are through ADO.
Iam connecting through java jdbcodbc bridge. hence it will be helpful
if i can access any table and query NOT NULL constants etc.

Lyle Fairfield wrote:

sr*******@gmail.com wrote:

how do i get the constraints associated with a table. is there any
system table which stores the constraint details. Atleast how do I know
if a column is nullable or not

You could run these and see if one gives you the info you need:

Option Compare Database
Option Explicit

Sub temp1()
Dim f As ADODB.Field
Dim r As ADODB.Recordset
Set r =
CurrentProject.Connection.OpenSchema(adSchemaRefer entialConstraints)
With r
Debug.Print .Fields.Count
While Not .EOF
Debug.Print "*****"
For Each f In .Fields
If Not IsNull(f.Value) Then
Debug.Print f.Name & ": " & f.Value
End If
Next f
.MoveNext
Wend
End With
End Sub

Sub temp2()
Dim f As ADODB.Field
Dim r As ADODB.Recordset
Set r =
CurrentProject.Connection.OpenSchema(adSchemaCheck Constraints)
With r
Debug.Print .Fields.Count
While Not .EOF
Debug.Print "*****"
For Each f In .Fields
If Not IsNull(f.Value) Then
Debug.Print f.Name & ": " & f.Value
End If
Next f
.MoveNext
Wend
End With
End Sub

Sub temp3()
Dim f As ADODB.Field
Dim r As ADODB.Recordset
Set r =
CurrentProject.Connection.OpenSchema(adSchemaConst raintColumnUsage)
With r
Debug.Print .Fields.Count
While Not .EOF
Debug.Print "*****"
For Each f In .Fields
If Not IsNull(f.Value) Then
Debug.Print f.Name & ": " & f.Value
End If
Next f
.MoveNext
Wend
End With
End Sub

[Lyle Fairfield]

sr*******@gmail.com wrote:

Thanks a lot for the info. I guess the solutions given are through ADO.
Iam connecting through java jdbcodbc bridge. hence it will be helpful
if i can access any table and query NOT NULL constants etc.

Perhaps someone else will find my answers helpful. I am glad they are
of no use to you.