从Excel 2010到SQL Server 2008的VBA连接 [英] VBA Connection from Excel 2010 to SQL Server 2008

问题描述

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

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.

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

推荐答案

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

When inserting data, you don't need a recordset object, since an INSERT statement 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.

d还建议在每个模块的顶部设置 Option Explicit ，因为它将阻止您使用变量进行错误（例如，您同时拥有 conn

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

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

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