How do I set a Primary Key on a Make Table SQL Statement

[ChaseMIS]

The subject of this message sums up what I am trying to do. In the interest of making a process foolproof, a previous programmer designed the process so that each run deletes the necessary table and then recreates it using a make table query written in the code. The idea behind this was to render any multiple runs of the process harmless. Recently, we have started to see duplicate data and this is causing real problems. One of the fields in the table is a unique identifier and could be used a the primary key but I don't know how to include that in the SQL statement. The help file has been less than helpful. Any assistance would be greatly appreciated.

Thanks,
Derek

 

[pcdaveh]

I'm not going to do all the work for you but here is a small example: you can look up CREATE UNIQUE INDEX



DoCmd.OpenQuery "MyQuery"
db.Execute "CREATE UNIQUE INDEX MyIndexName " _
& "ON MyTableName(FieldName) " _
& "WITH DISALLOW NULL;"