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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
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 ...
 

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
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
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,348
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.
 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,969
Normaly, you wouldn't use a make table.
Your tables would already be made,then you'd add data.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

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
Top