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

Here's code that could do the job, but I'd be real cautious about losing
existing data.

Private Sub TestThis()
Dim fld As Field
Dim dbs As DATABASE
Dim tbl As TableDef

Set dbs = CurrentDb()
'
'== Do the DATATYPE Change to Integer
'
For Each tbl In dbs.TableDefs
 If Left$(tbl.NAME, 2) = "t_" Then
    For Each fld In tbl.Fields
        If fld.NAME <> "t_proj" And fld.NAME <> "t_pt" And _
           fld.NAME <> "t_plink" And fld.NAME <> "ptlink" _
           And fld.NAME <> "t_species" Then
            Debug.Print "Before change:  - " & tbl.NAME & "   - field Name
/Type : " & fld.NAME & "/" & fld.Type
           
    dbs.Execute "ALTER TABLE " & tbl.NAME _
        & " DROP column " & fld.NAME & ";"
    dbs.Execute "ALTER TABLE " & tbl.NAME _
        & " Add column " & fld.NAME & " integer;"
            'Debug.Print "After change:  - " & tbl.NAME & "   - field Name
/Type : " & fld.NAME & "/" & fld.Type
        Else
        End If
    Next fld
 Else
 End If
Next tbl
dbs.Close
'
'===CHECK The results of datatype change ==============

Set dbs = CurrentDb
For Each tbl In dbs.TableDefs
 If Left$(tbl.NAME, 2) = "t_" Then
    For Each fld In tbl.Fields
        If fld.NAME <> "t_proj" And fld.NAME <> "t_pt" And _
           fld.NAME <> "t_plink" And fld.NAME <> "ptlink" _
           And fld.NAME <> "t_species" Then
    Debug.Print "After change:  - " & tbl.NAME & "   - field Name /Type : "
& fld.NAME & "/" & fld.Type
        Else
        End If
    Next fld
 Else
 End If
Next tbl

Beep
MsgBox "Done"
dbs.Close
End Sub


jack
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Paul Black
Sent: Tuesday, September 14, 2004 6:17 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Problem with code to change field data type in vba


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_' excluding certain fields listed above. 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

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com