Replace an autonumber column

Pettor

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

Some videos you may like

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.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,327
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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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

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

Board Regular
Joined
Aug 8, 2015
Messages
137
I have also copied the table in a new blank .db but I still receive the same problem.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,327
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
I don't think you can set the values in an Autonumber field.
 

Forum statistics

Threads
1,089,220
Messages
5,406,927
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top