Excel VBA to SQL Server without SSIS

Excel problem: User clicks a button and VBA parses an input file, putting data into cells in the spreadsheet. Then she mails copies of the spreadsheet to people who do work with the data.

I am to replace this with SSRS or ASP or Sharepoint displaying the data from SQL Server.

In order to work on this without interrupting the current process, I'd like to have the Excel VBA, each time it writes a row to the spreadsheet, also insert it into the SQL Server DB via stored proc.

I can have it write the row in CSV to a file for later SSIS import, but I'd rather go direct to the DB.

I know how to do it in VB.N开发者_开发技巧et but I've never written data in VBA (often read data into recordset but not written).

I'd prefer to pass the values as params to a stored proc, but I could generate the slower INSERT command for each row if I have to.

---

From VBA, the easiest data-access library to use is ADO. Add a reference to "Microsoft ActiveX Data Objects Library" so that you can use the ADODB.* objects.

To execute a stored proc (which in your case will add a record to a table), you could do it:

...the lazy way (creating SQL statements directly, without using Parameter objects; this is prone to SQL-injection hacks):

Public Sub AddFoo _
( _
    strServer As String, _
    strDatabase As String, _
    strUsername As String, _
    strPassword As String, _
    lFooValue As Long _
) 

    ' Build the connection string

    Dim strConnectionString As String
    strConnectionString = "Driver={SQL Server}" _
                            & ";Server=" & strServer _
                            & ";Database=" & strDatabase _
                            & ";UID=" & strUsername _
                            & ";PWD=" & strPassword


    ' Create & open the connection

    Dim oConnection As Connection
    Set oConnection = New Connection

    oConnection.ConnectionString = strConnectionString

    oConnection.Open


    ' Build the SQL to execute the stored procedure

    Dim strSQL As String
    strSQL = "EXEC AddFoo " & lFooValue


    ' Call the stored procedure

    Dim oCommand As Command
    Set oCommand = New Command

    oCommand.CommandType = adCmdText

    oCommand.CommandText = strSQL

    oCommand.ActiveConnection = oConnection

    oCommand.Execute


    oConnection.Close

End Sub

...or the correct way (which deals with encoding of all parameters, and is thus not prone to SQL-injection hacks - either deliberate or accidental):

Public Sub AddFoo _
( _
    strServer As String, _
    strDatabase As String, _
    strUsername As String, _
    strPassword As String, _
    lFooValue As Long _
) 

    ' Build the connection string

    Dim strConnectionString As String
    strConnectionString = "Driver={SQL Server}" _
                            & ";Server=" & strServer _
                            & ";Database=" & strDatabase _
                            & ";UID=" & strUsername _
                            & ";PWD=" & strPassword


    ' Create & open the connection

    Dim oConnection As Connection
    Set oConnection = New Connection

    oConnection.ConnectionString = strConnectionString

    oConnection.Open


    ' Build the SQL to execute the stored procedure

    Dim strSQL As String
    strSQL = "EXEC AddFoo " & lFooValue


    ' Create the command object

    Dim oCommand As Command
    Set oCommand = New Command

    oCommand.CommandType = adCmdStoredProc

    oCommand.CommandText = "AddFoo"


    ' Create the parameter

    Dim oParameter As Parameter
    Set oParameter = oCommand.CreateParameter("foo", adParamInteger, adParamInput)

    oParameter.Value = lFooValue

    oCommand.Parameters.Add oParameter


    ' Execute the command

    oCommand.ActiveConnection = oConnection

    oCommand.Execute


    oConnection.Close

End Sub

---

How do you read data with VBA?

If you use ADO recordsets: Have a look at the ADODB.Command class; this allows you to execute SQL or stored procedures and pass parameters to it (Google for ado command example).

If you use DAO recordsets: The Execute method of your DAO database allows you execute SQL statements.

---

In the long run, people are finally beginning to accept a better way: automation (not a button click) reads the file directly into the DB (SSIS), and people who need the data look at a report instead of an e-mailed Excel file.