How to bring 3 external Access tables together with a SQL Union/Select Into (1 Viewer)

[darbid]

I am not sure where to start or what to search/Google for here.

What I am trying to code (from an Access application) is to be able to allow a user to choose 3 different MDBs which will each contain 1 table.

With the three tables I then want to perform an INSERT into a table in the existing application. (This is actually a linked table to an SQL server so I could also insert it with ADO directly into the server).

I am not looking for spoon feeding but more some hints on what I need to do. I am ok with giving a user a dialog to choose the 3 mdb files, it is more what to do with the 3 database objects that I will have.

 

[spikepl]

Isnt't it just like linking to a table in a back end, just on-the-fly? And if not, how does it differ?

 

[darbid]

Hi spikepl,

Yes I think but I am lost with dealing with multiple external DBs.

My research has just found this very old suggestion - is this what I should be looking at in your opinion http://support.microsoft.com/kb/113701

 

[darbid]

If I follow the concept of the above link - I would need to do something like this -

I don't want to complicate the issue with a complicated SQL but it kinda is.......so assuming this SQL works if all tables are in one MDB

SELECT * INTO patent_questionnaire_table
FROM [SELECT IIf(IsNull(connect.srf_akz), em.akz,connect.srf_akz) as patent_akz, type, decision_number, decision_date, bkz_id, em.em_akten_id, em.total_eval,  connect.srf_akten_id
FROM (em
LEFT JOIN connect
ON em.em_akten_id = connect.em_akten_id)
UNION select akz as patent_akz, type, decision_number, decision_date,bkz_id,  null, total_eval, srf_akten_id
from srf]. AS [%$##@_Alias];

Then assuming
patent_questionnaire_table = SQL Server Table
em = [accessDB1].table1
connect = [accessDB2].table2
srf = [accessDB3].table3

SELECT * INTO [sql server table].tablename
FROM [SELECT IIf(IsNull(table2.srf_akz), table1.akz, table2.srf_akz) as patent_akz, table1.type, table1.decision_number, table1.decision_date, table1.bkz_id, table1.em_akten_id, table1.total_eval,  table2.srf_akten_id
FROM ([accessDB1].table1
LEFT JOIN [accessDB2].table2
ON table1.em_akten_id = table2.em_akten_id)
UNION select table3.akz as patent_akz, table3.type, table3.decision_number, table3.decision_date, table3.bkz_id,  null, table3.total_eval, table3.srf_akten_id
from [accessDB3].table3]. AS [%$##@_Alias];

 

[spikepl]

I am not quite sure about your specific needs. There seems to be two different concepts on offer:

1. Link to the tables in the external db's , and operate on them "as per usual" in a front end - each table can be linked to a different, defined on-the-fly, "back end" http://msaccesstips.com/2008/09/link-external-tables-with-vba/
2. Run SQL on external tables, as per your article in the link, with the limitations mentioned

 

[darbid]

I am not quite sure about your specific needs. There seems to be two different concepts on offer:

Yep there does, but linking is not an option or I think wanted. Data is being given to me/a user in the form of 3 tables contained in 3 different access 2003 mdb's. (This form is annoying but I have to live with it).

Now my task is to bring these together into one table and get them into my SQL server backend. I personally could do it manually but I am not always going to be here so I want to automate it. The user just needs to point to the right mdbs and away it goes.


I have set up the table in the back end and am now working our how to automate the front end.

I am just looking at 2 more concepts "Opendatasource" and "Openrowset"

 

[spikepl]

Okaay...although I still do not quite follow. When you can link to several external tables from VBA, as a bit further down in the link I posted, then you'd do away with the need for specifying connections in the SQL statements themselves, and the limitations posed. But again, the best approach is difficult to judge for an outsider.

 

[darbid]

Sorry buddy either I do not understand you or we are on 2 totally different wavelinks.

I am writing code with myMDB. myMDB is a front end which has an SQL Server backend.

Locally on the same PC as the myMDB front end there will be mdb1, mdb2, mdb3.

myMDB will use all 3 mdbs to make a Select SQL statement to which will insert into SQL Server.

mdb1, mdb2 and MDB3 will then be deleted.

 

[darbid]

here is an example maybe http://www.access-programmers.co.uk/forums/showthread.php?t=160146&highlight=OPENROWSET

 

[spikepl]

Ah ok, I am not familiar with myMDB at all.

 

[darbid]

Ah ok, I am not familiar with myMDB at all.

myMDB is just a name - ie. my MS Access front end.

 

[darbid]

Ok I more or less understand Openrowset which would allow me to use ADO to do this. This option is out because Ad HOC Access is not set up on the server and I am not the admin.

 

[darbid]

spikepl i should have taken your advice and saved some time.

I have now choosen to link the tables - do what i want and then dropping the links.

Thank you for your help.

 

[spikepl]

You are welcome ... and I deny any responsibility, if asked