Change Text to Memo

[Bob Howard]

Hi,

How can I execute a command (presumably using DAO) to change the data type
of an existing table field (and which already contains data) from "Text" to
"Memo"?

Thanks in advance...

bob h

 

[Daniel Pineault]

Bob,

I put this together rapidly for you. It should do the trick. At the very
least it will show you the basic concept and you can take it from there.
Enjoy!

'---------------------------------------------------------------------------------------
' Procedure : SwitchFieldType
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Switch a given table field to Memo
' 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:
' ~~~~~~~~~~~~~~~~
' sTableName - Name of the Table which has the field to switch
' sFieldName - Name of the field to switch to Memo Type
'
' Usage:
' ~~~~~~~~~~~~~~~~
' SwitchFieldType("tbl_Contact","Notes")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Mar-13 Initial Releas
'---------------------------------------------------------------------------------------
Function SwitchFieldType(sTableName As String, sFieldName As String)
On Error GoTo Error_Handler
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


sSQL = "ALTER TABLE " & sTableName & " ALTER COLUMN " & sFieldName & "
MEMO;"
DoCmd.SetWarnings False
DBEngine(0)(0).Execute sSQL, dbFailOnError
DoCmd.SetWarnings True

If Err.Number = 0 Then 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
--
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.

 

[Bob Howard]

Thanks. I think I see what you're after, but I'm still confused on how this
code locates the specific DATABASE involved. Actually, I'm going to run
this code to modify the field type (to Memo) for a table located within a
ANOTHER Access database.

I have a front-end / back-end application, and I need to send a special
Access app to the users of the application that can simply run to modify
their existing back-end database to make this change. Thus, the table
involved is not within the Access database of the running program.

bob

 

[Daniel Pineault]

You could have mentioned that originally. Not a big thing to modify. Try
this instead



'---------------------------------------------------------------------------------------
' Procedure : SwitchFieldType
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Switch a given table field to Memo
' 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)

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

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

Set db = Nothing

If Err.Number = 0 Then 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
--
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.

 

[Bob Howard]

Thanks so much ... I'll work on this later tody and post back.

Sorry I didn't initially mention that the database is elsewhere. I had
already created some code (before I posted), but it was failing with an
error. I wasn't using SQL to perform the function, but rather I was using
(what I'll simply call) "other" DAO coding. Like:

Dim daoDatabase as Dao.Database
Dim daoTableDef as Dao.TableDef
Dim daoField as Dao.Field

' the path is passed to this program as a command string
Set daoDatabase = OpenDatabase(Command)
Set daoTableDef = daoDatabase.TableDefs("table name")
Set daoField = daoTableDef("field name")
daoField.Type = dbMemo

The above fails with error 3219 at the set daoField line.

I copied some code from elsewhere, modified it (to the above) and was hoping
to make it work (somehow). I'm really a newbie at this type of programming.

bob

 

[Daniel Pineault]

Bob,

Don't worry about! As you can see for yourself the required modification to
make it suit your need was absolutely minimal. I was just pulling your leg a
bit.

Have a great day and post back if your need further help.

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.

 

[Bob Howard]

SUCCESS !

Thanks a "trillion" (I've never used that before...)

bob