从SQL Management Studio查询访问数据库，而无需使用链接服务器 [英] Query access database from SQL management studio without using linked servers

问题描述

如何在不使用链接服务器的情况下直接从SQL Management Studio查询MS Access数据库?

How do I query a MS Access database directly from SQL Management Studio, without using a linked server?

即.像

SELECT * FROM ["C:\ Data \ Accessdb.mdb"].[SomeTableInAccessDB]

SELECT * FROM ["C:\Data\Accessdb.mdb"].[SomeTableInAccessDB]

显然这是行不通的，但是是否可以在sql查询中指定访问数据库的详细信息呢?

Obviously this won't work but is there a away to specify the access database details within a sql query?

推荐答案

您可以使用OPENROWSET或OPENQUERY.例如(根据Microsoft的Northwind):

You can use OPENROWSET or OPENQUERY. For example (per Microsoft's Northwind):

 SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
             'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
             'admin';'',Customers)

添加链接服务器只是为了简化配置，因此不同的进程可以使用该连接而不必指定连接详细信息.我不认为链接服务器实际上会添加任何无法通过两个OPEN选项之一获得的功能.

Adding a linked server just allows ease of configuration, so different processes can use the connection without having to specify connection details. I don't believe a Linked Server actually adds any functionality that can't be obtained through one of the two OPEN options.

这篇关于从SQL Management Studio查询访问数据库，而无需使用链接服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！