Upgrading a DB

[BD]

What's the better way to upgrade a database? In case of several back
end DB's I want to compare the version saved in the DB's and if the
version is smaller I'll perform a upgrade. I don't want to use ADD
COLUMN with DDL. My question is if that is better yo use DAO with a
general structure of the DB's or make a copy of the tables, delete the
tables and recreate them with DDL CREATE TABLE...

[]'s
BD

 

[Nick Coe \(UK\)]

I can't think of any particular risk/benefits one way or the
other except personal preference and context - that is which
method do you feel most comfortable with and easy to program
using and how complex are the changes?

I've used various means to upgrade BE's usually depending
upon how complex the changes are going to be. With a lot of
changes - for example a couple of hundred tables with some
column changes/additions plus index changes then it's worth
considering creating a whole new back end then setting up
some means to transfer the data across using either code to
export then import (to a neutral format - ASCII text
perhaps) or just import using linked tables or a whole load
of queries which you later delete or some permutation of all
that.

It's really whatever suits your particular circumstances
best.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp is now Free
http://www.mrcomputersltd.com/ Repairs Upgrades

In BD typed:

 

[BD]

Well, despite I think that using DAO is more easy it imposes an
existent of a DB structure to compare.
The changes are based on tables added, column added and deleted but
never changed. I will need to turn off the RI and recreate them to
prevent errors.
Where can I find some real examples of DAO and DDL that would help me
a lot in the development DB upgrades?

[]'s
BD

 

[Nick Coe \(UK\)]

My favourite reference is the Access Developers Handbook
which I supplement with and old copy of SQL by Hursch and
Hursch (may not be available any more). Anything by John
Viescas will be good.

www.developershandbook.com

I have thought of something you could experiment with:-

Use vba to scan through the table/field design definitions
and store them in a new table which could become your Data
Dictionary. You would need to also store index info and
somehow document RI's. You could then use that table to
generate SQL DDL statements using vba and DAO. Could be a
long term project though....


--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp is now Free
http://www.mrcomputersltd.com/ Repairs Upgrades

In BD typed:

 

[BD]

Thanks for the link.
I really found the idea very interesting to store in a table the
tables/fields, indexes and RI's definitions. I will go to explore more
that suggestion.

[]'s
BD