Access make table query question

[Mr930]

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

 

[xenou]

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:

Microsoft Access tips: DAO Programming Code Examples

VBA code using the DAO library in Microsoft Access.

allenbrowne.com

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]

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]

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.

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]

Normaly, you wouldn't use a make table.
Your tables would already be made,then you'd add data.