Set Primary Key on existing tbl via VBA

[Pendragon]

I'm using Access 03. I'm coding a procedure to select IndividualIDs for a
Random Drug Test. I first use a make-table query to filter active drivers
from the master list into tempActiveDrivers. Field 1 is IndividualID. In
the master list, IndividualID is the primary key.

In my random number generation sequence (a modification of Article 170986),
there is code that tests to see if the random number generated exists in my
recordset, (rsDrivers) which was set as rsDrivers =
dbsRandom.OpenRecordset("tempActiveDrivers", dbOpenTable):

....
rsDrivers.Index = "PrimaryKey"
rsDrivers.Seek "=", lngGuess 'lngGuess is the random number generated
....

When I test the code, the error message is " 'PrimaryKey' is not an index in
this table."

The question, then, is how do I set IndividualsID in tempActiveDrivers to be
a primary key without manually opening the table in design view? Or, rather
than using a make-table query, is there a better method to create a temporary
table with the needed information before executing the random number sequence.

Your wisdom is appreciated!!

Ross aka pendragon

 

[Brendan Reynolds]

Public Sub CreateIndex()

Dim strSQL As String
strSQL = "CREATE INDEX PrimaryKey ON tblTest (TestID) WITH PRIMARY"
CurrentProject.Connection.Execute strSQL, , adCmdText Or
adExecuteNoRecords

End Sub

Replace 'tblTest' with your table name, and 'TestID' with your field
name(s).