By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

extracting the constraints

P: n/a
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

Jul 25 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Jul 25 '06 #2

P: n/a

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

Jul 25 '06 #3

P: n/a
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
Jul 26 '06 #4

P: n/a
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.

Jul 26 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.