Use Excel 2010 to read / write to a SQL Server 2008 database using stored procedures

Question

We have a SQL Server 2008 database that stores reads / write / etc processing procedures. These procedures are used in many domestic applications.

There was a need for one person to be able to make updates directly in one of the tables in the Employee database. The update is simple; update the VARCHAR and INT fields (foreign key). The problem is that SharePoint 2010 does not just support this type of upgrade through BCS; viewing and updating is not the best user interface.

It has been suggested that this is easily solved using Excel and VBA. In the window that opens, Excel connects to the SQL Server database and reads Employee from the stored procedure. It also performs sprocs reading for foreign key reference tables. The user updates the field, which in turn triggers an Employee sproc update.

The advantages of this are the lack of a website interface that must be built / hosted / etc .; DBA is great for this approach if Active Directory is used for authentication.

The problem is that I cannot find a VBA programmer or any useful resources or walkthroughs to write VBA.

Does anyone know about such an online resource and / or have an alternative suggestion on how to quickly launch the admin interface for a single user?

Answer 1

I ended 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 is a short link.

I also have each of the columns that the FKs check for the tables that they reference.

Here are some sample code:

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 

Answer 2

Hi, you can start with this.

Create a macro button in an excel file. Click Create, and then add this code.

 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 

Hi

Answer 3

I would recommend you create a simple website that will edit. It will be easier for you to find someone who can do C # in the database than vba. Encoding a very crude web part for any encoding in this way is pretty simple.

There are also many coding examples for C # and available web pages.

You can also save the code on the server without the potential disclosure of user name and password combinations in clear text in an office document.

This approach also allows you to smoothly improve the “quality” of a solution if it becomes more critical over time.

It sounds like you are following a well-chosen scenario in this example