MS Access Data Type Number Greater Than 28

Copper Contributor

When linking via ODBC to Snowflake, field is Number(38,0) and is linking at Short Text.  I thought Large Number Data Type would be the answer, but Microsoft says no and to recommend to their Engineering team the need to have this work.  The maximum number length apparently is 28 for linking; beyond this it shows up as Short_Text data type.  Any help or work arounds appreciated.  I can't join or use for calculations when it's short_text.  

4 Replies

@chantaloconnor 

One thing you could try is coercing it to an appropriate number data type for the join.

However, I'm not sure what you mean by "Number(38,0)", so I'm not sure about this. Are you referring to what would actually be Decimal(28,0) or Decimal(38,0) in the Access table? 

 

Also what is the data type of the field you want to use in this join? There are several conversion functions expressions, one of which should be appropriate. 

 

@George Hepworth Snowflake has fields set to Number(38,0).   First photo below.  When linking this table (second screen shot), it shows up as short text.  On the second photo below, the yellow highlighted fields are ones I would join on ... getting data type mismatch error.  I think the only thing I can do is use the conversion functions as you mentioned.  The Snowflake numeric max is 38, and is used for a large number of fields.  The conversion functions will be a challenge to apply to so many fields I need to use in my queries (linking, calculations, etc).  I was hoping there was a better solution.  My hope is that Microsoft would consider the 38 length numeric value, considering the potential for those linking via ODBC to Snowflake.  Thank you.

chantaloconnor_1-1632141916801.png

 

chantaloconnor_2-1632141956373.png

 

 

There is no way to handle these in Access. Maximum for Decimal is, as you have found out, 28 digits. The only option would be to manipulate those number via Pass-Through queries.

@chantaloconnor 

Thank you for clarifying. As Gustav noted, the max precision for decimals in Access is 28, although SQL Server and other server based RSBMS's support 38. In other words, MS does support the greater precision in SQL Server, just not in Access.

I wasn't aware, though, that Snowflake (and perhaps other databases?) refer to it as a more generic "number" instead of "decimal". 
One other though occurs to me, although it's probably not feasible. I might see if I could create views that convert those fields to the Number(28,0) precision and use those views in Access instead of the original tables.