We have one .exe application that uses one .mdb Microsoft Access database.
我们有一个.exe应用程序，它使用一个.mdb Microsoft Access数据库。
I need to access data inside access file over Microsoft SQL Server. We have SQL Server 2008 R2 Enterprise that has linked server pointed to this Access file and I can run select / update query using SQL statement.
我需要通过Microsoft SQL Server访问文件中的数据。我们有SQL Server 2008 R2企业，它将服务器链接到这个访问文件，我可以使用SQL语句运行select / update查询。
SELECT * FROM [LinkedServerAccessDB]...[SomeTable]
How can I configure that this linked server, my Access database, is directly published as "Database" when some application tries to connect to my SQL Server using SQL Server instance name, and username and password. Which "database name" should I use to use directly linked server ?
当一些应用程序试图使用SQL server实例名、用户名和密码连接到我的SQL服务器时，我如何配置这个链接服务器——我的Access数据库——直接作为“数据库”发布。我应该使用哪个“数据库名”来直接连接服务器?
Thank you
谢谢你！
2 个解决方案
#1
3
It sounds like you want your MS Access Linked Server object available as a database (i.e. available in the 'Databases' folder in SSMS). This isn't possible, directly.
听起来您希望您的MS Access链接服务器对象作为数据库可用(即在SSMS的“database”文件夹中可用)。这是不可能的,直接。
Suggest you create a new SQL Server database that mimics the name of that Access database. Map a user to that login you've got above. Allow the user to run queries against the linked server.
建议您创建一个新的SQL Server数据库，该数据库模仿访问数据库的名称。将用户映射到上面的登录名。允许用户对链接的服务器运行查询。
#2
1
You can use CREATE SYNONYM like so.
您可以像这样使用CREATE SYNONYM。
USE ASQLServerDB
GO
CREATE SYNONYM Sometable FOR LinkedServerAccessDB...SomeTable
Once this is done you can write SELECT [...] from SomeTable as though it was a member of the database ASQLServerDB
完成后，您可以编写SELECT[…]。从某个表中获取，就好像它是数据库ASQLServerDB的成员一样
I was only able to get it to work at the object level so you'll need to do this for each object you want to expose. You could create an empty database that just contained these Synonyms if you wanted to get that "published as a database" feel.
我只能让它在对象级别上工作，所以你需要对你想要公开的每个对象都这样做。如果您希望获得“作为数据库发布”的感觉，您可以创建一个仅包含这些同义词的空数据库。
--This doesn't work
CREATE SYNONYM Sometable FOR LinkedServerAccessDB...
#1
3
It sounds like you want your MS Access Linked Server object available as a database (i.e. available in the 'Databases' folder in SSMS). This isn't possible, directly.
听起来您希望您的MS Access链接服务器对象作为数据库可用(即在SSMS的“database”文件夹中可用)。这是不可能的,直接。
Suggest you create a new SQL Server database that mimics the name of that Access database. Map a user to that login you've got above. Allow the user to run queries against the linked server.
建议您创建一个新的SQL Server数据库，该数据库模仿访问数据库的名称。将用户映射到上面的登录名。允许用户对链接的服务器运行查询。
#2
1
You can use CREATE SYNONYM like so.
您可以像这样使用CREATE SYNONYM。
USE ASQLServerDB
GO
CREATE SYNONYM Sometable FOR LinkedServerAccessDB...SomeTable
Once this is done you can write SELECT [...] from SomeTable as though it was a member of the database ASQLServerDB
完成后，您可以编写SELECT[…]。从某个表中获取，就好像它是数据库ASQLServerDB的成员一样
I was only able to get it to work at the object level so you'll need to do this for each object you want to expose. You could create an empty database that just contained these Synonyms if you wanted to get that "published as a database" feel.
我只能让它在对象级别上工作，所以你需要对你想要公开的每个对象都这样做。如果您希望获得“作为数据库发布”的感觉，您可以创建一个仅包含这些同义词的空数据库。
--This doesn't work
CREATE SYNONYM Sometable FOR LinkedServerAccessDB...