Migration of new database/phasing out old one

[Guest]

I've redesigned a database, making significant changes to the data structure.
The old database contains just one table; whereas the new one has 7 tables.
I basically created a new one from scratch, and will soon be importing the
data from the old one into the new one.

There are 5 other databases that tie into this one and the person in charge
of those databases will have to significantly change their queries to gain
access to the data in the new database. What is being requested of me, is to
keep the old database updated for a period of time, until the person can find
time to change their queries.

I am wondering how I should go about creating an update query. First, is it
possible to run an update query on another database? I know you can append
to another db, but that's not what I need. If it can't be done, I think I
could import the old table from the old database into the new database and
update it from there without affecting the gal's queries. That's not ideal
for me though because this database is going to be being moved soon to across
the country and I don't want there to be a lot of extra stuff in the
database.

Advice appreciated.
AA

 

[Jeff Boyce]

If there's a chance that you could "combine" the data from your 7 tables
into one that looked like the old single table, then do it ... in a query!
You can use a query to "flatten" well-normalized data for export or ...

And if you create this query inside your new database, it only takes up as
much room as the SQL statement, not as much as the table full of data. Your
query provides the old data structure, so to speak, and is as up-to-date as
your current data. The person using the old data base would only need to
see the query to see the old data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for you reply Jeff.

If could expand on your ideas, I would greatly appreciate it. I have three
questions for you:

You said I can use a query to flatten well normalized data for
export...hmmm..how do I do that?


If I create the query inside my new database, you mentioned that it won't
take up as much space as the table. Does this mean that I would need to
create an external link to the table? I know that having a link serves as
only a reference to the table and doesn't take up much space.

Also, I don't understand how I can go about creating an update query that
would keep the old db as current as the new one. Do you mean I could have a
stand alone query that would do this or are you saying that i would need to
program the query into my forms to keep it up to date? I would prefer to
not program the query into the forms because I will no longer be with the
company when it comes time to get rid of the old db once and for all. Having
just one query to delete is easier for the users to do rather than going into
the VBE to delete statements. KWIM? I was thinking I could once a day or
something run the update query to update the old db. If I could keep it
current with each individual transaction but not programmatically through the
forms, that would be so sweet.

Thanks for your help!
AA

 

[Jeff Boyce]

See responses in-line below...

Thanks for you reply Jeff.

If could expand on your ideas, I would greatly appreciate it. I have
three
questions for you:

You said I can use a query to flatten well normalized data for
export...hmmm..how do I do that?

Create a query, add the tables, join them, select (all) the fields you need
to "match" the old single table.

If I create the query inside my new database, you mentioned that it won't
take up as much space as the table. Does this mean that I would need to
create an external link to the table? I know that having a link serves as
only a reference to the table and doesn't take up much space.

A query isn't a table. If you create a query, the one user who needs to see
the old data can look at the query.

Also, I don't understand how I can go about creating an update query that
would keep the old db as current as the new one.

I was suggesting the query INSTEAD of doing the update. Why write to an old
table when you can run a query that displays all the current, up-to-date
info?

?Do you mean I could have a

stand alone query that would do this or are you saying that i would need
to
program the query into my forms to keep it up to date?

Queries can exist on their own, without connection to any forms.

I would prefer to
not program the query into the forms because I will no longer be with the
company when it comes time to get rid of the old db once and for all.

When you (the old user) no longer needs the old format data, stop using the
query. No need to delete anything...

Having
just one query to delete is easier for the users to do rather than going
into
the VBE to delete statements. KWIM? I was thinking I could once a day or
something run the update query to update the old db. If I could keep it
current with each individual transaction but not programmatically through
the
forms, that would be so sweet.

Thanks for your help!
AA

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff. I see what you are saying now. The "old user" as you call her,
told me that her queries would not work with a query off of the new database.
That was my first suggestion, but she shot it down. Honestly, I didn't
second guess her on this because a)I have no idea what she does in her
position & b) she pulls data from this database along with 4 other databases.


What you are saying though is that I can write a query and as long as I
name the fields the same as the old DB, it should work the same with the old
user's queries?

 

[Jeff Boyce]

There's no way I can promise that! You are there, I am not.

If the "old" user only needs to see the old (format) data, I can imagine a
query that generates that set of data and lets her look at/look up.

If the old user has to MANIPULATE the old (format) data, you have a much
bigger problem on your hands -- which copy of the data, new or old, will be
the master/"gold" copy?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

The new database that I've built will be the Gold copy. The old database is
unnormalized and clunky, has just one table in it actually, with no forms or
reports.

I will speak to her about it more in detail as to what her queries do
because it would be so much more efficient to just build a query off the new
one rather than an update query to the old one.

Thanks for your help again!
AA

 

[Jeff Boyce]

The only other piece I can imagine right now is that the old user will need
to re-point her old queries to the new data source, i.e., the new query
we've been discussing. She wouldn't need to (or be able to) keep them
pointed at the old database, because you wouldn't need (or want to) keep on
updating it when the (display-only) query can accomplish the same thing.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I verified with the user at a meeting today that her queries only read data,
not write to the old database.

So, if I write a query and just name the fields the same as the old one, it
should work ok as long as she repoints her queries to the new query?

Some fields in the old database have been split into two fields, for
instance Employee Name is just one field in the old database, whereas the new
database has a First Name & a Last Name field. That shouldn't be a problem
if I combine the fields under one name, like:

Select Employee.FirstName & " " & Employee.Last Name as User

with User being the name of the old database field. Am I understanding this
correctly? If so, this sounds so easy....

 

[Jeff Boyce]

Yes, it does sound easy. The hardest part may be getting the names (and
recombination) of fields right in your "master" query. (the old user will
need to have access to the new database, and will need to point her queries
at the new "master" query.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,
I created the query that imitates the old database, however, the old user
simply doesn't have the time to update her queries with the new links. It
doesn't make a whole lot of sense to me as the old queries take forever and
ever to run. But not my call, I guess. I linked the new DB to the old one
and have two update queries for the user to run (one if she adds batches &
one if she deletes them) that is run by date. At least she doesn't have to
manually enter them though.

The new database is only 10 MB compared to the old 95 MB db. Pretty sweet,
eh? This was my first complex database I've built and it's been a very
rewarding experience. It almost makes me sad that I won't be working with
Access or database development at my new job. I'll be a programmer with an
ETL team, focusing on SQL.

Thanks for your help. I appreciate it!
AA

 

[Jeff Boyce]

De nada! Thanks for posting back with your results.

Jeff