[AccessD] Problem with code to change field data type in vba

You are missing  a statement Set tbl = dbs.TableDefs("Table1")   however 
this method wont work.

For Access 97 with DAO
 '*****************************************************************
      ' 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 PROD1")

         ' 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
       
For ADO with 2000 , you could also do this with ADO and 97 by setting an 
ADO reference
 and by setting the connection string directly like so

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\somepath\myDb.mdb;" & _
           "User Id=admin;" & _
           "Password="

Sub sChangeDataType()
    Dim cnn As ADODB.Connection
    Dim strSQL As String
    Set cnn = CurrentProject.Connection
 ' however this sql string  might work in Access 97 if using Jet 4.0 
rather than 3.51
'by selecting a reference to DAO 3.6 rather than DAO 3.51
    strSQL = "ALTER TABLE [Employees] ALTER COLUMN Extension int"
    cnn.Execute strSQL
end sub
 
See also
http://support.microsoft.com/default.aspx?scid=kb;EN-US;210304
http://support.microsoft.com/default.aspx?scid=kb;EN-US;128016

Paul Black wrote:

> Hi
>
> I using Access 97 and can not get the following to work:
>
> Private Sub Command0_Click()
> Dim fld As Field
> Dim dbs As Database
> Dim tbl As TableDef
>
> Set dbs = CurrentDb()
> For Each fld In tbl.Fields
>    If Left$(tbl.Name, 2) = "t_" And fld <> "t_proj" And fld <> "t_pt" 
> And fld <> "t_plink" And fld <> "ptlink" And fld <> "t_species" Then
>        fld.Type = dbInteger
>    End If
> Next fld
>
> Beep
> MsgBox "Done"
> End Sub
>
> It is supposed to change the field data type to integer from text. All 
> field names to change begin with 't_       
> 'excludingcertainfieldslistedabove.I
> keep getting an error:
>
> Runtime error '91'
> Object Variable or With Block variable not set
>
> Other than the excluded fields above all the data conatined in the 
> fields is numeric or has a Null value... but it is in a text field. 
> The data is coming to me off of a Pocket PC and it won't let me change 
> the field type to numeric.
>
> Please help me solve this.
>
> Thanks
>
> PB
>
> _________________________________________________________________
> Get ready for school! Find articles, homework help and more in the 
> Back to School Guide! http://special.msn.com/network/04backtoschool.armx
>

-- 
Marty Connelly
Victoria, B.C.
Canada