replace table without damaging the data after split

Ouch! That was a little harsh. At least I can take
confort in that I'm in some good company as
evidenced by these Oracle system tables. ;-)

Table SYS.SQL$
SIGNATURE NUMBER
NHASH NUMBER
SQLAREA_HASH NUMBER
LAST_USED DATE
INUSE_FEATURES NUMBER
FLAGS NUMBER
MODIFIED DATE
INCARNATION NUMBER
SPARE1 NUMBER
SPARE2 VARCHAR2

TABLE SYS.CON$
OWNER# NUMBER
NAME VARCHAR2
CON# NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2
SPARE5 VARCHAR2
SPARE6 DATE
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"David W. Fenton" wrote:

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=
wrote in
:

In the future add a few extra fields in each table. Something like
a couple of text (255), number (Double), Date, and maybe a Yes/No
or Memo. That way they are already there if you need them. I often
do this with new databases where it's possible to miss a
requirement or two.

I think that's a bloody idiotic idea.

First off, you shouldn't be routinely changing table structures on
an app that's in pfoduction use.

Secondly, if you are forced to do so, the client should pay you to
work with the original back end, either onsite, or via remote
access.

Failing that, they have to pay you to program it.

The easiest way to do it is to put a hidden, empty copy of the new
table structure in your front end. Then have code that renames the
original table, copies the empty table in its place, then append the
old table's data in the new table, and last of all, adjust the
relations (if there are any) to refer to the new table (when you
rename a table, it takes its original relationships with it).

An interim solution if you can't do that is to create a 1:1 side
table for the new fields and use that until you can get onsite to
adjust the original table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=
wrote in
:

Ouch! That was a little harsh. At least I can take
confort in that I'm in some good company as
evidenced by these Oracle system tables. ;-)

Table SYS.SQL$
SIGNATURE NUMBER
NHASH NUMBER
SQLAREA_HASH NUMBER
LAST_USED DATE
INUSE_FEATURES NUMBER
FLAGS NUMBER
MODIFIED DATE
INCARNATION NUMBER
SPARE1 NUMBER
SPARE2 VARCHAR2

No, you're in very *bad* company. Many of the data structures you
find in commercial applications are hideously bad, denormalized, and
often the result of legacy requirements that reflect decades-old
technology.

You should not be proud at all to be doing something that is wrong
just because large, successful companies do it wrong, too.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

But this not decades old technology nor an application someone built. I'm
talking about the system tables (like Access' MSys tables) in Oracle 10g
which is a relatively new product.

I think that we should agree to disagree.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"David W. Fenton" wrote:

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=
wrote in
:

Ouch! That was a little harsh. At least I can take
confort in that I'm in some good company as
evidenced by these Oracle system tables. ;-)

Table SYS.SQL$
SIGNATURE NUMBER
NHASH NUMBER
SQLAREA_HASH NUMBER
LAST_USED DATE
INUSE_FEATURES NUMBER
FLAGS NUMBER
MODIFIED DATE
INCARNATION NUMBER
SPARE1 NUMBER
SPARE2 VARCHAR2

No, you're in very *bad* company. Many of the data structures you
find in commercial applications are hideously bad, denormalized, and
often the result of legacy requirements that reflect decades-old
technology.

You should not be proud at all to be doing something that is wrong
just because large, successful companies do it wrong, too.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Frank Situmorang wrote:

My question is how can I replace the table after modification without
loosing the data. Users already having data. in the BE.

FWIW I've been using the following product the last year. While it
has it's quirks it does a good job of creating the necessary VBA to
update BE tables, fields, indexes and relationships.

Compare'Em
http://home.gci.net/~mike-noel/Compa.../CompareEM.htm

I also use a version number table in the backend which I update as I
run the code updating the various versions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

"David W. Fenton" wrote:

I think that's a bloody idiotic idea.

That's a bit harsh.

First off, you shouldn't be routinely changing table structures on
an app that's in pfoduction use.

I am always adding fields, tables, indexes and relationships in my
clients backends as I create new versions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

"Tony Toews [MVP]" wrote in
:

"David W. Fenton" wrote:

I think that's a bloody idiotic idea.

That's a bit harsh.

But it *is* what I think.

First off, you shouldn't be routinely changing table structures on
an app that's in pfoduction use.

I am always adding fields, tables, indexes and relationships in my
clients backends as I create new versions.

I don't. Anything like that is a major application revision and
requires careful plannning. Any app that has frequent updates to the
back end is one that was rushed into production without adequate
planning.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote:

First off, you shouldn't be routinely changing table structures on
an app that's in pfoduction use.

I am always adding fields, tables, indexes and relationships in my
clients backends as I create new versions.

I don't. Anything like that is a major application revision and
requires careful plannning. Any app that has frequent updates to the
back end is one that was rushed into production without adequate
planning.

Then we agree to disagree.

I also don't do a lot of careful planning and deep in depth interviews
about their requirements. I prefer to get a rough idea, give them an
estimate and then a preliminary system that they can use that I tell
them will only be 80% effective. Then they give me immediate feedback
while we're going through it and feedback as they use it over the next
while. I the meantime I work on the 20% neither they or I knew about.
And I use code to update the tables, fields, indexes and
relationships.

Later when they decide to 5% or 10% then I do the same thing.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

David

I'm not clear on how expressing what you think helps. That seems to fall
into the category of "personal opinions".

Wouldn't describing what works and what doesn't, or the pluses and minuses
of a particular approach help someone evaluate the approach as it relates to
the spedific situation they're faced with?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"David W. Fenton" wrote in message
.93...
"Tony Toews [MVP]" wrote in
:

"David W. Fenton" wrote:

I think that's a bloody idiotic idea.

That's a bit harsh.

But it *is* what I think.

First off, you shouldn't be routinely changing table structures on
an app that's in pfoduction use.

I am always adding fields, tables, indexes and relationships in my
clients backends as I create new versions.

I don't. Anything like that is a major application revision and
requires careful plannning. Any app that has frequent updates to the
back end is one that was rushed into production without adequate
planning.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/