Define Primary Key with code with Make Table Qry 1

[jdttek]

I have a Make Table Query that I need to run daily. The Query deletes the existing table and replaces it with the new one each time. I can't get the primary key to stick, so I need to re-set it manually each time. Can I set the Primary key on the new table with code each time the Make Table query is run? Thanks

JDTTEK

 

[Steve101]

Instead of a Make table, why not leave the original table structure intact, and then do a delete query followed by an append query to repopulate it. This will do the same job without having to reconstruct the make table correctly.

Let us know if you need further help using this technique.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[jdttek]

By "Delete Query", do you mean use query to delete all records from original table, then do append query? Would that be the same as maros to:
1. Select All Records on original table
2. Delete Records
3. Append Records with Append Qry

That is teqnique I use when I import / refresh data from excel to existing table. Seems to work.

Thanks
JDTTEK

 

[Steve101]

If you go to the query window, and define a new query, and select a table, you will note that one of the "Query Types" available from the Menu bar or Query Design toolbars is the "Delete Query" - note: You may have to double click on the Query menu item to see this, as infrequently used menu options dont always automatically display - in which case you'll see a >> symbol which you can click on, in leau of a double click).

Once you've selected the table and specified the query type (as Delete Query), you just need to save the query. Then you can run it from a macro - if you're using macros, or from code, if you're a little braver. If you're using a macro, use the OpenQuery command. You might want to prefix this command with the 'Setwarnings No' macro action, to suppress the warning message which will otherwise appear. Remember to 'SetWarnings Yes' at the end of the macro, to reverse this.

There are lots of ways to achieve what you want; this is one of them; others would allow you to do the same with less query objects required, but for the moment, this should be a good start.

Hope this clarifies it,

Cheers,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)