Linking a master table to many external tables? 2

[DAmoss]

I have a 'Master' Access database that I wish to populate automatically with all the records from 5 other similar databases. The table structures are exactly the same in all cases. They are all kept in the same folder, but each database is named differently, they have all been created using Access 2003.

Ideally I would love to have a selectable database list on the screen and then be able to tick which ones to pull into the master table! but maybe I'm jumping the gun here.

Can someone give me some clues on how I can pull in these other external tables into the master table. I know I have to loop through them one by one and import the data accordingly, but any help or clues would be greatfully accepted.

Thanx.

 

[Shusha52]

Hi there,

I tried everything on earth and still wont work. I reduced the number of fiels just for test purposes. if 5 fields can be successfully inserted then all can was what i was going with . I created identical tables with the 5 fields and then tried this code:

INSERT INTO EMPTYPAT (Masterno, ProgDesc, ClientId, Famid, Fullname, DirectFundedYN)

SELECT PAT.Masterno, PAT.ProgDesc, PAT.ClientId, PAT.Famid, PAT.Fullname, PAT.DirectFundedYN FROM PAT IN 'F:\TDY\RCMS_HW.MDB'

UNION SELECT PAT.Masterno, PAT.ProgDesc, PAT.ClientId, PAT.Famid, PAT.Fullname, PAT.DirectFundedYN FROM PAT IN 'F:\TDY\RCMS_NP.MDB'

UNION SELECT PAT.Masterno, PAT.ProgDesc, PAT.ClientId, PAT.Famid, PAT.Fullname, PAT.DirectFundedYN FROM PAT IN 'F:\TDY\RCMS_BR.MDB';

and still it keeps saying "syntax error in from Clause".

I give up.. pls let me know if there is something i am doing wrong. My table EMPTYPat has no data in it.

Works finne if it is a union query. it is when i use the Insert too it goes nuts.

Pls point me in the direction os sanity.. thks
Usha

 

[PHV]

Works finne if it is a union query. it is when i use the Insert too it goes nuts
So, save the union query and then:
INSERT INTO EMPTYPAT (Masterno, ProgDesc, ClientId, Famid, Fullname, DirectFundedYN)
SELECT * FROM yourSavedUnionQuery

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

I have the feeling that a union query might slow the records inserting process. Might I suggest to use 4 seperate INSERT INTO queries. You could use the QBE to help you out for the first one and then play with it to build the rest of them and benefit yourself from that.

 

[Shusha52]

Hello all you helpful gals/guys,

Thks for all your input. I got it working. What i did was this as suggested
1. built my union query with the tables from the 4 sites. Sabed it. Ran it to check all records were picked up.
2. Next used the saved Union query as source and appended it to a blank file with same structure as the 4 sites table.
Worked perfectly.
Leslie and PHV thanks a million for your invaluable input qnd guidance. Much appreciated
USha