从 Excel 2010 到 SQL Server 2008 的 VBA 连接

VBA Connection from Excel 2010 to SQL Server 2008

提问by DeliaOD

i'm pretty new to SQL and VBA, so please forgive any audacity the code below may contain. I am working with code written in Excel's VBA. Eventually, the data from the user form in excel will be entered into a SQL database I have created using SQL Server 2008. Right now, I am just trying to open the connection to the SQL database and enter hard coded values into the db. Unfortunately, this has been much more of a challenge than I expected. I have tried playing around with the connection string a few different ways but have had no luck. When the form runs, I get no errors and I can see the data was added into the appropriate excel worksheet (but no changes in the SQL DB). I can see the db on SQL Server Management Studio and add rows from there, but I am unable to add a row to the db via this code. The db is protected solely by windows authentication. Any help would be greatly appreciated.

我对 SQL 和 VBA 很陌生，所以请原谅下面的代码可能包含的任何大胆。我正在使用用 Excel 的 VBA 编写的代码。最终，来自 excel 用户表单的数据将输入到我使用 SQL Server 2008 创建的 SQL 数据库中。现在，我只是尝试打开与 SQL 数据库的连接并将硬编码值输入到数据库中。不幸的是，这比我预期的要困难得多。我尝试过以几种不同的方式使用连接字符串，但没有运气。当表单运行时，我没有收到任何错误，我可以看到数据已添加到相应的 excel 工作表中（但 SQL DB 中没有任何更改）。我可以在 SQL Server Management Studio 上看到数据库并从那里添加行，但我无法通过此代码向数据库添加行。db 仅受 Windows 身份验证保护。任何帮助将不胜感激。

Sub ConnectSqlServer()
'********SPC DATABASE CONNECTION**********************
'write slurry information to database
'spc_date, mix_type, slurry_lot_num, mixer_num, shift, oper

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

'Create connection string
sConnString = "Provider=sqloledb; Server=SERV; Database=db; Trusted_Connection=True;"

'Create the Connection and Recordset objects
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Open connection and execute
conn.Open sConnString
Set rs = conn.Execute("INSERT INTO TBL (col1, col2) VALUES ('val', 'val');")

'Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

回答by Jeff Rosenberg

When inserting data, you don't need a recordset object, since an INSERTstatement doesn't return a recordset. Instead, try using a command object instead. The command object gives you more control over how your SQL statement is passed to the server, and it also gives you a way to test whether any records were inserted.

插入数据时，不需要记录集对象，因为INSERT语句不返回记录集。相反，请尝试使用命令对象。命令对象使您可以更好地控制 SQL 语句如何传递到服务器，它还为您提供了一种测试是否插入了任何记录的方法。

I'd also recommend setting Option Explicitat the top of each module, since it will stop you from making mistakes with variables (for instance, you have both connand oConnin your code, which I don't think you intended.

我还建议Option Explicit在每个模块的顶部进行设置，因为它会阻止您在变量上犯错误（例如，您的代码中同时包含conn和oConn，我认为这不是您想要的。

Here are my edits to your code. It's untested, but I think I've got it right. If it runs without any errors, and recordsAffectedreturns 1, but there's still nothing on the server, then we'll have to do some more digging.

这是我对您的代码的编辑。它未经测试，但我认为我做对了。如果它运行没有任何错误，并recordsAffected返回 1，但服务器上仍然没有任何内容，那么我们将不得不做更多的挖掘。

Sub ConnectSqlServer()
'********SPC DATABASE CONNECTION**********************
'write slurry information to database
'spc_date, mix_type, slurry_lot_num, mixer_num, shift, oper

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString As String
Dim recordsAffected as Long

'Create connection string
sConnString = "Provider=sqloledb; Server=SERV; Database=db; Trusted_Connection=True;"

'Open connection and execute
conn.Open sConnString
With cmd
  .ActiveConnection = conn
  .CommandType = adCmdText
  .CommandText = "INSERT INTO TBL (col1, col2) VALUES ('val', 'val');"
  .Execute recordsAffected 'Includes a return parameter to capture the number of records affected
End With

Debug.Print recordsAffected 'Check whether any records were inserted

'Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set cmd = Nothing
Set conn = Nothing

End Sub