Re: Changing Stubborn Data Type

EricaLRivera@xxxxxxxxx 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


.