How to Return the Precision and Scale of a Decimal Field in Access

Retrieving the precision and scale of a Decimal data type field is surprisingly difficult in Access because those two attributes are not available via the DAO Field object.  In fact, I'm not aware of any way you can use DAO to retrieve that data.

We'll use the ADO Column object to fetch the precision and scale settings of a Decimal field.

Sample Tables

This method works equally well for local Access tables and linked SQL Server tables.

Consider these two sample tables.  MyLocalTable is a local Access table, while MySqlServerTable is a linked SQL Server table.

Each table has a field named MyDefaultDecimal which represents a default Decimal field (if you don't define the Precision and Scale of a Decimal type, it defaults to a precision of 18 and a scale of 0).

The Code

The GetDecimalAttributes() function returns a user-defined type (udtDecimalAttributes) so that both Precision and Scale may be returned at the same time in a type-safe manner.

Type udtDecimalAttributes
    'The maximum total number of decimal digits
    '   to be stored (1 to 38)
    Precision As Byte
    
    'The number of decimal digits that are stored
    '   to the right of the decimal point (0 to Precision)
    Scale As Byte
End Type

'Scale and Precision are not available in the DAO Field object,
'   so we need to use ADO to get this information
Function GetDecimalAttributes(LocalTblName As String, _
            FldName As String) As udtDecimalAttributes
    Dim Cat As Object ' ADOX.Catalog
    Set Cat = CreateObject("ADOX.Catalog")
    Set Cat.ActiveConnection = CurrentProject.Connection

    Dim Col As Object  'ADOX.Column
    Set Col = Cat.Tables(LocalTblName).Columns(FldName)
    
    With GetDecimalAttributes
        .Precision = Col.Precision
        .Scale = Col.NumericScale
    End With
    
    Set Col = Nothing
    Set Cat = Nothing
End Function

If you're not using vbWatchdog like I am, you will want to add your own error-handling code.

Sample Usage

To demonstrate usage of the GetDecimalAttributes() function, I created a couple of test procedures:

Sub PrintDecimalAttributes(TblName As String, FldName As String)
    With GetDecimalAttributes(TblName, FldName)
        Debug.Print TblName; "."; FldName; ": ", .Precision, .Scale
    End With
End Sub

Sub TestDecimalAttributes()
    PrintDecimalAttributes "MyLocalTable", "MyDefaultDecimal"
    PrintDecimalAttributes "MyLocalTable", "MyCustomDecimal"
    PrintDecimalAttributes "MySqlServerTable", "MyDefaultDecimal"
    PrintDecimalAttributes "MySqlServerTable", "MyCustomDecimal"
End Sub

External references

Image by NomeVisualizzato from Pixabay