SQL Server 2005 query multiple Access databases?

Is there a way to get SQL Server 2005 to query开发者_运维百科 3 or more separate Access databases (each returning 1 record) in one SQL statement?

Yes, but it will require you to create a Linked Server instance for each Access database. See here for details about creating Linked Server instances on SQL Server 2005 to MS Access.

Once you have those in place, you can query SQL Server and it will pass on the queries to the respective Access databases based on using the Linked Server instance notation when specifying tables in your SQL Server queries.

What you want is a Linked Server for each of the Access databases.

Just be aware that in SQL 2005 64-bit you won't be querying current versions of Access or Excel through linked servers any time soon.

Yes, set them up as linked servers using sp_addlinkedserver.