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.