Access make table query question

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
I am wondering if this is doable: Is it possible to do a make table query, and when the table is created, can you specify a field to be an index field during creation?

thanks
Fred Emmerich
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,
That would have to be programmed as a second step - the Make Table query itself is simply "select into ... from ..." and doesn't have any way of doing indexing.

For a long time I have used this site as my guide to most things related to DDL commands and other similar advanced topics:

I think however you can also google "DAO MSAccess Create Index" or "ADO MSAccess Create Index" or even just simply "MSAccess create index" - it might be possible to do this is plain old SQL although I don't know offhand (MSAccess SQL DDL is not as rich as for most other databases).



Note that personally, if I had a table with known structure I would generally prefer to create the table permanently and add the indexes when I create it. Then for your "make table" query you actually just delete all records from that table and then insert new records in ... this way you don't have to actually recreate the table all the time ...
 
Upvote 0
The create once, delete records, and then append the template from then on is my current solution. I was wondering if there was a way I could streamline it a bit.

thanks
Fred Emmerich
 
Upvote 0
If I am allowed to use VBA in the project, then I would likely do that by using DoCmd.RunSQL.

Following shows a way using SELECT INTO, and different index types.
VBA Code:
Sub makeTableWithIndex()
DoCmd.RunSQL "SELECT * INTO [newtable] FROM [Query1];"
DoCmd.RunSQL "CREATE INDEX NewIndex ON [newtable] (IdFieldName)"
'DoCmd.RunSQL "CREATE UNIQUE INDEX NewIndex ON [newtable] (IdFieldName)"
'DoCmd.RunSQL "CREATE INDEX NewIndex ON [newtable] (IdFieldName) WITH PRIMARY"
End Sub

You can also use CREATE TABLE with index first then transfer data by using INSERT INTO but SELECT INTO is better to not deal with the new table structure. However, if Query1 is only used for only creating the new table, then I wouldn't use a dedicated query for that but simply use SELECT statement as the source instead.
 
Upvote 0
Normaly, you wouldn't use a make table.
Your tables would already be made,then you'd add data.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top