Changing Linked Table Name

[Crystal (strive4peace)]

Hi Jack,

wow! I am really impressed with how well you are able to
explain your 'business survival' approach to application
development from a business manager perspective. What you
said makes a lot of sense and helps someone like me, who is
a developer, better understand how a database evolved before
I get called in to re-engineer it. As with anything you
build, at some point it is wise to stop using duct tape and
bailing wire ... and design for optimum flexibility and
stability.

Once your application has lots of data in it, the data
itself can tell you a lot and help drive a more efficient
structure design.

I was so inspired by the obvious time you took to share your
wisdom that I posted something I believe will be a great
help to you.

Create a Data Dictionary (and more) for any Access Database

My Analyzer ... the ultimate Data Dictionary, by Crystal
http://www.AccessMVP.com/strive4peace/Dictionary

going back to your original post, you said, "I have half a
mind to write a module that, given passed table names, will
relink the tables, ... "

One thing my Analyzer does for you is make linking to
specified BE (back-end) tables quick by making it easy to
create a list of the tablenames -- then all you have to do
is loop through them and re-link, or delete links and
reestablish a connection (if you want code, just ask and
I'll post it).

To create a list of table names in your database without
having to get them one-by-one or write code to loop through
the tabledefs to create the list of names, use a make-table
query from the analyzer's Tables table (for a particular RunID)

from my web page:
"Tip: use the tablenames stored in the analyzer's Tables
table to make a list of tablenames in a back-end (BE) that
you want to automatically link to from the front-end (FE). "

Another thing you can use the Analyzer data for is to export
the table and field names to Excel (you can start with the
qFields query -- but save your changes with a new name) --
then create a column in Excel for the new table or field
name. This enables you to plan your name changes
(additions, deletions, etc) and also have a list for V-Tools
DeepSearch

~~~

Thank you also for highlighting Luke's article. This is
especially profound: "The 100% solution may be irrelevant
when it's done"

Here is another article written by him that I pass the link for:

Error Handling and Debugging Tips and Techniques...
by Luke Chung, President of FMS, Inc.
http://www.fmsinc.com/tpapers/vbacode/Debug.asp

~~~

Parts --> Items

yes, I also use an Items table in applications I build --
and not just for manufacturing ... When you have expenses to
a project, it is nice to be able to store expense amounts in
one place and classify the expense types somewhere else. An
item could be a quantity in hours of labor or a ream of paper.

Since you do manufacturing, I expect you need a way to break
an Item down into its individual parts and assemblies so an
assembly of items can also be listed in the Items table (on
any level) and you can store a BOM (Bill of Materials) ...
not just for top-level items. You need to be able to drill
down several levels. This starts getting to the structure
you will need to integrate with your CAM software as you
need to store each step of the process.

You probably also need to come up with ways to store
flexible ordering and pricing structures; manufacturers,
OEMs, suppliers and product lines; revenue and costs;
repairs and rebuilding; parts, components, assemblies;
linking superseded parts forward and backward; inventory in
each location; jobs and projects; customers, sales, and
invoicing; and general accounting information.

~~~
"Modified object names are merely one of hundreds of things
that surface because of this requirement for balance ...
pilot training"

Absolutely! Loved your points on balance and pilot analogy


Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access
(also includes links for Whistles and Bells, VBA chapters,
sample databases, etc)

*
have an awesome day
*

 

[David C. Holley]

The fact remains that your business has a vested financial interest in
adapting as quickly as possible with a minum of cost to the changing
business environment. Investing in a little extra development time at the
outset *will* aford you the opportunity to reduce time later. That is an
indisputable fact regardless of industry, line of business and organization.

 

[David C. Holley]

The fact remains that your business has a vested financial interest in
adapting as quickly as possible with a minum of cost to the changing
business environment. Investing in a little extra development time at the
outset *will* aford you the opportunity to reduce time later. That is an
indisputable fact regardless of industry, line of business and organization.

 

[Jack Leach]

your business has a vested financial interest
False... I do this work on my own time, at no cost to my business

interest in
adapting as quickly as possible with a minum of cost to the changing
business environment. Obviously...

Investing in a little extra development time at the
outset *will* aford you the opportunity to reduce time later.

True in some cases, but in some cases this extra development time is not
available. If your boss comes in your office and tells you he needs a brand
new hot report made up in a matter of ten minutes or you will lose your job,
and you suspect it will take twenty to create the report on the fly, you do
not spend fourty minutes creating a template that you can re-use mext week.
You make the report, asafp, and build the template later.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

Thank you Crystal, I am immensely flattered by your praise. And if the
explaination of my approach to development has in fact given you a better
understanding of a career that a coveted MVP must already know so well...
there are no words I can say to express how that makes me feel.

Thank you also for the links. It still amazes me that after so much time
frequenting these newsgroups I still come across so many pieces of gold that
I had no idea existed.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[David C. Holley]

False... I do this work on my own time, at no cost to my business

The fact remains that your business has a vested financial interest in
adapting as quickly as possible with a minum of cost to the changing
business environment.

Ask that statement of the executives in your organization at a macro-level
and you'll see that it is a true statement. On a micro-level, even if its
something like developing a DB on your own time, with compensation only
being provided by your salary, the principle remains the same. The company
will realize a financial benefit from faster adaption in general and in your
application specifically. You personally will also realize a benefit in that
it will free up your personal time.

True in some cases, but in some cases this extra development time is not
available. If your boss comes in your office and tells you he needs a
brand
new hot report made up in a matter of ten minutes or you will lose your
job,
and you suspect it will take twenty to create the report on the fly, you
do
not spend fourty minutes creating a template that you can re-use mext
week.
You make the report, asafp, and build the template later.

My organization has a simply rallying cry - Do it right, first time,
everytime. If you're in an environment where your job is on the line if you
don't have a report within 10 minutes, you need to find a new job. The whole
discussion has *not* been about specific functionality, but rather about the
application as a whole. Furthermore, if you are doing this on your own time,
its in your own vested interest to think about the overall design because
its going to save you *PERSONAL* time later on. If you're having to do this
on your own time, then obviously the application isn't a priority for your
organization or you'd be working on it on the clock or your organization
would hire someone (or assign you) to work on it full time. At any rate, if
you're having to do this on your own personal time, compensated only by
salary, I would highly recommend that you reevaluate the circumstances
because you're obviously not being undercompensated.

 

[Crystal (strive4peace)]

Hi Jack,

you're welcome and thank YOU. You are obviously wise and
very good at what you do. I appreciate the time you took to
share your well-thought-out perspective.

"I still come across so many pieces of gold that I had no
idea existed."

gold is all over the place ... <smile>
I am thankful for the many forums and newsgroups where we
exchange information and links with each other ... and the
friendships that inevitably take root


have an awesome day


Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access
(also includes links for Whistles and Bells, VBA chapters,
sample databases, etc)

 

[david]

I was trying to work out why this was so much of a problem,
and then it clicked: You are using the same name for the
table link as for the table.

And when you change the table name, you are changing the
link name.

That's not necessary. It's not even necessarily a good idea.

The canonical example is "manifest constants". A manifest
constant is a number like 7, or a character like 'a'.

The standard solution is to give all manifest constants names,
so that the code remains the same when the value changes.

Giving your table name a fixed value works well for small
static applications: it's like using 7 or 'a' when you mean 7
or 'a'. For larger evolving applications, it makes sense to use
OVERDUE_PAYMENT_DAYS and ID_CODE_FIELD.

For larger evolving applications, it makes sense to stop
worrying about matching the FE link name to the BE table
name.

(david)

 

[AccessVandal via AccessMonster.com]

lol, yes that true

That's not necessary. It's not even necessarily a good idea.

lol, yes that true. Why bother?

The canonical example is "manifest constants". A manifest
constant is a number like 7, or a character like 'a'.

The standard solution is to give all manifest constants names,
so that the code remains the same when the value changes.

Giving your table name a fixed value works well for small
static applications: it's like using 7 or 'a' when you mean 7
or 'a'. For larger evolving applications, it makes sense to use
OVERDUE_PAYMENT_DAYS and ID_CODE_FIELD.

But there's no guarantee, the management might decide to rename or decide to
stop calling that name anymore and just call it "OVERDUES". I even have
Account Codes renamed and changed to a newer version.

For larger evolving applications, it makes sense to stop
worrying about matching the FE link name to the BE table
name.

So inconclusion, just leave as it is. If ain't broke, don't fix it. Only a
moron would call it a bad design.

 

[David C. Holley]

lol, yes that true


lol, yes that true. Why bother?


But there's no guarantee, the management might decide to rename or decide
to
stop calling that name anymore and just call it "OVERDUES". I even have
Account Codes renamed and changed to a newer version.


So inconclusion, just leave as it is. If ain't broke, don't fix it. Only a
moron would call it a bad design.

That's precisely what I reiterated several times. A developer of average
intelligence should be able to make the intellectual leep that a table named
'Automobiles' also contains records for trucks, busses and mobile cranes.
The fact that 'Vehicles' is a more accurate name is beside the point and
does not neccessarily warrant changing the table name.

 

[david]

For us it started with Name Length and Name Character
restrictions when we used SQL Server.

That forced us to change the BE table name, but did not
require a change to the FE table name.

Before that we were using Speed Ferret or FMS tools to
do global search-and-replace, so it wasn't impossible. It
was just a realization that the FE name could be decoupled
from the BE name.

(david)

 

[David C. Holley]

Right because Access allows the table name for the link to be renamed
because it is the path of the link that matters not the name, which in your
scenario was fine because you weren't dealing with anything on the front
end.