1

I'm working with access office 365 MSO. While importing excel files into access, sometimes long text columns will be assigned with the format "@". This somehow can lead to truncate the content. Currently I manually have to remove this "@" symbol, however the goal would be, to have a VBA code which would remove all "@" for long text columns, for all existing tables inside the DB.

I found this post Change column data type and format, however my knowledge of VBA is not sufficient to solve the issue. Could someone help?

Example of a long text column with format "@"

PiusL
  • 13
  • 2

1 Answers1

1

Place code in a procedure in general module and run it there or call from another procedure. 

Sub DelFmt()
Dim def As DAO.TableDef
Dim fld As DAO.Field
Dim prpName As String
prpName = "Format"
For Each def In CurrentDb.TableDefs
    If Not def.name Like "?Sys*" And Not def.name Like "f*" Then
        For Each fld In def.Fields
            On Error Resume Next
            fld.Properties(prpName) = "@"
            If Err.Number <> 3270 Then
                fld.Properties.Delete prpName
            End If
        Next
    End If
Next
End Sub
June7
  • 19,874
  • 8
  • 24
  • 34