Need Help with Function to change Data Type!!!!!

[msfgreen]

I am trying to use this code to change data type at run time but I am getting "SUB OR FUNCTION NOT DEFINED" error when the code is executed, please advise!!!

Option Explicit

       '*****************************************************************
      ' The AlterFieldType Sub procedure requires three string
      ' parameters. The first string specifies the name of the table
      ' containing the field to be changed. The second string specifies
      ' the name of the field to be changed. The third string specifies
      ' the new data type for the field.
      '*****************************************************************

      Sub AlterFieldType(TblName As String, FieldName As String, _
         NewDataType As String)
         Dim db As DATABASE
         Dim qdf As QueryDef
         
         Set db = CurrentDb()

         ' Create a dummy QueryDef object.
         Set qdf = db.CreateQueryDef("", "Select * from arcusts")

         ' Add a temporary field to the table.
         qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN_"
       AlterTempField " & NewDataType"
         qdf.Execute

         ' Copy the data from old field into the new field.
         qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET_"
            AlterTempField = [" & FieldName & "]"
         qdf.Execute

         ' Delete the old field.
         qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN ["_
            & FieldName & "]"
         qdf.Execute

         ' Rename the temporary field to the old field's name.
         db.tabledefs("[" & TblName & "]").Fields("AlterTempField")._
            NAME = FieldName
         ' Clean up.
      End Sub

the problem start at AlterTempField " &NewDataType " is where I am getting the "SUB OR FUNCTION NOT DEFINED".

 

[THoey]

This is just a guess, but I think the following line is wrong:

qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN_"
AlterTempField " & NewDataType"

The "_" is a line continuation character in Access. I think this line is supposed to be:

qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN " _
& AlterTempField & " " & NewDataType & ";"

Again, I think. But, then again, I was always taught that if you needed to change the field's type in code, you probably didn't do enough designing.

Hope that helps...
Terry

 

[msfgreen]

Thank you for your response, the reason that I'm changing the data structure is because we are importing this data from an accounting program to design some special reports. To achieve this some of the field data type must be changed. I tried the suggestion that was made with no success. Thank you for your help!!!!!

 

[msfgreen]

Thank you for your support with your suggestion I was able to correct code. The following is the correct code:

'*****************************************************************
      ' The AlterFieldType Sub procedure requires three string
      ' parameters. The first string specifies the name of the table
      ' containing the field to be changed. The second string specifies
      ' the name of the field to be changed. The third string specifies
      ' the new data type for the field.
      '*****************************************************************

      Sub AlterFieldType(TblName As String, FieldName As String, _
         NewDataType As String)
         Dim db As DATABASE
         Dim qdf As QueryDef
         'Dim AlterTempField As String
         Set db = CurrentDb()

         ' Create a dummy QueryDef object.
         Set qdf = db.CreateQueryDef("", "Select * from arcusts")

         ' Add a temporary field to the table.
         qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN AlterTempField " & NewDataType
         qdf.Execute

         ' Copy the data from old field into the new field.
         qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET & AlterTempField & " = ";"
         qdf.Execute

         ' Delete the old field.
         qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN [" & FieldName & "];"
         qdf.Execute

         ' Rename the temporary field to the old field's name.
         db.TableDefs("[" & TblName & "]").Fields("AlterTempField").NAME = FieldName
         ' Clean up.
      End Sub