Hi,

Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.

I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.

So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection but I am relying on very wishful thinking. Any other suggestions are welcome, but as I mentioned ideally we want to keep the reporting queries in MS Access.

Many thanks for you help

R

Hello,

In SQL Server you can use a three-part qualifier [DbName].[Schema].[Table] to query across several database; so you can use

SELECT Col1, Col2, Col3
FROM DatabaseA.dbo.TableName
UNION ALL
SELECT Col1, Col2, Col3
FROM DatabaseB.dbo.TableName

Better use UNION ALL, because UNION Returns a distinct list of records.

Hi UpsideDown55,

are the databases hosted on the same server?
Then nothing should stop you to create some views with a union all of both tables.
If they are on different servers you could use a linked server to achieve the same result.

What you have to do will be a bit of diligence work to create all the views. Or you come up with a way to do this automatically by querying the system catalogs and generating your SQL Text. Afterwards you could copy paste the create view statements or use dynamic sql to execute them.

But keep in mind that this will only help your select statements. If you do updates, inserts or deletes then sql server most likely wouldn't be able to figure out from the view declaration which database it should use.

Cheers

Martin

Hi Olaf,

My difficulty is that I would need to do the UNION SELECT in Access, which is possible but previous experience shows it is slow. I am not sure if this is because Access does the UNION on the client side rather than on the server side or not. But your suggestion is my default position.

Thanks

Hi Martin,

Yes they are on the same server. My difficulty is most of our reporting goes through MS Access. As far as I am aware, you cannot reference SQL Server views in Access. Correct me if I am wrong.

Thanks 

R

Then create a view with this query and query the view from MS Acess, then SQL Server will perform the union