By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,566 Members | 1,794 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,566 IT Pros & Developers. It's quick & easy.

Changing Stubborn Data Type

P: n/a
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

Nov 29 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 29 '05 #2

P: n/a

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

Nov 29 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.