Create index after make table


Well-known Member
Dec 1, 2002

Is there a way to create field indexes after a make table query? I have a query that updates a table with the latest snapshot of data from another source but the make table deleted the structure as well as the data.

Alternatively I thought of deleting the data and then using an append query but I couldn't see a way to do it using just the query.

Am I missing something obvious or is this best handles using a macro?



Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Nick, run 2 queries.

A Delete query that cleans out the table
An Append query to repopulate it

You can call them from a button.

Private Sub SomeButton_Click()
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "YourDeleteQuery"
  DoCmd.OpenQuery "YourAppendQuery"
  DoCmd.SetWarnings True
End Sub
Upvote 0
Hi Denis,

That makes more sense. Rookie error I know but because I'd not used delete queries before it didn't automatically show up in the menu so I didn't realise it existed!

I started putting together a macro that used RUNSQL to do the following:

DELETE TblData.* FROM TblData;

for each table. I assume this would be doing the same thing so will probably keep that.

The only thing I didn't understand was the 'Use Transaction' option. The help file didn't actually help very much apart from saying it may run quicker if set to No without explaining what the real difference was.

If I use the RUNSQL commands for the delete and append commands will there be any difference to having them saved as delete and append queries that are run from a macro? It seems it would be neater to have them all in the macro rather than visible as queries.


Upvote 0
Thanks James.

I decided to go for the delete and re-append method so it kept the entire structure but that could will in useful soon I'm sure.

Using SQL statements in the code to do the deletion and then RunQuery to run the append queries as it seems like way too much work to get the long SQL statements into a VB string.

Thanks both

Upvote 0
Hi Nick, to answer your earlier questions:

You can use RunSQL and assign the SQL to strings in the code, but I generally only do that when I am building something dynamic. Otherwise just running the query is simple to set up.

The Use Transaction bit is useful if the process may be interrupted, ie if there are thousands of records and the network traffic is high. If you set this parameter to True, nothing gets committed (saved) unless every record was loaded.

Upvote 0
Thanks again Denis.

shame they couldn't have an explanation as simple as that in the help file for Use Transactions. I'll leave it switched on.

Is there any way of doing that for the whole update? I guess I could copy the tables, update the copies and then only overwrite the old if all processes are successful. If there's a built-in way to do it I don't want to reinvent the wheel.

Upvote 0
Maybe you could copy the data out in a make-table query. Then delete the original data, proceed, and at that stage check that all is OK.
If so, clean out the copy and run a Compact & Repair to reclaim the unused space.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back