Dim db281_503 As Database
Set db281_503 = OpenDatabase("M:\503_281.mdb")
db281_503.Execute "CREATE UNIQUE INDEX PackNo ON tbl281tmp (PackNo) WITH PRIMARY"
The goal is to avoid adding a field, so I've used a pre-existing field called PackNo hoping that I'm not going to overwrite it with the dreaded sequential integer set.
Next.. How do I tell Access to run the Sub? Maybe the "On Open" property?
<<Brain/typing disconnect - this is a query, ignore the On Open comment>>
OK. You're 99% there... In your macro after running the make table query insert a RunCode action and point it to your new Function (I believe it has to be defined as a function) MkTblPK. That should do it...
I can't add the module in Expression Builder. There's no thing in the Value column. I went back to VB Editor, and it won't compile. I must have missed something important in the Help file, maybe? Where it defines "Database", maybe?
We must have posted the DAO solution at the same time....
I went back and used your code instead. Still needed the DAO reference, but it worked.
Unfortunately, my Make Table Query is creating duplicate values to start with. <ARGH!>
Well you could always create the table first (with the primary key), delete the records, append the records and ignore the errors....
or use a DISTINCT in your query if it's producing duplicate records... with duplicate keys you might have to do a little extra work to clean things up.
A shortcut for the copy/delete approach... I usually use copy/paste to create a template by choosing "Structure Only". You can add/remove keys, change field types, etc. depnding on your needs. Then I copy the template table to a new name and append the records without having to delete the records repeatedly during design.