Create index after make table

[litrelord]

Hi,

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?

Thanks

Nick

 

[SydneyGeek]

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

Denis

 

[litrelord]

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.

Thanks

Nick

 

[james_lankford]

CREATE INDEX 
  your_desired_index_name
ON 
  your_table 
( column1, column2, column3 )

http://www.blueclaw-db.com/create_index_sql_ddl.htm

http://www.google.com/search?q=ms+a...s=org.mozilla:en-US:official&client=firefox-a

 

[litrelord]

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

Nick

 

[SydneyGeek]

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.

Denis

 

[litrelord]

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.

Nick

 

[SydneyGeek]

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.

Denis

 

[litrelord]

That seems neater!

Thanks Denis.

Nick