How To Transfer Record(s) To Another Database

Hi,

I work in a hospital where each ward has its own identical but UNLINKED staff database. I wanted to make a single database but the management was adamant that for confidentiality they should each be standalone. I lost this argument!

Data is stored in 7 tables: Identity, annual leave, sick leave, qualifications, etc.

Too late, someone has realised that staff may transfer from one ward to another, so the whole record needs to be transferred. What's the best way to do this?

So far the process I have come up with is:
Choose the person you wish to export from the source database
Run 7 queries to export the records from the various tables e.g. to Excel
Create linked tables in the target database to pick up these records
Run import queries.

I set up a macro to run the 7 export queries, using the "OutputTo" action and specifying .xls for the output format. But the files created don't appear to be readable by Access. In Explorer, they only have the Windows icon and are just "files".

Does anyone know a better way to tackle this problem?

Thanks in advance for any assistance.

Pat.