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

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" wrote:

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

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" <(E-Mail Removed)> wrote in
message news:A40D230A-3579-4D31-930C-(E-Mail Removed)...
> 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 Release
> '---------------------------------------------------------------------------------------
> 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" wrote:
>
>> 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
>>
>>
>>

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" wrote:

> 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" <(E-Mail Removed)> wrote in
> message news:A40D230A-3579-4D31-930C-(E-Mail Removed)...
> > 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 Release
> > '---------------------------------------------------------------------------------------
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in
message newsAD8E862-3789-4D7F-BECF-(E-Mail Removed)...
> 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 Release
> '---------------------------------------------------------------------------------------
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in
>> message news:A40D230A-3579-4D31-930C-(E-Mail Removed)...
>> > 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 Release
>> > '---------------------------------------------------------------------------------------
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>>
>>
>>

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" wrote:

> 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" <(E-Mail Removed)> wrote in
> message newsAD8E862-3789-4D7F-BECF-(E-Mail Removed)...
> > 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 Release
> > '---------------------------------------------------------------------------------------
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in
> >> message news:A40D230A-3579-4D31-930C-(E-Mail Removed)...
> >> > 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 Release
> >> > '---------------------------------------------------------------------------------------
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

SUCCESS !

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

bob

"Daniel Pineault" <(E-Mail Removed)> wrote in
message news:0EC5E8C9-70BB-4A99-B117-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in
>> message newsAD8E862-3789-4D7F-BECF-(E-Mail Removed)...
>> > 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 Release
>> > '---------------------------------------------------------------------------------------
>> > 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" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in
>> >> message news:A40D230A-3579-4D31-930C-(E-Mail Removed)...
>> >> > 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 Release
>> >> > '---------------------------------------------------------------------------------------
>> >> > 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" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>