Format - Fixed property

[aziz rasul]

I have the following code: -

CurrentDb.Execute "ALTER TABLE tblTemp ALTER COLUMN MedianValue Double;"

How do I add to this so that the Format property is set to 'Fixed'?

 

[DJkarl]

I don't think you can set the format property of a table in Access via SQL. If you are already running VBA code you should be able to set the properties using a tabledef object.

 

[aziz rasul]

OK I'll look into that.

 

[aziz rasul]

I've written the following code to resolve the issue, but I get error 3420 i.e. 'Object invalid or no longer set' on the line indicated.

Public Sub ChangeTableFieldFormat(strTable As String, strFieldName As String)

    Dim tdf As DAO.TableDef
    Dim fld As Field
    Dim prp As Property
    
    Set tdf = CurrentDb.TableDefs(strTable)
    Set fld = tdf.Fields(strFieldName) 'ERRORS HERE
    Set prp = fld.CreateProperty("Format", dbText, "Fixed")
    
    fld.Properties.Append prp
    
    Set tdf = Nothing
    Set fld = Nothing
    Set prp = Nothing
    
End Sub

 

[aziz rasul]

It's OK folks, I got the solution from another source. The code should have been

Public Sub ChangeTableFieldFormat(strTable As String, strFieldName As String)

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    
    With CurrentDb
        Set tdf = .TableDefs(strTable)
        Set fld = tdf.Fields(strFieldName)
        Set prp = fld.CreateProperty("Format", dbText, "Fixed")
        
        fld.Properties.Append prp
        
        Set tdf = Nothing
        Set fld = Nothing
        Set prp = Nothing
    End With
    
End Sub