VBA连接到SQL Server Express数据库问题

I installed a copy of SQL Server Express (latest). I am having trouble connecting to it.

我安装了一份SQL Server Express（最新版）。我无法连接到它。

Since I am the admin on this computer, it should be not needing user name password, because I created the database, and everything should fall under Windows authentication. My code is below.

由于我是这台计算机上的管理员，因此我不需要用户名密码，因为我创建了数据库，所有内容都应该归入Windows身份验证。我的代码如下。

I get an error:

我收到一个错误：

Run-time error '-2147217843 (80040e4d)':
Invalid authorization specification

运行时错误'-2147217843（80040e4d）'：无效的授权规范

This error happens on attempting to open the connection.

尝试打开连接时发生此错误。

When I launch a subform from main form, this code runs in initialize:

当我从主窗体启动子窗体时，此代码在初始化中运行：

Option Explicit
Private objMyConn As ADODB.Connection
Private objMyRecordset As ADODB.Recordset

Private Sub UserForm_Initialize()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Contact;"
        objMyConn.Open

        strSQL = "Select * from Contact where Lastname like " + Chr(39) + "%" + LastSearch + Chr(39) + " And Firstname like " + Chr(39) + "%" + FirstSearch + Chr(39)
        MsgBox strSQL

End Sub

2 个解决方案

#1

Add Integrated Security=SSPI to your connection string to indicate a trusted connection is desired:

将Integrated Security = SSPI添加到您的连接字符串以指示需要可信连接：

objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Contact;Integrated Security=SSPI"

EDIT

编辑

SQL Server Express installs a named instance by default so you may need to change the data source to include the instance name:

SQL Server Express默认安装命名实例，因此您可能需要更改数据源以包含实例名称：

objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=Contact;Integrated Security=SSPI"

#2

use something like this instead of localhost

使用类似这样的东西而不是localhost

HP8300-0792\SQLEXPRESS

where HP8300-0792 is the name of your computer.

其中HP8300-0792是您的计算机的名称。

#1