Upgrading BE Tables

R

Rose B

I have just been (seriously) enhancing an Access application which is split.
I have no problems with FE changes, or with linking to new tables. but is
there any way to import design changes for existing tables to the
'operational' BE or do I have to re-enter the changes? (The application is
very active so I need to keep the data come implementation of the new
version).

Thanks,
 
J

John W. Vinson

I have just been (seriously) enhancing an Access application which is split.
I have no problems with FE changes, or with linking to new tables. but is
there any way to import design changes for existing tables to the
'operational' BE or do I have to re-enter the changes? (The application is
very active so I need to keep the data come implementation of the new
version).

Thanks,
Click to expand...

The design of the backend tables must be changed in the backend. That's where
the tables ARE and they can only be changed there!

Where would you be "importing" design changes from? Some third database, or
from local frontend copies of the backend tables, or what?

John W. Vinson [MVP]
 
T

Tony Toews [MVP]

Rose B said:
I have just been (seriously) enhancing an Access application which is split.
I have no problems with FE changes, or with linking to new tables. but is
there any way to import design changes for existing tables to the
'operational' BE or do I have to re-enter the changes? (The application is
very active so I need to keep the data come implementation of the new
version).
Click to expand...

If you have only the two backends, testing and production then you can
manually keep track of what changes you've done. However my standard
blurb on this topic follows.

You have two basic methods of doing version upgrades to the backend
MDB.

1) Ship a new empty MDB and copy the tables across in relational
sequence, ie parent records, then child tables and so forth down and
across the "relational tree:" This can be done by creating a table
containing the table names with a relational sequence field and
running down the tables doing append queries.

Where appropriate you will need to copy specific fields to other
tables to split data out given new tables.

This is a rather ugly solution.

2) Create a bunch of code within your new version which creates the
new tables, relationships, indexes and new fields on existing tables.

Updating an Access Backend MDBs structure using VBA code
http://www.granite.ab.ca/access/backendupdate.htm

The Compare'Em utility has made this process much easier.
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm

In both cases I would suggest keeping a version number of the FE and
BE in a table or property thus helping you to figure out when to run
the code or not allow a new FE to be run against an old format BE.

Also make a backup before doing any such updates, double check the
number of records in the new tables after the conversion and include a
mechanism to ensure users aren't in the backend when all this starts
or during the process.

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/
 
R

Rose B

Thanks - will explore the 2nd option. I have been keeping a track of what I
have been doing in terms of field additions/deletions etc.
 
R

Rose B

Sounds great - but the URL gave the following error... can you re-post with
an amended one?

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32
 
T

Tony Toews [MVP]

Peter Hibbs said:
You can use the code at :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'
which will do it automatically.
Click to expand...

Yes, from what I read that tool will do the changes to the BE
automatically. But it won't figure out the changes in the first
place. Which is why I like the Compare'Em utility.

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/
 
P

Peter Hibbs

That is true, the developer's version and the client's version of both
BE and FE files need to be identical before the BE Update code can be
implemented.

Peter Hibbs.
 
Top