Setting the PK in vb

D

Douglas J. Steele

Realistically, fields can't be primary or foreign keys. Rather, fields can
belong to indexes, and the Index can be marked as Primary. As to foreign
keys, you'd create a Relation, and add the appropriate field(s) to the
Relation's Fields collection.

To do this through DAO, look at the CreateIndex and CreateRelation methods.
 
6

'69 Camaro

Hi, Joel.

Actually, it's less work to add indexes to tables (not to fields) using SQL
-- if you know the syntax. But doing it in VBA is still pretty simple, too.
I'll show you one of each. Try:

Public Sub createPKIndex()

On Error GoTo ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set db = CurrentDb()
Set tbl = db.TableDefs("tblDepartments")
Set idx = tbl.createIndex("PrimaryKey")

Set fld = idx.CreateField("DeptID")
idx.Fields.Append fld
idx.Primary = True
tbl.Indexes.Append idx

CleanUp:

Set fld = Nothing
Set idx = Nothing
Set tbl = Nothing
Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in createPKIndex( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub


Public Sub createFKIndex()

On Error GoTo ErrHandler

CurrentDb().Execute "ALTER TABLE tblPersonnel " & _
"ADD CONSTRAINT Personnel_Depts_FK " & _
"FOREIGN KEY (DeptID) " & _
"REFERENCES tblDepartments (DeptID);", dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in createFKIndex( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where tblDepartments and tblPersonnel are tables that already exist,
and both contain the field DeptID, which is of the same data type in both
tables.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Top