[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

How do I programmatically change an existing Text Field's size?

I get an invalid operation in the line "fld.Size = bytFieldSize".
How can I change the code below to work?
Private Function ChangeTextFieldSize(strTableName As String, strFieldName As String, bytFieldSize As Byte)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    Set fld = tdf.Fields(strFieldName)
    With tdf
        fld.Size = bytFieldSize
    End With
    Set tdf = Nothing
    Set fld = Nothing
    Set dbs = Nothing
End Function

Open in new window

0
bobbat
Asked:
bobbat
  • 4
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you can use this

currentdb.execute "alter table NameofTable alter column NameOfField byte"
0
 
Rey Obrero (Capricorn1)Commented:
just take note that if the field contains data bigger than a byte can hold, you will lose some of the data.
0
 
bobbatAuthor Commented:
But if a text field can only be a maximum of 255 characters and if a byte stores numbers from 0 to 255, can I still lose data?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Rey Obrero (Capricorn1)Commented:
255 characters >> is the number of characters *not* the value

a byte field can hold only a *maximum* value of 255
0
 
Rey Obrero (Capricorn1)Commented:
example > 256 is three characters, but a byte field will reject 256 as an entry to the field because it exceeds the max value of 255
0
 
bobbatAuthor Commented:
Thanks, that worked.  I used:
    CurrentDb.Execute "ALTER TABLE " & strTableName & " ALTER COLUMN " & strFieldName & " TEXT (" & bytFieldSize & ")"  The entire code is in the snippet below.
Private Sub cmdChangeTextFieldSize_Click()
    z = ChangeTextFieldSize("tblInvoice", "strAddressFormat", 25)
End Sub
 
Private Function ChangeTextFieldSize(strTableName As String, strFieldName As String, bytFieldSize As Byte)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    Set fld = tdf.Fields(strFieldName)
 
    CurrentDb.Execute "ALTER TABLE " & strTableName & " ALTER COLUMN " & strFieldName & " TEXT (" & bytFieldSize & ")"
    
    Set tdf = Nothing
    Set fld = Nothing
    Set dbs = Nothing
End Function

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now