Search code examples
sqlms-accessindexingcreate-table

How to add indexing to maketable queries in MS Access?

So I work with a remote data source that refreshes daily, so I have to run a few make table queries to translate/process the most up to date snapshot into tables.

However I don't see any clear way to add indexing. Right now what I am doing is I use one query to pull from the data source and manipulate it (call the result of this SourceQuery). And then in my maketable query I'll do:

SELECT SourceQuery.* INTO [Results Table] IN '\\path\to\dest\myDatabase.mdb' FROM SourceQuery;

But the resulting table has "No" in the index field for everything.

How can I modify the queries to add indexing to fields that are commonly used in joins?

Solution

  • When you write SELECT INTO statement, the structure of the new table is defined by the attributes of the expressions in the select list, so you won't be able to get the indexes of the source table this way.

    you should write create index in your query CREATE INDEX NewIndex ON [Results Table] (field1, field2)