How to refrence primary key and forgine key

[tony007]

Hi guys. could any one help refrence primary key and forgine key in access db using vba code and show it in message box.thanks

 

[RuralGuy]

Primary and Foreign Keys are just regular fields in a table. You would reference them the same way you reference any field in a table. I expect there is more to your question that will now appear.

 

[modest]

I think he's asking.. how can you tell if a field is a PK or a FK

 

[tony007]

I think he's asking.. how can you tell if a field is a PK or a FK

THank u guys for u reply. I want to use vba to identify and print the pk and fk of tables in my db . I be happy if some one show me how.Thanks

 

[RuralGuy]

Look in the VBA Help system for Index Collection (DAO). There are examples of code in there to print out almost anything you want.

 

[Pat Hartman]

Paste this into a standard code module. You may have to adjust the path to the northwind.mdb if yours is in a different location. Or, you can modify the code to reference objects in your own database. The code simply lists all the properties of the tabledef, querydef, recordset, relation, and index objects.

Sub FieldPropertiesInTableDef()

   Dim dbsNorthwind As DAO.Database
   Dim rstEmployees As DAO.Recordset
   Dim fldTableDef As DAO.Field
   Dim fldQueryDef As DAO.Field
   Dim fldRecordset As DAO.Field
   Dim fldRelation As DAO.Field
   Dim fldIndex As DAO.Field
   Dim prpLoop As DAO.Property

   Set dbsNorthwind = OpenDatabase("C:\Program Files\Microsoft Office\OFFICE11\OFFICE11\SAMPLES\Northwind.mdb")
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees")

   ' Assign a Field object from different Fields
   ' collections to object variables.
   Set fldTableDef = _
      dbsNorthwind.TableDefs(0).Fields(0)
   Set fldQueryDef = dbsNorthwind.QueryDefs(0).Fields(0)
   Set fldRecordset = rstEmployees.Fields(0)
   Set fldRelation = dbsNorthwind.Relations(0).Fields(0)
   Set fldIndex = _
      dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)

   ' Print report.
   FieldOutput "TableDef", fldTableDef
   FieldOutput "QueryDef", fldQueryDef
   FieldOutput "Recordset", fldRecordset
   FieldOutput "Relation", fldRelation
   FieldOutput "Index", fldIndex

   rstEmployees.Close
   dbsNorthwind.Close

End Sub

 

[tony007]

Thanks for u reply. When i try to run it in module windows . I get the following error :

compile error
sub or function not defind
and it points to the following window

  ' Print report.
   FieldOutput "TableDef", fldTableDef

further more if i want run it for my current db what should i do?Thanks

 

[Pat Hartman]

Whoops:

Sub FieldOutput(strTemp As String, fldTemp As Field)
   ' Report function for FieldX.

   Dim prpLoop As Property

   Debug.Print "Valid Field properties in " & strTemp

   ' Enumerate Properties collection of passed Field
   ' object.
   For Each prpLoop In fldTemp.Properties
      ' Some properties are invalid in certain
      ' contexts (the Value property in the Fields
      ' collection of a TableDef for example). Any
      ' attempt to use an invalid property will
      ' trigger an error.
      On Error Resume Next
      Debug.Print "  " & prpLoop.Name & " = " & _
         prpLoop.Value
      On Error GoTo 0
   Next prpLoop

End Sub

 

[tony007]

Thanks again. could u tell me where to place this code ? should i add replace it with yoru first coder or add it ? Thanks

 

[Pat Hartman]

I should have been more clear. This code won't do what you want to do. This code will show you the names of the properties you need to work with. You will need to write code to modify the property you want to modify. I don't have a sample that I can post or I would have posted that.