0

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
LuckySlevin
  • 695
  • 3
  • 16
  • 23
  • 1
    It's telling you you're hitting the maximum capabilities of Access. Check into moving the database to something else, perhaps SQLServer or mySQL. – Jonathan M Nov 26 '11 at 21:07
  • @JonathanM you should write this as an answer :), i tried that on SQL 2008 and it worked. If you write this as an answer, I will accept, thanks for the tip. – LuckySlevin Nov 26 '11 at 21:25

3 Answers3

1

Try to create a new table, and than push everything from the old table to the new one. eventually you can delete old and rename the new. if you have relations, you will need to deal with them as well. since the autonumber is a new column, I guess it is not involved yet in any of them....

and @jonathan comment is right... move to SQL Express or SQL server (MS world) or mySQL if you have a provider for it from your app.

Dani
  • 14,639
  • 11
  • 62
  • 110
1

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

Jonathan M
  • 17,145
  • 9
  • 58
  • 91
0

Good data management is key to any database, but more so with access as it does have size/performance limitations vs dedicated database servers.

You should look at some form of archival for very large datasets if moving to a dedicated server is not an option, e.g. moving data that is more than 6 months old (timescale will depend on just how much data you are adding)

Also, looking at the request it seems as though you may want to add the autonumber field to uniquely identify your records, if this is the case then it's good practise to ensure you have your unique identifier (primary key) set up when the table is first created.

All this aside, for the timebeing you could try the following: Move your database to a local drive (if not already on one) with plenty of free space. Ensure you are running on a machine with plenty of RAM and virtual memory. Perform a compact and repair of your database as this may sufficiently decrease the size of your table (This may take a while). Attempt to add your autonumber column again.

Matt Donnan
  • 4,933
  • 3
  • 20
  • 32