replace table without damaging the data after split

[Frank Situmorang]

Hello,

I have distributed my database to be used. I have split it to the FE and BE.
I found it later that I need additional field in the table and I want to
modify it.

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

Thanks in advance

 

[Golfinray]

If you want to add additional tables in a split database, you do that in the
BE. Simply open the BE (it should be listed as yourdatabasename_BE) and add
the table. Then go into the FE, use the linked table manager and create a
link to the table you created in the BE. The linked table manager is under
tools/database utilities.

 

[Klatuu]

You just need to get all users out of the application. You can't make any
design changes if someone has the file open.
To be sure, I usually open Access, navigate to and select the mdb file, then
in the low right coner drop down select open exclusive. If anyone has the
file open, you will get an error message.

Once in the database, open the table in design mode, make the changes you
need and save the table. Close the mdb file and you are done.

All this should be done after you first make a backup copy of the mdb.

 

[Jeff Boyce]

It isn't clear from your description whether you have distributed both a FE
AND a BE, or have merely distributed the FE to your users and they are all
sharing a single, common BE.

Also, why do you think the users will lose their data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

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.

One of my databases is 500 miles away and in use 24/7. I have updated it by
talking a reasonably competent IT person through the steps while on the
phone. I practice the steps first and write them down. I email them the steps
before starting so that we are both on the same sheet of music.

 

[Frank Situmorang]

Thanks to all of you:

I already distributed both FE and BE to different users which are church
secreatries of my denomination. They are naive in Access program. Since I
have their database design, both for FE adn BE, what I want to do if it is
possbile to have the design of the BE and FE and then ask them to save it in
the directory that I have instructed them to do. For the FE they can replace
the old FE and use link manageer to BE, I thingk it woyuld be fine, But for
the BE if they want to replace it it will delete also their member's data
So I appreciate if any ide to resolve this problem.

Thanks in advance

 

[John W. Vinson]

I already distributed both FE and BE to different users which are church
secreatries of my denomination. They are naive in Access program. Since I
have their database design, both for FE adn BE, what I want to do if it is
possbile to have the design of the BE and FE and then ask them to save it in
the directory that I have instructed them to do. For the FE they can replace
the old FE and use link manageer to BE, I thingk it woyuld be fine, But for
the BE if they want to replace it it will delete also their member's data
So I appreciate if any ide to resolve this problem.

You should probably create a "DDL" query (Data Definition Language) such as

ALTER TABLE tablename IN "C:\path\backenddatabase" ADD FIELD fieldname...

See the VBA (oddly enough, since it's not VBA) help for ALTER for the exact
syntax, this is from memory and may be faulty.

Send them a frontend with the query and have them execute it, when they're
alone in the database.

 

[David W. Fenton]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

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]

You should probably create a "DDL" query (Data Definition
Language) such as

ALTER TABLE tablename IN "C:\path\backenddatabase" ADD FIELD
fieldname...

That assumes that all the back ends have the same name and are in
the same location.

See the VBA (oddly enough, since it's not VBA) help for ALTER for
the exact syntax, this is from memory and may be faulty.

Send them a frontend with the query and have them execute it, when
they're alone in the database.

I think I'd tend to put this in the front end, and create a macro
that executes the code. This would allow the code to dynamically
find the back end according to where it was actually stored.

 

[Jerry Whittle]

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

 

[David W. Fenton]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

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.

 

[Jerry Whittle]

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.

 

[Tony Toews [MVP]]

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/CompareEM-LITE/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/

 

[Tony Toews [MVP]]

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/

 

[David W. Fenton]

That's a bit harsh.

But it *is* what I think.

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.

 

[Tony Toews [MVP]]

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/

 

[Jeff Boyce]

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