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

 

[Joe4]

Any Access database has a maximum size limit of 2 GB. It looks like that you are bumping up against that limit, and your action would put you up over that limit.

I don't know how much you know about Access databases, but when you delete data, tables, etc, you really don't delete them (and the size of the database doesn't go down). You just "flag" these records for deletion. So the size of your database will continue to grow and "bloat". You need to "flush" out the deleted data by doing a regular "Compact & Repair" on your database.

Take a look at the current size of your Access database (in Windows Explorer).
Now, open it up, and do a Compact & Repair on it.
Then, check the size of the database.
Has it gone down considerably?
My guess is, it probably has, and you will probably be able to do those other tasks you were trying to do.

Anytime you delete a large amount of data, it is a good idea to run a "Compact & Repair Database", and it is advised to run that periodically, for database maintenance.

 

[bobsan42]

what is the query?
did you check the database size? do you have writing permissions for this disk?
Why bother with the autonumber filed anyway? Is it that important?

 

[Pettor]

Hi All, thank you for your help.
I really know almost nothing about Access but I know I have to optimize it so this step has already been done.
I have also increased the registry maxlocks as this was also a problem that previously occurred.
The file size is 350mb and I want to replace this column to an existing table.
This column is my primary key actually and some other elements depend on this column so it is important to have it.
Since the whole access is 350mb is it normal to becoming 2Gb just by adding an autonumbering column?

 

[Joe4]

Since the whole access is 350mb is it normal to becoming 2Gb just by adding an autonumbering column?

If that is indeed true, I wouldn't expect that adding a single column would increase the database size by that much.
There must be something else going on there.

 

[Pettor]

I even tried to paste the number from excel but it had the same problem. Is there any way that I could combine columns with a query?

 

[Pettor]

I have also copied the table in a new blank .db but I still receive the same problem.

 

[Pettor]

And it is just so close...

 

[bobsan42]

You can't simply replace your primary key field, especially if it is used in relashionships.
You cannot write numbers to this field or paste them.
What is the query you are trying to use? Did you check your write permissions - can you write any data to the database?

 

[Joe4]

I don't think you can set the values in an Autonumber field.