CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlms-access
user261338
user261338

Reputation: 41

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?

Upvotes: 4

Views: 574

Answers (4)

ErikE
ErikE

Reputation: 50191

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.

Upvotes: 1

David M
David M

Reputation: 72840

Yes, set them up as linked servers using sp_addlinkedserver.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180787

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332491

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.

Upvotes: 6

Related Questions