I've a MS Access solution accessing to linked tables to SQL Server. Today it happens that one key filed in a table reached the limit of int.

So my first idea was to change it from int -> bigint but it seems to be not possible as ALTER queries return a couple of errors for.

The second idea was to clone the key field, delete original and make the clone to the new key field. This worked without problems but now the linked table only show #deleted values for all fields within the table. It seems that MS Access uses the keys for some relations when it access to the backend.

Maybe somebody know a solution to extend /reset the key without loosing the relation for MS Access?

Thank you

CodePudding user response：

MsAccess has a "long integer" number option in the table design, which has the same size of INT from SQL Server. See below:

https://www.microsoft.com/en-us/microsoft-365/blog/2017/03/06/new-in-access-2016-large-number-bigint-support/

CodePudding user response：

I found this: "Unfortunately, Access is unable to use BigInt as a primary key. You will most likely need to change this to a long integer. The article below is for an older version of Access, but the info still applies:" (https://answers.microsoft.com/en-us/msoffice/forum/all/linked-table-showing-deleted-in-all-rows-and/d0451a78-08c1-4667-8677-6c61edcebcf1)

So it seems not to be possible to use bigint as key.