Capture Data Type

[Donna]

How do I capture the data type of each field in a table?

Found how to change the data type through code; now I need
to find all the fields set to int and change to text.

Thank you in advance for your help!

 

[Kel]

You could try using a for...each statement:

e.g.

Dim dbs as object
dim fld as dao.field

set dbs = CurrentDB

For Each fld in dbs.tabledef(<TableName>).Fields
if fld.properties("Type").Value = 4 then 'field type is number
'code to set data type
end if
Next fld


If you want to loop through all fields in all of your tables rather than
one:

Dim dbs as object
dim fld as dao.field
dim tblDef as tabledef

set dbs = CurrentDB

For each tblDef in dbs.tabledefs
For Each fld tbldef.Fields
if fld.properties("Type").Value = <Value Type> then
<code to set data type>
end if
Next fld
Next tblDef

 

[Donna]

Tried both of these and keep getting an error message
Object does not support this method.?

 

[Kel]

Do you know which line is causing this error? You do have to put in some
error trapping in case the propery doesn't already exist in the properties
collection, then you have to create it, the error-handling could be
something like:

On Error GoTo Err_Get_Property
Dim prp As Variant

Get_Property = obj.Properties(prpName).Value


Exit_Get_Property:
Set prp = Nothing
Exit Function

Err_Get_Property:
If Err = 3270 Then ' Property not found.
Set prp = obj.CreateProperty(prpName, prpType)
obj.Properties.Append prp
Resume Next
Else
Call Error_Message(Err.Number)
Resume Exit_Get_Property
End If
End Function

However if its not your property that's causing the error, then this will be
no good and you'll need to figure out what line in your code is causing the
error!

Kel

 

[Tim Ferguson]

now I need
to find all the fields set to int and change to text.

Huh???

Tim F

 

[Donna]

Trying to find all fields in a table that are set to
number and set them to text.

 

[Tim Ferguson]

Trying to find all fields in a table that are set to
number and set them to text.

I understood the question, but I was amazed at the idea of anyone actually
doing it, that's all. Not my business, of course, and I am sure there are
reasons for wanting it, but certainly worth a double-take or two.

B wishes


Tim F