Import Data

[Lost in Microbiology]

I have databases from several sites all using the exact same database. I now
want to put them into one central database. If I start a new database and
import the data, Access automatically renames the tables Patient, Patient 1,
Patient 2, etc. for each of my sites. Is there a way to append them? I tried
doing an append query, but there are so many relationships, it was a little
beyond my abilities to get it to work. I don't even need to open the files, I
just want to take all 20 .mdb files (which all have the same tables and
relationships) and make 1 working database.

Any help would be greatly appreciated!

 

[John W. Vinson]

I have databases from several sites all using the exact same database. I now
want to put them into one central database. If I start a new database and
import the data, Access automatically renames the tables Patient, Patient 1,
Patient 2, etc. for each of my sites. Is there a way to append them? I tried
doing an append query, but there are so many relationships, it was a little
beyond my abilities to get it to work. I don't even need to open the files, I
just want to take all 20 .mdb files (which all have the same tables and
relationships) and make 1 working database.

Any help would be greatly appreciated!

Your only solution is to do the append queries, correctly, in a way that
reflects the relationships. If these tables have autonumber ID's and
relationships using them, this can become an absolute nightmare. It's doable
but it requires some expertise in construcing queries, a through knowledge of
the application, a good understanding of its relationships, and a lot of work.

There's no "magic wand" solution, I fear!

 

[Gina Whipp]

Lost in Microbiology,

Please note that this newsgroup offers FREE support and advice, a fact which
seems to be lost on Steve. IF you are considering getting professional
services check out their references.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John... Visio MVP]

Hello,

I have done this many times and John is correct in that it is a lot of
work. Since you say it is beyond your ability to get it to work, I would
like to offer to work with you to get the task done. I provide help with
Access, Excel and Word applications for a reasonable fee. If you would
like my help, contact me at (e-mail address removed) . I would be happy to give you
a quote to amass all your data into one database.

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified people in these newsgroups who gladly help
for free. Stevie is not one of them. He has proven many times in the
newsgroups that he does not know what he is talking about, but is willing to
seperate posters from their money with offers of questionable help at
unreasonable fees. There is nothing modest about his proposals.

If he was any good, his legions of satisfied customers would have him
drowning in work, but for some reason, they are staying away.

Stevie, I think it is time to get back on your medication and control your
delusions of being a resource. Where are your legions of satisfied
customers?

John... Visio MVP

 

[pietlinden]

I have databases from several sites all using the exact same database. I now
want to put them into one central database. If I start a new database and
import the data, Access automatically renames the tables Patient, Patient1,
Patient 2, etc. for each of my sites. Is there a way to append them? I tried
doing an append query, but there are so many relationships, it was a little
beyond my abilities to get it to work. I don't even need to open the files, I
just want to take all 20 .mdb files (which all have the same tables and
relationships) and make 1 working database.

Any help would be greatly appreciated!

One way to handle this is to write a series of append queries that
would import the data from the many databases, and then modify the SQL
to use the IN clause

here's an example from Developer's Handbook (yeah, it's cheating, but
it's *right*.)

SELECT OrderId, OrderDate, LastName AS Customer
FROM tblOrder INNER JOIN tblCustomer
ON tblOrder.CustomerID = tblCustomer.CustomerID
IN "G:\ADH\ch04\ch04.mdb";

Here's an example of an append query...
INSERT INTO lo_Patient ( PatientID, FirstName, LastName, ECOG,
Gender )
SELECT r_Patient.PatientID, r_Patient.FirstName, r_Patient.LastName,
r_Patient.ECOG, r_Patient.Gender
FROM r_Patient IN 'C:\Documents and Settings\Helen\My Documents
\db1.mdb';

It will work if the local and remote tables have the same name... I
just prepended "lo_" for local tables and "r_" for remote tables.

As John mentioned, though, fixing fields with AutoNumber *sequential*
record ID's is going to be a challenge. Because basically, you could
very easily import two parent records with the same record ID - (I
sure did!). So as John said, you could do it, but it may not be as
easy as you had hoped. If all your "source" databases have everything
set up with Random Autonumbers, then you can very likely pull this
off...

You would set the source database (from a field in a table in your
"destination" database), loop through a recordset based on that table,
and modify your Insert statements accordingly (so they'd be built in
code). Then you could execute them.

Without a bit more detailed information, though, it's hard to come up
with a solution that will definitely work. But it's not a trivial
task.

Pieter