Replace an autonumber column

[Pettor]

Hello guys,

I have a question about a column that I would like to replace. I have a table with 450.000 records and since there was a lot of data in and out, it turned out to be a very messy numbering in the autonumber column.
I tried to replace it by adding a new one but I received the below message

"The query can't be completed. Either the size of the query result is larger than the maximum size of a database (2GB). or there is not enough temporary storage space on the disk to store the query result."

Is there any way to bypass this message?

Thankis

 

[Pettor]

What I am trying to do is just to add a new column trying either to paste the data from excel or create a new autonumber field. I am not trying to replace the original at the moment.
Of course I can write to the database. I guess due to the high number of records in creates a limit and I would like to know if this can be bypassed somehow.

 

[bobsan42]

It would help a loooooot if you post the Query eventually!
My guess is you are trying to create a second Autonumber filed. Unfortunately you are only permitted to have one per table.

 

[Pettor]

It is not a query it is a table who is being filled by another table who has the autonumbering field. The first table is the feeder who is filled, transmit the data after a screening and then it is being erased. Due to this back and forth it has created an enormous number that has been transferred to the new table and every new number it gets has 10 digits.
I have managed to create a new autonumber column in the first table that has no data since they are being erased after each transmission and what I want now is to correct the numbers to the master table that has 450000 records. It is not a query or an actual primary key.

 

[bobsan42]

I got a bit lost. But anyway ...
What do you mean by " ... to correct the numbers to the master table ..."? Is the autonumber field related to any other fields in other tables?
The easiest way to reset the autonumber field is: empty the table (by moving all records to a temporary one), compact & repair the database (this will reset the autonumber filed of all empty tables to its initial value), put all the other data back in the table.

 

[Joe4]

Is what you are doing really necessary?
Note that the sole purpose of an Autonumber field is to ensure uniqueness.
The field itself, should not be expected to have any other meaning beyond that (i.e. should not be used as a "counter").
So it shouldn't really matter what the values of an Autonumber field are.
If you want a Primary Key field that has a "meaningful" value, you can create your own methodology/process for it, but that isn't an Autonumber field then.
I have databases where I have VBA create unique client IDs which are my Primary Key fields, but those cannot be an AutoNumber field, as you can select what value to put in an AutoNumber field.

 

[Pettor]

Probably I haven't described well the problem.

There are two tables. One is the "import" (temporary table) and the second is the "main" (master table).
The import tbl is being populated by an excel file, it is the table with the autonumber column and when some criteria are met it appends its data with an append query to the "master" tbl.
So the autonumber column is basically in the first table.

I don't want to change that. This has been reset already.

My problem is that the "main" table now has some very big numbers as not all the rows are being transferred from the first table and although I have 450.000 records the autonumbering has reached already the 500.000.000...

What I want is to correct this problem to the master tbl and have a number from 1-450.000. No primary keys, no queries involved.

Probably you are right in some of your comment about its use, but it was designed that way from someone else and I am just trying to give a solution although I am naive in computer programming...

That is whay I ask the questions in a foolish way probably...

 

[Joe4]

Perhaps this article will be helpful: Setting the value of an Autonumber field | Database Solutions for Microsoft Access | databasedev.co.uk

 

[Pettor]

That's good. It seems to be working. Many thanks!
I will give it a try and I will let you know if I have any more problems!

Thanks! I appreciate!

 

[Joe4]

You are welcome.

 

[xenou]

Generally if the field values don't matter another no-fuss solution would be to delete the autonumber field, then add it back. If it were a primary key you'd have to un-primary key it temporarily, but it sounds like that is not the case here.