Please make Access able to handle more data types as are commonly used in Server databases
For example Access cannot handle BigInt in linked tables. This has been in use in SQL Server for many years, but Access can't handle them in linked tables.
(Apologies for the irrelevant emailed response earlier)
Not too long ago we added support for Large Number (Big Int) – https://blogs.office.com/en-us/2017/03/06/new-in-access-2016-large-number-bigint-support/
We reviewed all the suggestions below and took note of them. I’m going to mark this topic as Done so you can your votes back.
If you’d like to ask for additional data type support, please create new suggestion (one for each).
Another field can be IP addresses not in text field.
Mark, VBA already has had LongLong. In 32-bit, you still can implicitly get LongLong from the BigInt field though you can't dim a LongLong variable explicitly but a variant variable will contain a LongLong. (kind like how decimal can't be explicilty dim'd but can be implicitly set via a variant variable).
Mark Burns commented
That article said NOTHING about supporting BIGINT values in VBA...so does this update enable BIGINT datatypes in VBA as well?
Mathew, I wouldn't call it completed, though. While adding BigInt is an exciting and a big step forward for us, we still have lot of gaps with our data types.
Personally, I wouldn't consider this item "completed" until Access team has achieved 100% compatibility with all data types supported in ODBC.
Anders Ebro (TheSmileyCoder) commented
You can read about the BigInt datatype update here:
Matthew Sheets commented
Completed / on it's way, correct?
Michael McCon commented
Decimals with precision at 20 or over. We have some third party databases that we connect to with decimals over 19 precision. When it comes into access it creates the field as ShortText. The same issue with BitInt.
There should really be someway to map these fields types for linked tables.
John Heaser commented
The ability to Get/Set the Lat/Lng properties of a Geography data type would be very useful
I understand BigInt is going to be included. I would agree with the others - a good move as this brings it into line with SQL Server
Jan Einar commented
Beside the already mentioned missing datatypes:
A datatype to handle "hours:minutes" where hours are not limited (i.e. a 32bit int value) to handle sums of i.e. working hours over a longer period than 24 hours (like "130:24")
A currency datatype with a user-changeable currency symbol like:
Where "€" is displayed as a combobox containing all available currencies. Windows has all these currencies in the internal regional settings list.
The current currency datatype always simply displays the currency symbol which is in the regional settings of Windows so if I create such control in an Access form and the user enters "$" and changes the regional settings then it is "€" from then on which leaves the currency formatting and datatype useless.
So the datatype should include the value like before and add an internal ID pointing to the standard Windows IDs of regional settings or alternatively a standard SYS table in Access containing all currencies and their symbols.
Pat Hartman commented
The BigInt is especially problematic since it seems to be popular as a PK data type. When a data field is BigInt, Access "sees" it as text which is usually OK but when the PK is BigInt, Access falls apart and renders every column and row in the table as #Deleted. I had a lot of trouble with date data types until I discovered the ODBC 11 driver. I'm not sure why Access still installs SQL Server as the default when it tops out at SQL Server 2005 or maybe even 2000. It has been a real PITA to get everyone upgraded to the ODBC 11 driver and it's not like they're using A2K, the client is using A2013.
Heinz Hoegel commented
BigInt (with AutoNumber capability), Date, Time.
In general, raise the limits of some ACE data types to those of its counterparts in T-SQL (nvarchar(4000), nvarchar(max)).
And of course: Extend VBA to handle those data types natively.
Mark Burns commented
1) BigInt(64-bit integer) (and a BigInt AutoNumber to go along with it, of course) *signed and Unsigned also
6) BigNum (or unlimited precision/infinite-precision/Arbitrary-precision numerics see: https://en.wikipedia.org/wiki/Arbitrary-precision_arithmetic)
Varbinary(max) to be able to upload and download files from SQL Server easier.
Ananda Sim commented
In my use,
1. proper support for nvarchar(max) - ntext has already been deprecated in SQL Server yet some interaction between nvarchar(max) and the ODBC SQL Driver 11 and Access 2007, 2010, 2013 causes an overflow condition.The amount of text that this happens varies with Access version and varies in amount - something around 8000 chars.
2. the number - integer variations in sql server vs the number types in Access aren't completely emulated or translated
In regards to the comment RE: supporting the data types, there's this item.... </shameless plug>
Alphonse Giambrone commented
Date, Time, BigInt. Note though that support in tables is not so useful if there are not corresponding VBA data types.