Data Fields Changing

S

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?
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top