Access Runtime and BigInt (Large Number) support (1 Viewer)

[Minty]

I came across an interesting gotcha the other day.

From a linked SQL server backend we were generating a sequence number using the Rank() Over function.
This returns a BigInt data type, it appears that Access Runtime 2016 version doesn't recognise this datatype, but I can't find any official listings for Runtime version where it says they are supported?

Does anyone know or is able to test, I've temporarily lost custody of my spare machine with the runtime(s) installed so it's currently awkward to trial the different versions.

Obviously have solved the immediate issue by casting the result to a normal SQL integer (It's never going to be a big int in value!)

 

[CJ_London]

couple of thoughts

• Have you turned support on? (File>Options>Current Database)
• Reinstall 2016 runtime or a later version

 

[Minty]

It is enabled in the source Accdb file, but that was written in the subscription version of O365.
I don't have a runtime loaded on my development machine, I suppose I could spin up a second VM on my Dev machine to install it on.
I already have one with a full Access 2010 (or is it 2007?) loaded for legacy conversions.

The MS sites don't seem to mention it on the runtime functions, I don't really want the get the client to uninstall RT ver 16, then install RT ver 19 or 365 only to find it still doesn't work.
Time for some leg work at my end I guess.

The error is this one by the way;

Database requires a newer version of Access - Microsoft 365 Apps

Describes an error that occurs when you try to open a Microsoft Access database. The error message prompts you to use a newer version of Access. This issue concerns to use of the BigInt data type. A resolution is provided.

learn.microsoft.com

 

[sonic8]

Reinstall 2016 runtime or a later version

Access 2016 did not support Large Integer. You need a later version. I don't know if 2019 will do, or if you need the 365 runtime.

 

[ebs17]

I don't have a runtime loaded on my development machine

By renaming the file (a copy!) in ACCDR you can immediately test runtime conditions.
Only 64-bit versions of Access offer the use of the LongLong data type, which should correspond to BigInt.

 

[Josef P.]

The 32-bit versions also support the 'Large Number' data type in tables.

Quote from support.microsoft {dot} com /en-us/office/using-the-large-number-data-type-5b623f6e-641d-4e97-8bdf-b77bae076f70
(sorry, can't post orginal url)

To add the Large Number data type, you need Access 2016 (16.0.7812 or later).

 

[Minty]

@ebs17 As far as I can tell that doesn't answer the question when you have a full version that does support it, as we tested it locally and didn't see the error.

I'll need to add a text or message box to see what version the runtime is reporting, as you can't get to the immediate window.

 

[Pat Hartman]

I think using the Cast is going to be the best solution. Once you set the BigInt property, you prevent the db from being opened by earlier versions of Access.

 

[GPGeorge]

By renaming the file (a copy!) in ACCDR you can immediately test runtime conditions.
Only 64-bit versions of Access offer the use of the LongLong data type, which should correspond to BigInt.

LongLong is a VBA data type, right?

BigInt is a SQL Server data type, right?

True, they have the same range of values: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

However, it's not clear to me how that equivalence between a VBA data type and a SQL Server data type would work as a solution here. But I would be open to an explanation.

That said, it appears that Large Integers are supported from at least one version of Access 2016 forward, without regard to the bitness of the database itself. Documentation here: Large Integers

To add the Large Number data type, you need Access 2016 (16.0.7812 or later).

 

[gemma-the-husky]

Well 2^32 is 4billion roughly, giving the long number range of -2 billion to +2 billion.

The long long uses 64bit numbers to get to the value given by @GPGeorge which is 2^63.

I assumed this would only be available in 64bit access.

 

[ebs17]

Thanks for the tips, I was pretty cursory.
But how are these values used in the application? Is there just a link and look, or does VBA come into play somewhere?

Otherwise, further candidates for problems may be named.

 

[Minty]

It's just used to produce a top-n grouped ordered list, as I said using the Rank()Over T-Sql Function.

These are not normally exposed in the Front end, keeping them firmly as Where clauses in BE views or SP's, but the client wanted it on the form as a visual aide to the end user (which made perfect sense).

TL;DR - No VBA just a linked BE view.

 

[GPGeorge]

Well 2^32 is 4billion roughly, giving the long number range of -2 billion to +2 billion.

The long long uses 64bit numbers to get to the value given by @GPGeorge which is 2^63.

I assumed this would only be available in 64bit access.

Remember, we're talking about VBA vs SQL here. The documentation doesn't mention the bitness of Access as a factor, but I may be assuming too much from that omission.

 

[gemma-the-husky]

^^^^
Yes. I just suspect that 32bit access can't process the result if SQL returns a 64 bit integer.

 

[sonic8]

I just suspect that 32bit access can't process the result if SQL returns a 64 bit integer.

Your suspicion is unfounded.
The BigInt data type (dbBigInt) is supported by DAO since at least DAO 3.6 (Access 2000!).
You just need to be aware that you can only store a BigInt value in a Variant variable in VBA.

 

[ahmedjamalaboelez]

Access 2016 did not support Large Integer. You need a later version. I don't know if 2019 will do, or if you need the 365 runtime.

2019 support