Changing Stubborn Data Type

[EricaLRivera]

Hi Y'all I was hoping someone out there might be able to help me.

I have an access program that needs to change the data types
programatically. I have tried to do an alter column to change the
original data type from binary to text, but that did not work. The
current section of code I have is the following:

Public Function ChangeColumnType(ByVal StrgTableName As String, _
strgColumnName As String, StrgNewDataType) As Boolean

On Error GoTo Err_ChangeColumnType

DoCmd.RunSQL "ALTER TABLE [" & StrgTableName & "] ADD COLUMN [" &
strgColumnName & "-NEW] " & StrgNewDataType
DoCmd.RunSQL "UPDATE [" & StrgTableName & "] SET [" &
strgColumnName & "-NEW] = [" & strgColumnName & "]"
DoCmd.RunSQL "ALTER TABLE [" & StrgTableName & "] DROP COLUMN [" &
strgColumnName & "]"
ChangeColumnType = RenameColumn(StrgTableName, strgColumnName &
"-NEW", strgColumnName)

Exit_ChangeColumnType:
Exit Function

Err_ChangeColumnType:
MsgBox "There was an Error while changing the Table Column [" &
strgColumnName & "] to [" & _
StrgNewDataType & "] within the Table named [" & StrgTableName & "]
" _
, vbCritical, "Column Retype Error"
GoTo Exit_ChangeColumnType
End Function
Unfortunately this does not work. For some reason it wont drop the old
attibute and rename the newly created attribute. Instead it errors
out. Anyone have some suggestions? I would appreciate any and all
help.
Thanks,
Erica

[Br]

Er**********@gmail.com wrote:

Hi Y'all I was hoping someone out there might be able to help me.

I have an access program that needs to change the data types
programatically. I have tried to do an alter column to change the
original data type from binary to text, but that did not work. The
current section of code I have is the following:

Public Function ChangeColumnType(ByVal StrgTableName As String, _
strgColumnName As String, StrgNewDataType) As Boolean

On Error GoTo Err_ChangeColumnType

DoCmd.RunSQL "ALTER TABLE [" & StrgTableName & "] ADD COLUMN [" &
strgColumnName & "-NEW] " & StrgNewDataType
DoCmd.RunSQL "UPDATE [" & StrgTableName & "] SET [" &
strgColumnName & "-NEW] = [" & strgColumnName & "]"
DoCmd.RunSQL "ALTER TABLE [" & StrgTableName & "] DROP COLUMN [" &
strgColumnName & "]"
ChangeColumnType = RenameColumn(StrgTableName, strgColumnName &
"-NEW", strgColumnName)

Exit_ChangeColumnType:
Exit Function

Err_ChangeColumnType:
MsgBox "There was an Error while changing the Table Column [" &
strgColumnName & "] to [" & _
StrgNewDataType & "] within the Table named [" & StrgTableName & "]
" _
, vbCritical, "Column Retype Error"
GoTo Exit_ChangeColumnType
End Function
Unfortunately this does not work. For some reason it wont drop the
old attibute and rename the newly created attribute. Instead it
errors out. Anyone have some suggestions? I would appreciate any
and all help.
Thanks,
Erica

As usual I'm going to as "why???". I simply can't see a good reason to
ever dynamically change the database table design like this.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Lyle Fairfield]

Er**********@gmail.com wrote:

I have an access program that needs to change the data types
programatically. I have tried to do an alter column to change the
original data type from binary to text, but that did not work.

What version of Access?

This works in 2003.

CurrentProject.AccessConnection.Execute "ALTER TABLE SecondTable ALTER
COLUMN Product1 Text (255)"