Union Query to select same tables from different databases?

[AR Ratheesh]

Hi,
I am not really work with union querys.Please help me to solve this issue.

I have 4 MDBs. and all MDBs contains "Table A"(Same table in every MDB)

I want to retrieve a union select query to get all data from all MDBs to one subform.Is it possible?

All MDBs are located in same directory say "C:\My Work\DB\"

Thanks in Advance
AR

[nico5038]

Just create a linked table for each [Table A] in the four mdb's.
Change the name to easily see from which mdb the come (like [TableA_A_mdb],[TableA_B_mdb], etc.)

Now create a new query and close the table selection popup to see the [SQL] selectiojn button top left.
Then type the SQL text like:

select * from [TableA_A_mdb]
UNION ALL
SELECT * from [TableA_B_mdb]
UNION ALL

select * from [TableA_C_mdb]
UNION ALL
SELECT * from [TableA_D_mdb]

That's all. This will make sure all rows are dispayed. Using just "UNION" without the "ALL" will suppress duplicates.

Now create the subform based on this query. Keep in mind that the results can't be updated, it will be "read only" !

Success !

Nic;o)

[AR Ratheesh]

Hi Nico,

Thanks for your support, i really plan to use this option for overcome the MDB size (2GB).and multiply the size to 8GB(4MDB X 2GB) or above.I dont know whether is it possible? anyway thanx..

AR Ratheesh.

[NeoPa]

If that's your limitation you're probably better advised to use a version of SQL Server for the back-end instead. Free versions are available and they are very powerful compared to even a single Access database, let alone mucking about with multiple databases.

[nico5038]

I support NeoPa's view of using another type of database (MS SQL, MySQL, Oracle, etc.), as Access will become very slow and I'm not sure or the 2Gb max also applies to the needed temporary space for execution of the query...

Nic;o)

[AR Ratheesh]

My clients doesn't need much more data.but i still consider a safety view on this. mostly are in single user apps.I heard about the max limit of access, so I just put a provision on it.

Thanks Neopa and Nico for your support.
AR Ratheesh

[NeoPa]

Nico's suggestion in post #2 will give you that then. As long as you understand the limitations related to that approach you're in a good position to choose how to handle things if ever such a situation arises for real.