Function to Retrieve the Precision and Scale Property for Decimal Data Type

The uniqueness of decimal data type in Access are the existing properties named Precision and Scale. Precision property refers to the maximum number of digits in a number. Meanwhile, Scale property is the number of digits to the right of the decimal point in a number. Say for example, 123.45 has a precision of 5 and a scale of 2.

The problem is, we are unable to retrieve both Precision and Scale properties in DAO Access Database. The DAO for Access database can only be used to retrieve CollateOrder property as the replacement for Precision property, no replacement property for Scale.

But do not worry, we use ADO data library to retrieve both Precision and Scale properties. Before using ADO, In the VBA Editor, we must first set the ADO related references by clicking Tools menu, choose References... Next, enable the following references:

• Microsoft Activex Data Object 2.8 Library
• Microsoft ADO Ext 2.8 for DDL and Security

as seen in the Figure 1.

Figure 1. Setting the ADO references

After that, copy the following function to VBA editor:

Function getPrecisionScaleArrayADO(strFieldName, strTableName As String) As Variant
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
On Error GoTo Err_Msg
    cat.ActiveConnection = getDbsConStringADO()
    Set col = cat.Tables(strTableName).Columns(strFieldName)
    If col.Type = adNumeric Then
      getPrecisionScaleArrayADO = Array(col.Precision, col.NumericScale)
    Else
      getPrecisionScaleArrayADO = Array(Null, Null)
    End If
    Set col = Nothing
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function getPrecisionScaleArrayADO, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

The getPrecisionScaleArrayADO has two string parameters: strTableName as the table name and strFieldName as the field name.

The "cat.ActiveConnection" is set by using connection string for ADO function named getDbsConStringADO. The output for getPrecisionScaleArrayADO function is array variant consists of two index items:

1. Index item 0, describes the Precision property.
2. Index item 1, describes the Scale property.

Implementation:

Figure 2. Table1 with Field10 that has decimal data type

To implement the above function, we must get the connection string to open an ADO database using getDbsConStringADO. Suppose that an external database as described in getDbsConStringADO function has a table named Table1 and a field named Field1 with the following properties: Data Type=Number, Field Size=Decimal, Precision=5, Scale=2. See the Figure 2 above.

Figure 3. Immediate Window

The following function retrieve Precision property for Table1 table and Field10 field:

getPrecisionScaleArrayADO("Field10","Table1")(0)

The following function retrieve Scale property for Table1 table and Field1 field:

getPrecisionScaleArrayADO("Field10","Table1")(1)