Change Data Type in a Field Common to 200 Tables

[dbsavoy]

I've got 200 relatively small tables in Access each with the same fields.
Each of these fields have Text as Data Type. There is one field in each
table that I need to change from Text to Number. I really don't want to do
this manually 200 times.

Can someone help me with VBA code to automate this process?
Thx!

 

[Steve Schapel]

Dbsavoy,

First thing I have to ask... Is this table structure set in concrete,
or could you consider a review of your data model? Sorry, but 200
tables with the same fields means something seriously wrong.

 

[dbsavoy]

No, nothing's wrong. These are tables I've imported from .CSV files. Each
represents a unique product.
Thanks,
Dave

 

[Steve Schapel]

Dave,

This is what is sometimes known as the "tables as data" trap. In
database terminology, "each table represents a unique product" is called
*un-normalised*. All of this data should be in one table, with an
additional field to identify each record according to the product it
represents. This can be achieved by running an Append Query as part of
the process of importing from CSV.