Back To Top


MS Access VBA Change Field For versions 2000, 2002, 2003, 2007

Go here To Create a Field in VBA.
Go here To Check if Field exists in VBA.
Go here To Count all Fields in VBA.
Go here To Rename a Field in VBA. This will change a field name using VBA.
Go here To Update a Field in VBA.

The VBA Change Field Sample is available for download here in a zipped MS Access Database. The below picture (click to enlarge) shows the Single form and module.

MS Access Form 

The Actual VBA Code to Change a Field within a Table using "Alter Table Alter Column", you should be able to copy/paste right into a module:





Function
ChangeField( _
     
ByVal TableName As String , _
     
ByVal FieldName As String ) _
     
As Boolean

  
'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   ' Updates a Field with a given Data type
   ' Accepts
   ' TableName: Name of table in which the field is located
   ' FieldName: Name of the field to Change
   ' Returns True on success, false otherwise
   'USAGE: ChangeField "TABLENAME", "FIELDNAME"

   '>>Please Note<< that not all Fields can be changed this way.
   'For Instance a YES/NO Field changed to TEXT Remains YES/NO
   'In Table View.
   'For those that cannot be changed, I suggest creating a new Field with your
   'chosen data type and append the old field data to it if possible.
   'I would then Remove the old field if it is no longer needed.
   'If you wish to change an Autonumber field to a regular number
   'You would choose INT as the data type (Long Integer). 

  
On Error GoTo errhandler

  
Dim Db As DAO.Database
  
Dim strSql As String 

  
'Create the Update SQL Code from our String Values
   'This example changes the existing Field "TEST" from Text to Number (Single)

  
strSql = "ALTER TABLE " & TableName & " ALTER COLUMN " & FieldName & _
        
" SINGLE;"

  
'Some Possible Data Types
   'YESNO (Boolean Value)
   'SINGLE (Number)
   'DOUBLE (Number)
   'INT (Long Integer)
   'SMALLINT (Integer)
   'TEXT
   'MEMO (Large Text for above 255 chars)
   'BINARY

  
Set Db = CurrentDb()

  
'Print the SQL so we can paste into the query builder if there are errors
   'This example changes the existing Field "TEST" from text to Number (Single)

  
Debug.Print strSql

  
'Run the SQL Query
  
Db.Execute strSql 

  
'If no errors return true
  
ChangeField = True

ExitHere:

  
Set Db = Nothing
  
'Notify the user the process is complete. 

  
MsgBox "Change Field Complete"
  
Exit Function

errhandler:
  
'There is an error return false
  
ChangeField = False


   With
Err
      MsgBox
"Error " & .Number & vbCrLf & .Description, _
            vbOKOnly
Or vbCritical, "ChangeField"
  
End With 

   Resume
ExitHere

End Function



 

Did you find what you were looking for?
What would you suggest?
mail@eraserve.com
Your Name:
Your Message:
Your Email:

    

 

Custom Search