updating data type of many fields

[Guest]

I've inherited a database in which we store survey questions and answers. For
some reason, the person who designed it designated survey answers as text
despite the fact that they need to be numbers for various calculations. Most
of the Fields have similar names--like Q1, Q2, Q3, etc. Occasionally there
are names like Q32a.
There are a lot of questions, and I would like to avoid changing them all
manually.

How could I use VBA to change the Data Type for a large number of fields
automatically.
Thanks,

 

[Andi Mayer]

I've inherited a database in which we store survey questions and answers. For
some reason, the person who designed it designated survey answers as text
despite the fact that they need to be numbers for various calculations. Most
of the Fields have similar names--like Q1, Q2, Q3, etc. Occasionally there
are names like Q32a.
There are a lot of questions, and I would like to avoid changing them all
manually.

How could I use VBA to change the Data Type for a large number of fields
automatically.
Thanks,

Use SQL
ALTER TABLE table ALTER COLUMN field type[(size)] |

this is out of the help file

 

[Guest]

I have been unable to find the Help File in question. Would you mind passing
on it's title?
Where in Access can I use this statement? I know little about VBA (and most
of what I do know VBA in Excel and Word), and I know less about SQL.

Thanks,

tj

 

[Andi Mayer]

I have been unable to find the Help File in question. Would you mind passing
on it's title?
Where in Access can I use this statement? I know little about VBA (and most
of what I do know VBA in Excel and Word), and I know less about SQL.

its in MS Jet SQL Reference

in VBA :
make a new module
first check in you have a reference for DAO (Tools--references)

Sub test()

CurrentDb.Execute "ALTER TABLE myTableName " _
& " ALTER COLUMN MyFieldName INTEGER", dbFailOnError

End Sub

change myTableName with your TableName and
MyFieldName with the field you want to change

INTEGER means it will be a Long (a Integer , no commas)

hope this help

 

[Guest]

Thanks, I'll give it a shot.

tj

its in MS Jet SQL Reference

in VBA :
make a new module
first check in you have a reference for DAO (Tools--references)

Sub test()

CurrentDb.Execute "ALTER TABLE myTableName " _
& " ALTER COLUMN MyFieldName INTEGER", dbFailOnError

End Sub

change myTableName with your TableName and
MyFieldName with the field you want to change

INTEGER means it will be a Long (a Integer , no commas)

hope this help

 

[Guest]

I'm apparently a little slow on the uptake...
I can get this to work fine for 1 column at a time, but I need to update
several hundred. Is it possible to make this work for multiple many columns
at once?

tj

 

[Andi Mayer]

I'm apparently a little slow on the uptake...
I can get this to work fine for 1 column at a time, but I need to update
several hundred. Is it possible to make this work for multiple many columns
at once?

this works only for a single field

BTW: several hundred is not possible: maximum is 255 per Table

do you mean you have different tables?

I will give you a hint what else you can do:

taking the field names from your first post:
all starts with Q

ATTENTION!!!!!!!
try this on a copy, it is changing EVERY field in your database which
start with Q

Sub test2()
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Dim fld As DAO.Field
Set db = CurrentDb

For Each tbl In db.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then
For Each fld In tbl.Fields
If Left(fld.Name, 1) = "Q" Then
db.Execute "ALTER TABLE " & tbl.Name _
& " ALTER COLUMN " & fld.Name & " INTEGER", dbFailOnError
End If
Next fld
End If
Next tbl

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
End Sub

 

[Guest]

Yes, there are multiple talbe involved. I'll give this a shot. Thanks for the
insights.

tj