Migrate updates from developement to production DB

[DocBrown]

Hi, I've looked through this group but haven't recognized a solution to my
situation.

In the database I'm working on, originally, I developed the DB and when I
had tested it in my dev env, I created a production copy by exporting all the
tables, forms, etc to a new copy minus the test data.

Now, I am adding some fields to the primary table. I made and tested my
changes in my development copy but I'd like to migrate these change to the
production database.

There are linked tables where a number field in the primary table has a one
to many relationship to a number field in a related table. There are also
other fields where a number field is related to another table that is
essentially a lookup table where the second field is a text field. I do this
so the user can expand the lookup table at will by entering new text records
in the lookup table.

What is the most effecient way to migrate my table, form and report changes
from my development database to the production DB? I've seen description
where I export the updated table to the production DB, and execute an append
query from the old table to the new. I've also manually exported the forms,
reports etc.

Is there a more automated way to update my production DB? If there is a
better way to maintain a development and production copy of a DB, I'm all
ears.


Thanks in advance for any advice.
John

 

[Albert D. Kallal]

First and foremost the only practical way to manage this problem is to run
to a split database. I speak of this here :

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

The above means that for bug fixes, new forms, new reports etc., you'll have
little trouble updating your end users application part, because the
application part does not have the data anymore.

All that is required here is that you provide them with a new copy of the
program part, or what is often referred to here in these newsgroups as the
new front end.

Now, I am adding some fields to the primary table. I made and tested my
changes in my development copy but I'd like to migrate these change to the
production database.

The only solution to the above is to make notes of the changes you make
(and, you Make these notes at the same time when you make the change to the
table).

I often open up notepad, put in a list of changes I've made in the
development
version, and then to help me remember what I must do to the production
version database, I simply go back to my notes

eg:

ResOptions
add pk field id (autonumber)

tblLocations:
Add field Acctive (yes/no, default = yes)

etc. etc.

In the above you can see that I added ID field to table Resoptions, and I
also added a active flag field to tblLocations

While I might have spent several days doing a lot of serious development and
application changes , I find the actual list of table changes only takes a
matter of minutes to do if you have a nice reference list like the above.

Of course the problem is if you're developing off site, and don't have
access to the actual physical data file, then this task becomes a little bit
more tricky. In this kind of scenario I simply have the person zip up, and
send the data file to me, and tell them not to work until I've made the
changes and send it back to them.

Even more deal than the above is to actually write code that adds the new
fields and tables to the back end database, however this code can be a bit
tricky to write. This is the current solution I use now, and therefore to
add a new field to a table, I don't go into the table and add it by hand, I
write that code to add the new field.

Some of my applications that are few years old up schley has several hundred
lines of code that represents additions and updates to the tables/fields.

Llast, but not least Tony has a nice auto front end updater that works
really well if you're in a network environment. that to use that approach,
then every time you make updates and are satisfied with your changes, you
simply copy the new updated version to the network file share location, and
all users will instantly get this new version next time they run your
application.

http://www.granite.ab.ca/access/autofe.htm

 

[Tony Toews [MVP]]

Even more deal than the above is to actually write code that adds the new
fields and tables to the back end database, however this code can be a bit
tricky to write. This is the current solution I use now, and therefore to
add a new field to a table, I don't go into the table and add it by hand, I
write that code to add the new field.

Some of my applications that are few years old up schley has several hundred
lines of code that represents additions and updates to the tables/fields.

I'm well past a few hundred lines now. Lately I've been using the
following tool. It's a decent tool but certainly has a few minor
limitations.

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

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/