vba 使用 Excel 2010 通过存储过程读取/写入 SQL Server 2008 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9508454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use Excel 2010 to read/write to a SQL Server 2008 database using stored procedures
提问by user1241463
We have a SQL Server 2008 database that has stored procedures to handle reads/writes/etc. These procedures are used by a variety of applications internally.
我们有一个 SQL Server 2008 数据库，它具有处理读/写/等的存储过程。这些过程由内部的各种应用程序使用。
The need has come up for a single person to make updates directly to a one of the tables in the database called Employee. The update is dirt simple; update VARCHAR and INT (foreign key) fields. The problem is that SharePoint 2010 doesn't easily support this kind of update through BCS; browsing and updating isn't the best user experience.
需要一个人直接更新数据库中名为 Employee 的表之一。更新很简单；更新 VARCHAR 和 INT（外键）字段。问题是SharePoint 2010 不容易通过BCS 支持这种更新；浏览和更新不是最好的用户体验。
It's been suggested that this is easily solved with Excel and VBA. On open, Excel connects to the SQL Server database and reads from the Employee read stored procedure. It also executes the read sprocs for the tables the foreign keys reference. The user makes an update to a field, which in turn calls the Employee update sproc.
有人建议使用 Excel 和 VBA 可以轻松解决此问题。打开时，Excel 连接到 SQL Server 数据库并从 Employee read 存储过程读取。它还为外键引用的表执行读取过程。用户对字段进行更新，然后调用 Employee 更新 sproc。
The advantages to this are that there is no website interface that needs to be built/hosted/etc; the DBA is fine with this approach if Active Directory is used to authenticate.
这样做的好处是没有需要构建/托管/等的网站界面；如果使用 Active Directory 进行身份验证，DBA 可以使用这种方法。
The problem is that I can't find a VBA programmer or any helpful resources or step-by-step walk throughs to write the VBA.
问题是我找不到 VBA 程序员或任何有用的资源或逐步演练来编写 VBA。
Does anyone know of such an online resource and/or have an alternate suggestion on how to quickly get an admin interface up and running for the single user?
有没有人知道这样的在线资源和/或有关于如何为单个用户快速启动和运行管理界面的替代建议？
采纳答案by user1241463
I ended up going with this approach using AD authentication. I used the example in this post for inspiration: http://www.eggheadcafe.com/community/sql-server/13/10141669/using-excel-to-update-data-on-ms-sql-tables.aspx
我最终采用了这种使用 AD 身份验证的方法。我用这篇文章中的例子来获得灵感：http: //www.eggheadcafe.com/community/sql-server/13/10141669/using-excel-to-update-data-on-ms-sql-tables.aspx
Note that these functions live in different areas of the Excel Workbook (Objects, Modules, This Workbook), but here's a quick reference.
请注意，这些函数位于 Excel 工作簿的不同区域（对象、模块、本工作簿），但这里有一个快速参考。
I also have each of the columns that are FK validating against the tables they reference.
我也有针对它们引用的表进行 FK 验证的每一列。
Here are some code samples:
下面是一些代码示例：
Public aCon As New ADODB.Connection
Public aCmd As New ADODB.Command
Private Sub Workbook_Open()
Application.EnableEvents = False
PopulateSheet
Application.EnableEvents = True
End Sub
Sub Connect()
Dim sConn As String
sConn = "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=[SVR];Database=[DB]"
With aCon
.ConnectionString = sConn
.CursorLocation = adUseClient
.Open
End With
BuildProcs
End Sub
Sub BuildProcs()
With aCmd
.ActiveConnection = aCon
.CommandType = adCmdStoredProc
.CommandText = "[SPROC]"
.Parameters.Append .CreateParameter("@in_EmployeeID", adInteger, adParamInput)
End With
End Sub
Sub PopulateSheet()
Dim n As Integer, r As Long
Dim aCmdFetchEmployees As New ADODB.Command
Dim aRstEmployees As New ADODB.Recordset
If aCon.State = adStateClosed Then Connect
With aCmdFetchEmployees
.ActiveConnection = aCon
.CommandType = adCmdStoredProc
.CommandText = "[SPROC]"
Set aRstEmployees = .Execute
End With
r = aRstEmployees.RecordCount
Worksheets(1).Activate
Application.ScreenUpdating = False
Cells(2, 1).CopyFromRecordset aRstEmployees
For n = 1 To aRstEmployees.Fields.Count
Cells(1, n) = aRstEmployees(n - 1).Name
Cells(1, n).EntireColumn.AutoFit
Next
Cells(1).EntireColumn.Hidden = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If aCon.State = adStateClosed Then Connect
With aCmd
.Parameters(0) = Cells(Target.Row, 1)
.Parameters(1) = Cells(Target.Row, 4)
.Parameters(2) = Cells(Target.Row, 5)
.Parameters(3) = Cells(Target.Row, 6)
.Parameters(4) = Cells(Target.Row, 7)
.Parameters(5) = Cells(Target.Row, 8)
.Parameters(6) = Cells(Target.Row, 10)
.Parameters(7) = Cells(Target.Row, 11)
.Parameters(8) = Cells(Target.Row, 12)
.Parameters(9) = Cells(Target.Row, 13)
.Parameters(10) = Cells(Target.Row, 14)
.Parameters(11) = Cells(Target.Row, 15)
.Parameters(12) = Cells(Target.Row, 16)
.Execute , , adExecuteNoRecords
End With
End Sub
回答by BizApps
Hi you can start with this.
你好，你可以从这个开始。
Create Macro Button on your excel file. Click New and then add this code.
在您的 excel 文件上创建宏按钮。单击新建，然后添加此代码。
Sub Button1_Click()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim SNfound As String
'Your sqlserver 2008 connection string
Const stADO As String = "Provider=SQLOLEDB.1;" & _
"" & _
"Initial Catalog=YOurDB;" & _
"Data Source=YourServer;UID=yourusername;PWD=yourpassword;"
'SELECT FROM STORED PROCEDURE
' eg: select SN from SNTable where SN=@SN
stSQL = "exec usp_SelectSN '" & "TESTSN" & "'"
Set cnt = New ADODB.Connection
With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
If rst.RecordCount = 0 Then
'NO SN FOUND ON YOUR DB
Else
'RECORDS FOUND SHOW Retrieve SN from DB to message box
SNfound = rst.Fields("SN")
MsgBox ("Found:" & SNfound)
End If
Set rst = Nothing
Set cnt = Nothing
End Sub
Regards
问候
回答by Nat
I would recommend that you create a simple Webpart that does the edit. You will have an easier time finding someone who can do C# to a database than vba. Coding a very rough webpart to any coding in this manner is pretty easy.
我建议您创建一个简单的 Webpart 来进行编辑。与 vba 相比，您将更容易找到可以对数据库执行 C# 操作的人。以这种方式将非常粗糙的 webpart 编码为任何编码非常容易。
There are also plenty of coding samples for C# and webparts available.
还有大量的 C# 和 webparts 编码示例可用。
You also have the benefit of keeping the code on the server without potentially exposing username/password combinations in clear text within an office document.
您还可以将代码保存在服务器上，而不会在办公文档中以明文形式公开用户名/密码组合。
This approach also enables you to smoothly improve the "quality" of the solution if it becomes more mission critical over time.
如果解决方案随着时间的推移变得更加关键，这种方法还可以让您顺利提高解决方案的“质量”。
It does sound that you are following a scenario covered nicely by this example
听起来您正在遵循此示例很好地涵盖的场景
