How to set a field in a table as a Primary Key in VBA code?

[Rgmiket]

I have a make table query creating a copy of an existing table. This temp
table is used as the table in a datasheet view which allows a user to edit
values then close the form and gives the choice to update the DB or not with
the changes made.

In order to do this I need to compare the two tables(original and the temp)
Make any deletions/additions/modifications if the user selects "Yes" The
problem is in order to use the temp.seek command an index/field has to be
selected as the search field in order to do this in a public function I am
passing in the 2 record sets then looping through them as shown in the
following code...

If anyone can enlighten me on
1. how do you set a primaryKey on a field in a table in Code...here is the
formload..
strTableName = "tblAllports"
If (IsTableExisting(strTableName)) Then 'if the table exists
Me.RecordSource = ""
DoCmd.DeleteObject acTable, strTableName
End If
CurrentDb.QueryDefs("qryAllPortsMakeTable").Execute
Me.RecordSource = strTableName
' Set CurrentDb.TableDefs(strTableName) = "PrimaryKey" 'this is the line
that does not work
bChangeFlag = False

2. In the code currently the three tables I have tested with all have the
PrimaryKey field as the first field in the table(this will not always be the
case) How do I get an actual field number so regardless of which field is the
primary I can pull the value to search on from it?

here is a chunk of code for the AddDelete subroutine to date...I plan to do
the deletions then do a single loop for Modifications and Additions..but have
it broken out currently for clarity and debug. As you can see I have hard
coded the 0 into the .seek call at this time...but really this needs to be a
variable dependent on which ever field is the "primary key" field.

'This subroutine adds and deletes the records in the destination table that
have been added
'and deleted from the source table
Private Sub AddDelete(recDst As Recordset, recSrc As Recordset)
Dim recToDelete As Recordset, recToAdd As Recordset
Dim lRecCnt As Long
Dim fld As Integer
'Deletions
For lRecCnt = 0 To (recDst.RecordCount - 1)
With recSrc
.Index = "PrimaryKey" 'this is why I need to set temp tables
Primary Key
.Seek "=", recDst.Fields(0).Value
If .NoMatch Then
recDst.Delete
recDst.Update
End If
recDst.MoveNext
End With
Next lRecCnt
'Additions
recSrc.MoveFirst
For lRecCnt = 0 To (recSrc.RecordCount - 1)
With recDst
.Index = "PrimaryKey"
.Seek "=", recSrc.Fields(0).Value
If .NoMatch Then
.AddNew
For fld = 0 To .Fields.Count - 1
.Fields(fld) = recSrc.Fields(fld)
Next fld
.Update
End If
End With
recSrc.MoveNext
Next lRecCnt
End Sub

 

[Douglas J. Steele]

You don't make a field a primary key: you make an index that contains the
field, and make that index the primary key.

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxPrimary As DAO.Index

Set dbCurr = CurrentDb
Set tdfCurr = dbCurr.TableDefs(strTableName)
Set idxPrimary = tdfCurr.CreateIndex("PrimaryKey")
idxPrimary.Fields.Append idxPrimary.CreateFields("NameOfField")
idxPrimary.Primary = True
tdfCurr.Indexes.Append idxPrimary

Note, though, that you could always use the FindFirst method rather than the
Seek method.

And realistically, adding and deleting tables in your front-end database
isn't a great idea: it leads to excessive bloating. Why not consider putting
your temporary tables in a temporary database? Tony Toews has an example at
http://www.granite.ab.ca/access/temptables.htm