使用Excel 2010使用存储过程读取/写入SQL Server 2008数据库 [英] Use Excel 2010 to read/write to a SQL Server 2008 database using stored procedures

问题描述

我们有一个SQL Server 2008数据库，它具有处理读/写等的存储过程。这些程序在内部由各种应用程序使用。

需要一个人直接将数据更新到名为Employee的数据库中的一个表中。更新是污垢简单;更新VARCHAR和INT（外键）字段。问题是SharePoint 2010不容易通过BCS来支持这种更新;浏览和更新不是最好的用户体验。

有人建议使用Excel和VBA轻松解决这个问题。打开时，Excel连接到SQL Server数据库，并从Employee读取存储过程读取。它还为外键引用的表执行读取sprocs。用户更新到一个字段，该字段又调用Employee update sproc。

这样做的优点是没有需要构建/托管/等;如果使用Active Directory进行身份验证，则使用该方法，DBA很好。

问题是我找不到VBA程序员或任何有用的资源或逐步有人知道这样一个在线资源，或者有一个关于如何快速获得管理界面运行的备用建议。对于单个用户？

解决方案

我最终以这种方式使用AD身份验证。我用这个帖子中的例子来启发：
http://www.eggheadcafe.com/community/sql-server/13/10141669/using-excel-to-update-data-on-ms-sql -tables.aspx

请注意，这些功能生活在Excel工作簿（对象，模块，此工作簿）的不同区域，但以下是快速参考。

我还有每个列都是FK验证他们引用的表。

这里有一些代码示例：

  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 ]
与aCon 
 .ConnectionString = sConn 
 .CursorLocation = adUseClient 
 .Open 
结束
 BuildProcs 
 End Sub 
 
 Sub BuildProcs（）
使用aCmd 
 .ActiveConnection = aCon 
 .CommandType = adCmdStoredProc 
 .CommandText =[SPROC]
 .Parameters。附加.CreateParameter（@ in_EmployeeID，adInteger，adParamInput）
结束
 
 End Sub 
 
 Sub PopulateSheet（）
 Dim n As Integer， r As Long 
 
 Dim aCmdFetchEmployees As New ADODB.Command 
 Dim aRstEmployees As New ADODB.Recordset 
 
如果aCon.State = adStateClosed然后连接
 
使用aCmdFetchEmployees 
 .ActiveConnection = aCon 
 .CommandType = adCmdStoredProc 
 .CommandText =[SPROC]
设置aRstEmployees = .Execute 
结束
r = aRstEmployees.RecordCount 
 
工作表（1）.Activate 
 Application.ScreenUpdating = False 
单元格（2，1）.CopyFrom记录集aRstEmployees 
 
对于n = 1到aRstEmployees.Fields.Count 
单元格（1，n）= aRstEmployees（n  -  1）.Name 
单元格（1，n）。 EntireColumn.AutoFit 
下一个
单元格（1）.EntireColumn.Hidden = True 
 End Sub 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
 Dim cell As Range 
如果aCon.State = adStateClosed然后连接
与aCmd 
 .Parameters（0）= Cells（Target.Row，1）
 .Parameters（1）=单元格（Target.Row，4）
。参数（2）=单元格（Target.Row，5）
。参数（3）=单元格（Target.Row，6）
 .Parameters 4）= Cells（Target.Row，7）
 .Parameters（5）= Cells（Target.Row，8）
 .Parameters（6）= Cells（Target.Row，10）
。参数（7）=单元格（Target.Row，11）
。参数（8）=单元格（Target.Row，12）
。参数（9）=单元格（Target.Row，13 ）
。参数（10）=单元格（Target.Row，14）
。参数（11）=单元格（Target.Row，15）
 。参数（12）=单元格（Target.Row，16）
 .Execute，，adExecuteNoRecords 
结束
 
 End Sub 
  

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.

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.

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.

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.

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.

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?

解决方案

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

Note that these functions live in different areas of the Excel Workbook (Objects, Modules, This Workbook), but here's a quick reference.

I also have each of the columns that are FK validating against the tables they reference.

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

这篇关于使用Excel 2010使用存储过程读取/写入SQL Server 2008数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！