Excel VBA到SQL Server 2016连接字符串错误 [英] Excel VBA to SQL Server 2016 Connection String Error

问题描述

我们正在尝试通过Excel中的ADODB插件对本地服务器数据库进行VBA访问。但是，无论我们尝试哪种连接字符串配置，我们都会遇到相同的错误消息。

We´re trying to get VBA access to our native server database with the plugin ADODB in Excel. However, we´re running into the same error message no matter what connection string configuration we´re trying.

任何帮助将不胜感激！参见下面的图像和代码。

Any help would be appreciated! See below image and code.

Sub test()

    Set Conn = New ADODB.Connection
    ConnString = "Provider=SQLOLEDB;Data Source=databaseb006;Database=EIU"
    Conn.Open

End Sub

推荐答案

请考虑首先检查 SQLOLEDB 是否已从以下 @ josepmv的博客页面，也可以在他的 Gitub回购。

Consider first checking if SQLOLEDB is among installed OLEDB providers with below PowerShell script, borrowed from this @josepmv's blog page, also available on his Gitub repo.

PowerShell （在x86（32位）和非x86（64位）的Powershell ISE中运行.ps1脚本）

function Get-OledbRegistered
{
    [CmdletBinding()]
    [OutputType([System.Collections.Generic.List[PSObject]])]
    param ()

    Process
    {
        $list = New-Object ([System.Collections.Generic.List[PSObject]])

        foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator())
        {
            $v = New-Object PSObject        
            for ($i = 0; $i -lt $provider.FieldCount; $i++) 
            {
                Add-Member -in $v NoteProperty $provider.GetName($i) $provider.GetValue($i)
            }
            $list.Add($v)
        }
        return $list
    }
}

$list = Get-OledbRegistered
$list | ?{ $_.SOURCES_NAME.IndexOf('SQL') -ge 0 }

上面的脚本搜索包含 SQL 的任何源名称。只需在最后运行 list 即可显示所有OLEDB提供商。此外，可用的提供程序必须匹配您的MS Office的位版本。例如，MS Office 32位版本可能无法运行OLEDB 64位提供程序，反之亦然。同样，您的SQL Server位版本必须也与提供程序对齐。

Script above searches for any source name containing SQL. Simply, run list at end to show all OLEDB providers. Also, the available provider must match the bit-version of your MS Office. For example, MS Office 32-bit version may not be able to run OLEDB 64-bit providers and vice versa. Similarly your SQL Server bit-version must align as well to the provider.

此外，请注意，您可以下载最新的OLEDB驱动程序，它会发出一条重要消息（添加了重点）：

Also, do note you can download the latest OLEDB driver which raises an important message (emphasis added):

以前用于SQL Server的Microsoft OLE DB提供程序（SQLOLEDB）和SQL Server本机客户端OLE DB提供程序（SQLNCLI）仍被弃用，不建议使用 >要么用于新开发工作。

...

新的OLE DB提供程序称为SQL Server的Microsoft OLE DB驱动程序（MSOLEDBSQL）。新的提供者将使用最新的服务器功能进行更新。

The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work.
...
The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). The new provider will be updated with the most recent server features going forward.

---

来自在那里，根据计算机的可用性根据需要调整连接字符串。以下是未经测试的典型示例。

---

From there, adjust connection strings as needed according to availability on machine. Below are typical, untested examples for illustration.

VBA

Sub test()

    Set Conn = New ADODB.Connection
    ConnString = "Provider=MSOLEDBSQL;Server=myServer\myInstance;Database=myDatabase;Trusted_Connection=yes;"
    Conn.Open

    ConnString = "Provider=SQLOLEDB;Data Source=myServer\myInstance;Initial Catalog=myDatabase;Integrated Security=SSPI;"
    Conn.Open

    ConnString = "Provider=SQLNCLI;Server=myServer\myInstance;Database=myDatabase;UID=myUser;PWD=mypwd"
    Conn.Open

End Sub

这篇关于Excel VBA到SQL Server 2016连接字符串错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！