Using a Make-Table query that copies the primary key 1

[chrisperkins]

Hi,

I am using a Make-Table query to create a new table. The source table has a Primary Key set on the "row-id" field.
This is being done every night of the week in order that we have a local copy of the data. I am first deleting the old local table & then carrying out a new make-table.

When I create this new make-table query though the primary key properties do not get copied to the "row-id" field of the new table.

Is there something else that I must do or is it something that I will have to do in code. If this is the case how would I go about doing this?

Cheers,
Chris

 

[BNPMike]

You'd be much better simply emptying the table (delete * from XXX) each day and appending the rows in. There's no advantage in doing a make-table.

Do the append query and then compact the database. You will preserve all your table properties and keep the database efficient.

 

[scriverb]

First of all you do not need to delete the old table each time you run your code. The Make-Table query will overwrite the previous table.

This is the code to accomplish what are asking:

Dim db As DAO.Database
Set db = CurrentDb
DoCmd.SetWarnings False
DoCmd.OpenQuery "[red][i]yourMakeTableQueryName[/i][/red]"
DoCmd.SetWarnings True
db.Execute "CREATE INDEX row-id ON [red][i]yourNewTableName[/i][/red] (row-id) WITH PRIMARY;"
db.Close

Post back if you have any questions.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[chrisperkins]

Thanks for the info Bob - it works a real treat. I must admit that it has gotten me out of rather a large hole.

Thanks again.

Chris

 

[scriverb]

Chris, please to help you with the problem. Thanks for the Star.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]