-1

I recently run into the problem of reaching the maximum amount of records in my table, somewhere around 32567. I got an overflow error trying to access the records. I figured out how it could happen since I have only around 2000 actual records in the table. It was the autonumber Id which reached 32567.

It happens because I use two tables. Documents and Archive. Documents is daily filled with all files in a folder on the harddrive. It is then compared with the table Archive and all double records are deleted from the Documents table. After that the new files are added to the Archive table and the Documents table is emptied again.

All the deleted records from Documents however used an autonumber which makes it increase fast. What should I do to prevent this from happening?

Regards Marco

Andre
  • 26,751
  • 7
  • 36
  • 80
Marco
  • 85
  • 1
  • 14

2 Answers2

2

Neither Autonumber nor #records are limited to 32567 (2^15 - 1).

You probably have the ID column in Archive defined as Integer (16 bit) instead of Long (32 bit).

Change it to Long and you will have a long time to go. :)

Andre
  • 26,751
  • 7
  • 36
  • 80
  • 2
    Well, if something goes wrong after 32567, then *somewhere* in your tables or in your code a `Integer` must be involved that should be a `Long`. – Andre Feb 13 '18 at 12:49
0

From your description, it looks like you are using [Documents] as a temporary table. You can extend the

"Documents table is emptied again."

function to recreate your document table, delete the old one. This will reset the Autonumber issue.

DoCmd.TransferDatabase acImport, "Microsoft Access", currentproject.Path & "\" & currentproject.Name,acTable,"Documents","DocumentsFresh",structureonly:=true

docmd.DeleteObject acTable,"Documents"
Docmd.rename "Documents", acTable, "DocumentsFresh"

Also beware, if you are relaying on documents.autonumber identity you should not follow this process. Do this only if you require temporary table function.

Krish
  • 5,917
  • 2
  • 14
  • 35
  • Imo deleting a table, even when deleting all records and refilling it, often is a bad idea. You can run into trouble if there are dependencies or relationships (delete failing, queries changing [Fieldname] to [Fieldname] As Expr1). [Truncating](https://stackoverflow.com/a/7237441/7296893) is a better plan. – Erik A Feb 13 '18 at 11:04
  • @ErikvonAsmuth it's an abstract of information or an idea. Implementing it depends on the user's actual requirement. SO answers do not only answer OPs but all kind of future readers who might expect exactly this kind of operation. Thanks for adding the dangers of this. – Krish Feb 13 '18 at 11:09