Replace an autonumber column

Pettor

Board Regular
Joined
Aug 8, 2015
Messages
175
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
I have also copied the table in a new blank .db but I still receive the same problem.
 
Upvote 0
And it is just so close...
 

Attachments

  • Capture.PNG
    Capture.PNG
    12.4 KB · Views: 11
Upvote 0
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?
 
Upvote 0
I don't think you can set the values in an Autonumber field.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top