通过使用ADO从Excel VBA通​​过网络连接到SQL Server [英] Connecting to SQL Server over network using ADO from Excel VBA

问题描述

我需要从网络上的另一台计算机连接到笔记本电脑上运行的SQL Server数据库。我正在从C＃应用程序以及VBA宏中执行此操作。两者都在笔记本电脑上完美运行，C＃应用程序在网络上运行完美。但是我无法通过网络连接VBA。这是我的连接字符串：

I need to connect to a SQL Server DB running on my laptop from another computer on a network. I am doing this both from a C# app as well as in a VBA macro. Both run perfectly on my laptop and the C# app runs perfectly over the network. However I cannot connect using VBA over the network. This is my connection string:

ConnectionString =Driver = {SQL Server}; Server = MY-LAPTOP; DAtabase = SAFEXlive; UID = MyUsername ; PWD = MyPassword

除了'Driver = {SQL Server}'外，这与我正在使用的连接字符串相同C＃应用程序工作。

Aside from the 'Driver={SQL Server}' this is the same as the connection string I am using in the C# app which works.

然后我使用以下代码（参考VBE中的Microsoft ActiveX Data Objects 6.0库）打开连接：

I am then using the following code (With a reference to Microsoft ActiveX Data Objects 6.0 Library in VBE) to open the connection:

Dim oConnection As Connection
Set oConnection = New Connection
oConnection.ConnectionString = strConnectionString
oConnection.Open

如果我在笔记本电脑上运行，但如果我在网络上的另一台计算机上运行我收到错误：运行时错误-2147217843（80040e4d）[Microsoft] [ODBC服务器驱动程序] [SQL Server]登录失败的用户...，用户指定Windows登录计算机。

This works correctly if I run it on my laptop but if I run it on another computer on the network I get the error: "Run-time error '-2147217843 (80040e4d) [Microsoft][ODBC Server Driver][SQL Server]Login failed for user..." and the user it specifies it the windows log in for the computer.

是否有一些安全性设置我需要在代码或excel中设置？还是我错误地构建连接字符串？我做错了什么？

Are there some security settings I need to set in code or in excel? Or am I constructing the connection string incorrectly? What am I doing wrong?

推荐答案

已解决。答案相当愤怒。实际上连接字符串与UID有关。相信或不改变 ... UID = MyUsername; ... 到 .. UID = MyUsername; ... ，即删除空格，完全是这样！感谢您的建议。

Solved. The answer is rather infuriating. The problem is in fact with the connection string, with the UID. Believe it or not changing ...UID= MyUsername;... to ..UID=MyUsername;..., i.e. removing the space character, was all it took! Thanks for suggestions though.

这篇关于通过使用ADO从Excel VBA通​​过网络连接到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！