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.
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
|
|
