Change field properties automatically

[Alex]

Hi, I have a bunch of fields that are set as text (Access default) and
I'd like to write a macro (or anything that'd work!) to change most of
them to 'Number', 'double precision'. Is that feasible or do I have to
change them by hand??
Thanks

Alex

 

[Joseph Meehan]

Hi, I have a bunch of fields that are set as text (Access default) and
I'd like to write a macro (or anything that'd work!) to change most of
them to 'Number', 'double precision'. Is that feasible or do I have to
change them by hand??
Thanks

Alex

If you have so many fields that need to be changed, I suspect there is a
problem with your table design or with the way you are importing data.

I don't know a way of automating the process, but it is not difficult to
do manually.

Open the table, change to design view. highlight the field you want to
change and click on the data type. Remember there are a number of different
types of number fields. Make sure you are choosing the one that fits you
needs best.

 

[RoyVidar]

Hi, I have a bunch of fields that are set as text (Access default)
and I'd like to write a macro (or anything that'd work!) to change
most of them to 'Number', 'double precision'. Is that feasible or do
I have to change them by hand??
Thanks

Alex

I think I'd try to find methods of creating tables which involved
setting datatypes too, so I'll assume this is a one time operation.

Here's a very short thingie without any errortesting or anything, which
should give you the possibility of altering those you want. I'll use
ADO and DDL, though it surely possible through other methods that'll
work too.

If using DDL is OK, though it seem to be a bit frowned upon in these
NGs, here's an article (with links to more) - watch for linebreaks
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Sub AlterFieldsToDouble(v_strTable As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, v_strTable, Empty))
Do While Not rs.EOF
If rs.Fields("DATA_TYPE") = adWChar Then
If (MsgBox("Change '" & _
rs.Fields("COLUMN_NAME").Value & "'?", _
vbOKCancel) = vbOK) Then
cn.Execute _
"ALTER TABLE [" & v_strTable & " ] " & _
"ALTER COLUMN [" & _
rs.Fields("COLUMN_NAME").Value & "] DOUBLE"
rs.MoveNext
End If
End If
Loop
End Sub

 

[Allen Browne]

Alex, you already have 2 answers, explaining how to do it manually
(Joseph's) and programmatically (Roy's), but something is bothering me here.

If you have a table with lots of fields, and most of them should be numeric
rather than text, it sounds to me like this is a big spreadsheet. I'm
guessing you have lots of columns of similar data, such as:
Item1Price, Item2Price, ...
or
FirstScore, SecondScore, ...

If so, you probably need to have a related table with many *records*, rather
than this monster table with many fields. You may be able to get some help
from the table analyzer:
Tools | Analyze | Table

If that's a new concept, the keyword to find more info is "normalization".
Here's a start:
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
and lots more links:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101