Re: Change Text to Memo



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" <DanielPineault@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:DAD8E862-3789-4D7F-BECF-3F5E7C420391@xxxxxxxxxxxxxxxx
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" <DanielPineault@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:A40D230A-3579-4D31-930C-57EF6B2DD0C5@xxxxxxxxxxxxxxxx
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









.



Relevant Pages

  • RE: Change Text to Memo
    ... Switch a given table field to Memo ... Function SwitchFieldType(sTableName As String, sFieldName As String) ... Dim sSQL As String ... If Err.Number = 0 Then Exit Function ...
    (microsoft.public.access.modulesdaovba)
  • Re: Change Text to Memo
    ... Sorry I didn't initially mention that the database is elsewhere. ... Switch a given table field to Memo ... Function SwitchFieldType(sDb As String, sTableName As String, sFieldName ... Dim sSQL As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Change Text to Memo
    ... Switch a given table field to Memo ... Function SwitchFieldType(sDb As String, sTableName As String, sFieldName As ... Dim sSQL As String ... If Err.Number = 0 Then Exit Function ...
    (microsoft.public.access.modulesdaovba)
  • RE: Writing Word Document from Access VBA
    ... Dim sSQL As String ... Dim rs2 As Recordset ... wdLineStyleNone ...
    (microsoft.public.access.modulesdaovba)
  • Re: proposal: reswitch
    ... > which levels of loops and/or switches and/or ... socket -server command ?-myaddr addr? ... You've got the quick-exit (break, no string), the fall-through to next ... -switch option), then it'd be the nth switch. ...
    (comp.lang.tcl)