Using VBA to set the primary key in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm designing sub-procedure which uses runs a few SQL statements to make a
new table, copy the table, empty the table of data and then append data from
a table to the copy - the rationale being to delete duplicate entries.

The problem I have is that once I have created and then emptied the new
table I want to define a field in it ([tblUnique2].[UniqueID]) as the
PrimaryKey and my knowledge and the Help files have deserted me!

Can anyone show me how to do this?
Joe
 
I'm not sure I understand why you need to create a new table.

To create the Primary Key, you can use:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("tblUnique2")
Set idxCurr = tdfCurr.CreateIndex("PrimaryKey")
idxCurr.Fields.Append idxCurr.CreateField("UniqueID")
idxCurr.Primary = True
tdfCurr.Indexes.Append idxCurr
 
Back
Top