ms-accesslongtext

How to remove long text format "@" (MS Access)


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 "@"


Solution

  • 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