Search code examples
databasems-accessautonumber

New column can not be added to Access database

I have an Access database table with approximately 4 million rows. What i want to do this to add a column(field) with the type of autonumber using MS Access Design View. But when i tried to this, access gives an error and says: "The query can not be completed. Either the size of the query is larger than the maximum size allowed (2 GB) or there isn't enough temporary space for this action."

What I have is:

Column1    Column2   .   .   . 
-------    -------

What I want to have is:

Number   Column1    Column2  . . . 
------   -------    -------

What I tried so far:

  • Change maxLocks per file value from registry.
  • Compact and Repair of database with Access.

Is there any way to fix this? or is there any other way to do what I'm trying to do?

Solution

  • Per OP Request:

    It's telling you you're hitting the maximum capabilities of Access. Check into moving the database to something else, perhaps SQLServer or mySQL