VBA and BigInt Access Data Type RRS feed

  • Question

  • Access 2016 in its last release add the bigint data type, Ok!...


    I do not find a relative variable type so it is not so much useful... 

    how can i manipulate this data type in vba? i must consider it as a string?

    many thanks!

    Monday, September 10, 2018 8:20 AM

All replies

  • It's only available in Office 64bit (LongLong).

    i must consider it as a string?

    In 32bit, afaik yes.

    Monday, September 10, 2018 8:52 AM
  • Not so. LongLong is the data type for bigint, and it is available in 32-bit Access as well.

    -Tom. Microsoft Access MVP

    Tuesday, September 11, 2018 1:19 PM
  • What version? It's not in Access 2016 (tested it).

    Tuesday, September 11, 2018 1:45 PM
  • Unfortunately, there is no x64 bit longs available in access x32. (it is after all only a x32 bit version!!!!).

    So the “longLong” variable type is only available in the access x64 bit version.

    However, you can use a data type of “variant”. Inside of Access, you find that the resulting variable type is a packed decimal if assigned these larger integers.

    So Access x32 does allow VERY large number types. Even without big integer support in Access (x32), you could in the past setup/design a column in a local access table as “decimal “type. Such decimal types can be up to 28 digits, and since they are packed decimals, then they behave like currency types (no floating point rounding errors). I explain use of these packed decimal types in this article of mine:


    So if you now or in the past need some REALLY large numbers in Access, then packed decimal types will solve this issue for you, and it also how  you can work with larger integers then what “long” provides. This kind of very large numbers in access can be most usefull for science, or large accounting numbers, and the bonus part is no rounding errors either.

    So you have to declare a VBA var type of “variant”.

    You thus simply assign the results of a recordset into a “variant” type var. If you had some “need” to decleare and use such variables BEFORE pulling data from the table, then you coul make a one row dummy “local” table, and assign the variant to a value from that “fake” table.

    If you set this value from a query of data into a recordset, then the resulting data type will be a number type, not a string, and you can use addition (and math) for that type.

    I not tested this, but I assume that the data type shown in the linked table should be “decimal” type, and not string/text.

    If you assign that value to a variant, it thus should become a decimal type, and that allows math functions and basic adding to that variable (which a string would not).

    I also believe that you have to ensure that you using one of the later “native” ODBC drivers, and you have to re-link your table with that native driver (the older legacy sql driver cannot be used).

    So the "big int" thus should come through as a number, and not a string. Keep in mind in the past, those larger values could be updated, it was just that you would see the column as a string. With the update to 2016, you can now get that value to be seen as a larger number (decimal). As noted, you need to re-link the tables, and ensure that you adopt the native sql drivers.


    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Tuesday, September 11, 2018 4:01 PM
  • Sorry, no longLong in access x32. You might see some "example" code, but it would have used compiler directives - there is no x64 wide integers in Access. You could however in the past use packed decimal types, but LongLong can only compile in access x64.

    We also have a new type called longPtr. However, longPtr is x32 in access x32, and it is x64 bits wide in access x64. This is ideal, since then if you use windows api, then it is x32 or x64 depending on your platform and the windows api requires the pointer to memory data type to be x32 or x64 when making a x32 or x64 bit call.

    If access had the longPtr data type in the past, then the jump to the x64 bit windows api would have been near seamless. We unfortunately had to use a long data type for memory pointers into the windows api (x32), and thus when going to x64 api, those longs have to be changed to longlong, or better yet, longPtr which tends to allow the api to work with both x32/x64.

    So longPtr changes size depending on the version of Access but longlong only can be declared and used in access x64. The x64 bit support in Access appeared for 2010.

    However, the new bigInt support can be used in access x32 -  you just have to put the results into a variant, which in term becomes a packed decimal type in x32 - which has always been supported.

    The only REAL difference in the new bigInt support is that access will return the column as a integer (or packed decimal) where as in the past it always came through as a string. So the support of "big integers" from sql server is due to update of the ODBC driver support inside of Access, not that we now have a x64 bit version of Access.

    Packed decimal is a great option in Access for really large numbers (up to 28 digits). So for scientific data, or large accounting numbers, packed decimals in Access are viable option if the currency data type (4 decimal points) will not suffice.


    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Tuesday, September 11, 2018 4:12 PM
  • See my other response (you are correct, no longLong in access x32).

    You can however now get the larger PK from sql server as a number type and not a string – but you have to use type variant in Access x32 to get that value as a number.


    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Tuesday, September 11, 2018 4:20 PM