Solved Add Index Field? (1 Viewer)

[Space Cowboy]

Good Afternoon Good People,

I have converted a query to a table.
I need to add a unique ID to each row
Is there a simple way to add an auto number index field, I thought that it may ask me on creation but it did not.

 

[June7]

Simplest is to just open table in design view and add autonumber field.

Otherwise, review https://stackoverflow.com/questions...to-add-a-primary-key-using-a-query-make-table

 

[GPGeorge]

Queries don't have independent indexes. Tell us what you are really aiming for as the final product. June7's suggestion, for example, would work if the table in the query actually includes an AutoNumber in it.

 

[Space Cowboy]

I currently have the following Table

PrtID

odate

OrderID

Several different parts can be on an order. For other calculations I need a unique ID for each row. The first column needs to be supplemented to the above table to produce the below table. I would like it to auto number the entire table.

IndexID

PrtID

odate

OrderID

 

[Josef P.]

What didn't work with the implementation of June7's suggestion?

 

[June7]

See my previous (edited) reply.

ALTER TABLE tablename ADD COLUMN IndexID COUNTER, CONSTRAINT PRIMARYKEY PRIMARY KEY (IndexID);

Field will be added at the end of fields in table design.

 

[Space Cowboy]

I have added a new field as instructed and set data type as auto number.

Get this

 

[June7]

I've never encountered that. Web search: https://learn.microsoft.com/en-us/office/troubleshoot/access/file-sharing-lock-count-exceeded

 

[Gasman]

Need to increase that setting I believe?

 

[isladogs]

See Max Locks Per File | Access World Forums (access-programmers.co.uk)

 

[Space Cowboy]

I have reduced the rows to half a million and auto number has worked. Thanks June7

I will just push until I find the max.

Thank you all!

 

[isladogs]

I had a CSV file of 2.6 million postcode records and over 50 fields which I imported into Access and added an autonumber PK field.

 

[Pat Hartman]

I have reduced the rows to half a million and auto number has worked. Thanks June7

So, with something that large, the best solution is to define the table with an autonumber column as the PK and then run an append query. You shouldn't have any trouble generating an autonumber this way.

 

[isladogs]

So, with something that large, the best solution is to define the table with an autonumber column as the PK and then run an append query. You shouldn't have any trouble generating an autonumber this way.

It was slightly complicated in my case as that single table occupied almost 1.6 GB! Thankfully the data was read only