Change Precision and Scale

[Jay]

Access version: 2007

Is there a way to change the Precision property of a Decimal field using VBA
code? I have tried
myTableDef.Field(FieldNum).Properties("CollatingOrder") = 13
and
myRecordSet.Field(FieldNum).Properties("CollatingOrder") = 13
but both give an "Invalid arguement" error.

I have also tried using an ADO recordset:
rs.Fields("DField").Precision = 13
In this case, no errors are thrown and the property is changed, but when I
close the connection and open the table in design view in Access, the
precision hasn't changed.

I also tried an SQL
ALTER TABLE tablename ALTER COLUMN columname DECIMAL (13,6)
statement, but it gives a syntax error. According to the access help file,
only text and binary fields can take a (size) parameter.

Jay

 

[June7 via AccessMonster.com]

Some properties can be changed through code and some not. I had reason to
change data type for a bunch of fields and someone built the following for me.
Perhaps you can adapt.
________
Sub ChangeType()

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim fldOld As DAO.Field
Dim fldNew As DAO.Field
Dim n As Integer

Set db = CurrentDb

For Each td In db.TableDefs
If Not td.Name Like "~*" Then
'use the count rather than a "for each fld in td.fields"
'because when you delete the fld you mess up your position in the
fields collection
'for the same reason, the counter needs to proceed in reverse order
For n = td.Fields.Count - 1 To 0 Step -1
Set fldOld = td.Fields(n)
If fldOld.Type = dbSingle Then
' Debug.Print fldOld.Name
Set fldNew = New DAO.Field
fldNew.Name = fldOld.Name
fldNew.Required = fldOld.Required
fldNew.Type = dbDouble
fldNew.OrdinalPosition = fldOld.OrdinalPosition
td.Fields.Delete fldOld.Name
td.Fields.Append fldNew
End If
If fldOld.AllowZeroLength = True Then
Debug.Print td.Name
End If
Next n
db.TableDefs.Refresh
End If
Next td
End Sub

 

[Jay]

I found a solution using ADO (from another forum):

Dim cs As ADODB.Connection
Set cs = New ADODB.Connection
cs.ConnectionString =
Replace(CurrentProject.Connection.ConnectionString, "BugAndFeatures.accdb",
"testdecimal.accdb")
cs.Open
cs.Execute "ALTER TABLE TestDecimalTable ALTER COLUMN DField DECIMAL
(13,6);"
cs.Close

Jay