Solved Bigint data type in Microsoft Access

[deletedT]

This is the structure of a table in MS SQL server:

When I add this table as a linked table to a Microsoft Access database this is what I have :

Apparently, Short Text is not what I need and the table doesn't behave the way I need.
I can insert data into the table. No problem. SQL server shows the data correctly.

But access shows me this when I open the table:

I also can not use this in VBA:
myVar=Dlookup("BigIntTest","tblTest","ID=3")
I receive a Data type mismatch error.

But this line brings in the correct answer:
myVar=Dlookup("BigIntTest","tblTest","ID='3'")

Now if I test it this way :
?vartype(dlookup("BigIntTest","tblTest","ID='3'"))
I receive 8. (vbString). It means that I can't use it in calculations. Because Access thinks it's a string and not a number.

According to the following page, Microsoft Access supports BigInt data type since 2016 version:

New in Access 2016—Large Number (BigInt) support | Microsoft 365 Blog

The Large Number data type provides additional analytical capability and deepens the integration experience when users are importing/linking BigInt data. —Read on to learn more.

www.microsoft.com

Now my question:
How can I have a linked table from sql server with bigint datatype in Microsoft Access?

Any kind of advice would be a great help.
Thank you.


Edit:
I'm using Microsoft 365.

 

[isladogs]

To use bigint in Access, you need to enable support for it in Access options.
It should then be treated as a very large number field when you relink the fable.

NOTE that if any of your users have A2013 or earlier, they will no longer be able to open your database.
Unless you are certain you need big int datatype, i would avoid using it

 

[deletedT]

Unless you are certain you need big int datatype, i would avoid using it

No I don't really need a bigint. While adding a table to sql server I made a mistake and chose bigint instead of int and I faced the above problem. My question above was just to satisfy my curiosity what's happening.

To use bigint in Access, you need to enable support for it in Access options.

Million thanks.

 

[Galaxiom]

BigInt is +/- 2^63 = +/- 9,223,372,036,854,775,807
Do you have that much data?

Create a View to Convert the value to Int. Link the view to Access.
(I do this on some databases that ship with bigint keys.)

Convert to varchar(20) if faced with numbers that don't fit Int but the number would be a lot better to use if possible.

 

[deletedT]

BigInt is +/- 2^63 = +/- 9,223,372,036,854,775,807
Do you have that much data?

No I don't. I simply wanted to know what's happening. and why Access shows #Deleted.