Solved Bigint data type in Microsoft Access (1 Viewer)

deletedT

Guest
Local time
Today, 05:17
Joined
Feb 2, 2019
Messages
1,218
This is the structure of a table in MS SQL server:

2020-06-22_17-26-03.jpg


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

2020-06-22_16-05-30.jpg


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.


2020-06-22_16-19-06.jpg


But access shows me this when I open the table:

2020-06-22_16-16-57.jpg


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:


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.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 05:17
Joined
Jan 14, 2017
Messages
18,186
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

Guest
Local time
Today, 05:17
Joined
Feb 2, 2019
Messages
1,218
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

Super Moderator
Staff member
Local time
Today, 16:17
Joined
Jan 20, 2009
Messages
12,849
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.
 

Users who are viewing this thread

Top Bottom