transferring data

[Max]

A little background: as you may know, I am attempting to "fix" my employer's
existing database. We are a non-profit (i.e., NO budget for someone to help
me with this) youth group. The bosses are talking about doing away totally
with Access; meanwhile, I've spent a year working with this and learning what
I can about it, and am convinced that it can work if it can be made more
efficient (even normalized). Before I came to this job, I'd never even heard
of relational databases, indeed, the only computer software I'd used was word
processing.

On to my question: now that I have my new database started (I've gotten the
first two tables designed and the first form designed), is there an easy way
of transferring the data from the old database into the new database? Or do
I actually have to print out the old data and type in the records one by one?
(I hope not.)

Please realize I do not know any programming code, and I'm attempting to do
all this within the wizards functions of Access. I understand the "concept"
of code, but I do not know what to do with the information contained therein.

Thanks for your understanding reply, in advance.

In peace,

Max

 

[Ken Snell [MVP]]

Is the old database also an ACCESS database? If yes, you can link to the
tables in that database and use append queries to copy the data into the new
database's tables.

 

[Tim Ferguson]

On to my question: now that I have my new database started (I've
gotten the first two tables designed and the first form designed), is
there an easy way of transferring the data from the old database into
the new database? Or do I actually have to print out the old data and
type in the records one by one?
(I hope not.)

Data migration is a huge subject -- in the big bad industrial world, it's
a career choice all on its own!

The stages (roughly) are:

analyse the old data: are they accurate and complete enought to make
the rest of the procedure worth it? Are there loads of duplicated
records? Are there missed duplications (eg. "Saint Mary Street", "St
Mary's St.", "St Mary Street", etc) that will have to be cleaned by hand?
If the old data are highly un-normal, then are there loads of
inconsistencies?

define a data dictionary: for example to get rid of the missed dupes

clean the old data: deduplication, correcting spelling misteaks, force
everything into the data dictionary style. Much of this has to be done by
hand, with lots of queries to find the errors.

design the migration: these are update and append queries to copy the
old records into the new system.

testing

cross fingers and go live!


It sounds a bit intimidating, but with a clear head and a logical
approach need not be too awful! It is not money, or even technical
expertise, that limits it as much as time -- particularly if all the hand
cleaning falls on you! Get help from the users, who will know the data
pretty well already but who may need convincing to spell Road as Rd. with
a full stop.

All the best


Tim F

 

[Max]

Yes, they are both Access databases, but I have absolutely no idea of how to
link to the old d/b's tables nor how to create or use append queries. The
only queries I've ever made were simple queries. I've noticed the other
options on the menu, but don't know how to use them.

Is there an existing article that explains, to virtual illiterates like
myself, what these two concepts are and how to execute them?

Thanks again.

 

[Max]

Sad to report, I am the primary user. Yes, there are many inconsistencies
like you mentioned. One of my main tasks, in fact, is the visual scanning
and correcting of these. I have no source to tell me which is better, to use
Road or Rd., for example, so I just decided mostly on which variation appears
the most often, then just kept that as the norm. Another example, someone
before me must have added the phone number mask (###)###-####, and there are
many records where the phone number is in the old non-masked form.

In fact, one of the things I have read on i-net sites, is that the d/b
should be split. Today, I came across an article the gives 3 reasons why; I
realized that the first 2 reasons disprove my need to split our d/b. (We do
not have multiple users, nor developers, I'm the sole user, and I'm the only
one learning to develop it. I suppose in the long run, if we do keep Access
(not Excel or some other system), someone might come in who knows it and
would be interested in altering it, there's always a chance, but for now the
only other person who utilizes any information from it, only knows Excel.
Whenever I give her any reports, they are converted to an Excel format.

Thanks, Tim.

 

[Ken Snell [MVP]]

Linking is easy. Go to File | Get External Data | Import. A wizard will
allow you to navigate to the old database and then you can select the tables
that you want to link.

Append queries are easy to set up. Open a new query in design view, select
the source data table and put it on the grid. Close the "Add tables" window.
Go to Query icon on toolbar and select Append; wizard will ask you to select
the destination table. Pull onto the grid the fields from the source table
that you want to get the data from and append to the other table. Then in
"Append To:" cells, select the field in the "append" table to where that
field's data are to go.

Cleaning up the data is a whole other issue... and can be an art in and of
itself.

--

Ken Snell
<MS ACCESS MVP>

 

[Jeff Boyce]

Max

This may burst your bubble... your bosses have reasons why they are talking
about doing away with Access. No matter what else you do, if you don't
address those concerns, you aren't going to get their buy-in.

WIIFT (what's in it for them?) Unless you can address their concerns and
show how your approach will solve one/more problems they've identified,
you'll probably chalk up the last year to "experience".

Best of luck!

Jeff Boyce
<Access MVP>

 

[Matt Fragale via AccessMonster.com]

You are probably right. Access can probably do what you need it to do.
What you really need is some knowledge to back that up and allow you to
speak to it with the aforementioned bosses.

While I think that having read a few of the responses here for other
people, this is overall, a helpful group and they will do their best to
impart what they know to you, picking up small bits of information as you
run into problems may ultimately lead you down the path of doom. Get
yourself a good book on the subject and cloister yourself away for a couple
of days and read and do some stuff.

Generally, I suggest to people the Sybex Access books written by Litwin and
Getz (because they have a sense of humor and you might not fall asleep
reading them LOL) but there are plenty of good ones out there.

But get some basic knowledge under your belt and keep coming back here for
validation when necessary. Keep at it and you'll get it!!

Best of luck to you with your project!

 

[Tim Ferguson]

In fact, one of the things I have read on i-net sites, is that the d/b
should be split. Today, I came across an article the gives 3 reasons
why; I realized that the first 2 reasons disprove my need to split our
d/b. (We do not have multiple users, nor developers, I'm the sole
user, and I'm the only one learning to develop it.

I don't know the list you are referring to, but I can guess what the
reasons are -- in fact there are many more than three good reasons for
always splitting a db, even in a single-user, single-developer setup.

Wait until you have spent ages working on a form and then realised it's a
dead end (yes, that happens to all of us!); and then spot the fact that
you can't go back to a working backup because the data (that you have
been updating all the while) are out of date in that one. Moreover, the
simple convenience of having different front ends (for data entry, for
reporting, for mailmerges and label printing, etc) makes an invincible
argument on its own. Not to mention the ease with which you can swap
between testing and live back ends -- you don't program and test against
live data do you?

Splitting is _strongly recommended_ if you are at all serious about
moving out of the "toy database" brigade.

All the best


Tim F