Data Fields Changing

[Steve Stad]

I have a Master Table in a Master DB linked to a few sub databases. The
input forms (to update Master table) are in the sub databases based on
queries also in the sub db's. The field names in the Master table change
names occasionally. Is there an efficient way to update code in modules,
forms, and control/fields in the form(s) when the fields change in the Master
table. I can update the control source/fields in the form for the new fld
names - but I noticed the old/previous field names still in the dropdown
window (at top left of Font window). Also do I have to update all field
names in the code/modules and re-insert new fields on form? Am I doing
un-necessary/extra work when the field names change or is there a more
efficient way to update or plan for field name changes?

 

[Gina Whipp]

Steve,

Though I am slightly confused by your meaning of *sub databases* I think
this will work for you... http://www.rickworld.com/products.html (Sorry
don't know of a free one that actually works).

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Clifford Bass via AccessMonster.com]

Hi Steve,

If the names of the table columns change, but that is all, then you
could simply hide the problem behind a query. Make sure you have a backup of
the front end. We are talking a front end here aren't we? Create a new link
to the table and give it a new name. Create a query that uses the new table
and aliases (renames) any columns as needed. When you save it, save it as
the current table name. Delete the old table link. All should work as
before. In the future when column names change, all you have to do is fix
the query.

So if your original table link is "tblData" and its fields are Col1,
Col2 and Col3, you would create a new table link to it called something like
"tblTheData". Then you would create a query named "tblData" that simple does
"select Col1, Col2, Col3 from tblTheData". Then delete "tblData". When the
back end "tblData" column names change to Fld1, Fld2 and Fld3, you update the
link to "tblTheData" and and then change the "tblData" query to be "select
Fld1 as Col1, Fld2 as Col2, Fld3 as Col3 from tblTheData". And you are done.
Make sure you document that you are naming a query to look like a table. Or
name the query "qryData" and then go through all of your code and queries and
forms and do a one-time replacement of "tblData" with "qryData".

Hope that helps,

Clifford Bass