I'd like to automate the update steps below. I get stuck trying to copy the the structure ONLY of the table. Suggestions?

Update the applications unique table
• Run “create applications table” query
• Copy the applications table
• Paste applications table
o Structure Only
o Rename as applications unique
• Open applications unique in design view
• Make Comp_Inv_ID the primary key
• Make the Company field indexed – Duplicates OK
• Save; close
• Run “delete dups application table” query

There may be a better way, but if you don't get any better answers, you could use two queries:
- A Create table query.
- And a "Delete * from NewTable" query.

Not really sure what the big picture is but you could look at CreateTableDef in VBA help, this will enable you to automate the creation of tables.

Brian

I can't use the Delete, because I need to create a new table without duplicates from the one I have. The only way I know to do that is to create a new table, structure only, create a primary key and then run an append query from the first table to the second.

Other thoughts?

pj ..... did you sort this out in the end?

I have had this problem in different ways at different times and solved it using a variety of methods (all manual!). I now need a method that is totally automatic ... but precisely the equivalent of what you have detailed above ... which I originally obtained from the Access 'Help' section and which works perfectly (manually!).

I am sure I can also solve the problem using recordsets via VBA and parcel the whole thing up into a Form button procedure (I have done similar things before) :cool: ... but I don't want to have to work on the records at this detailed level.

No ... what I am trying to do is to solve this using 2 or 3 sets of SQL statements, one of which has no Access QBE equivalents - thus necessitating using VBA :D , but at a simpler level than using recordsets.

Are you (or is anybody else) interested in this?

Andre