Change Data Type from Memo to Text

[rscott927]

I have to fields called 'NAME' and 'CLIENTID' in a table called 'LOCATIONS'.
They are in the Memo format but need to be converted to text. How do I
change the format using VBA? In kow this has been asked in different way
before, but none of the other examples seem to help.
Thank you so much for you help

 

[Douglas J. Steele]

Once a field has been added to the table's Fields collection, you can't
actually change it using VBA.

I believe you should be able to run a DDL (Data Definition Language) query
along the lines of:

ALTER TABLE MyTableName ALTER COLUMN MyMemoField TEXT(255)

but unfortunately I've only got Access 97 on this machine, so I can't test
to confirm.

 

[Daniel Pineault]

below is the code to do the exact opposite. You should have no problem to
switch it around to suit your need. At the very least it demonstrates one
method to do this.

'---------------------------------------------------------------------------------------
' Procedure : SwitchFieldType
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Switch a given table field to Memo in an external database
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb - Database Path & Name to execute command against
' sTableName - Name of the Table which has the field to switch
' sFieldName - Name of the field to switch to Memo Type
'
' Usage:
' ~~~~~~~~~~~~~~~~
' SwitchFieldType("D:\Contacts\Contacts.mdb", "tbl_Contact", "Notes")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Mar-13 Initial Releas
'---------------------------------------------------------------------------------------
Function SwitchFieldType(sDb As String, sTableName As String, sFieldName As
String)
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim sSQL As String

''Execute the query statement under DAO like this:
'DBEngine(0)(0).Execute sSQL, dbFailOnError
''or under ADO like this:
'CurrentProject.Connection.Execute sSQL

Set db = DBEngine.Workspaces(0).OpenDatabase(sDb) 'External database
'Set db = DBEngine(0)(0) ' Local database

sSQL = "ALTER TABLE " & sTableName & " ALTER COLUMN " & sFieldName & "
MEMO;"

DoCmd.SetWarnings False
db.Execute sSQL, dbFailOnError
DoCmd.SetWarnings True

Set db = Nothing

Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: SwitchFieldType" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function


Also, be careful as MEMO fields can hold a lot more data than text field, so
you may loose data by doing what you are about to do.

Whatever you do, before proceeding, MAKE A BACKUP copy of your
database(back-end of course)!!! Better safe than sorry.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Dale Fye]

Recommend you change the name of your [NAME] field to something else. Name
is a reserved word in Access and keeping it as a field name will
occassionally result in interpretation errors. You can avoid these errors by
wrapping it in brackets [ ] every time you use it, but that can get annoying.

You can find a complete list of problem name and reserved words at:

http://www.allenbrowne.com/AppIssueBadWord.html#N