Query to create index not working - Resources exceeded (1 Viewer)

Hi All,
I've got a really weird issue creating an index for a table.

(nb: table, index and field names renamed below for clarity)I have a table (Table1) that contains an unindexed numerical field (Field1).
I have a query (Query1) that contains the following SQL:

If I run the query I get a "System Resources Exceeded" error.

When I edit the table in the GUI, I can see that Field1 still isn't indexed.
If I go into the Indexes in the table and create the index manually it works.

Has anyone encountered any similar issues?
Any idea what might be causing it?

I've had that error but not when using SQL to create an index.
Does the table have a lot of existing records? Are each of them unique for that field?
What datatype is 'Field1'?

You could create a copy with no records, index it then import your records

It may be that you have lots of other database objects open.
Try running this when you first open the app.
If it still fails, have a look at this utility Available Connections

Otherwise, you can increase the MaxLocksPerFile setting from the default 9500 to say 15000 either using VBA or editing the registry. If you need to do that do a forum search for that phrase. There will be many hits including https://www.access-programmers.co.uk/forums/showthread.php?t=305354&highlight=MaxLocksPerFile

Thanks for the info. I'll start looking into that.

There are a large number of records on that particular table.
I'm working on a database that was written by someone else but from what I can see stepping through the code:

1. the local table gets cleared out
2. all the indexes are then deleted
3. it then imports a load of data from a pass-thru connection
4. finally it executes a query to add the index back in

There's a few of these "CREATE INDEX" queries (one per field).
It randomly fails on two of these.
One is for a date field the other is numerical.

Strangely, I've been told it works fine on other users PCs.
They all have the same version of MS Access and all have a local copy of the database file.
They copy the file to their PC each day from a network location so there can't be inconsistencies between the files they are running.

Very odd.

If I find the cause I'll post it here.

Thanks again for the pointers!

The sequence you've described seems very strange.
Why delete existing records? Why delete the index then reinstate it?
The only logical reason would seem to be that the imported data has duplicates.

There has to be a simpler way of achieving your end result.

Also it sounds like your users are running an unsplit database copied to their computer. Why not just have a split database and avoid all this copying from the network?