Update SQL database through excel VBA

Question

First off I understand the process of having a user update a db via excel runs alot of risks, I appreciate any advise against this method but lets speculate that I have the perfect user who never makes mistakes :)

So firstly I've managed to access a stored procedure via vba in excel to pull data based on a parameter in a particular cell. Now if I wanted to update these fields how would I go about doing this?

My first suggestion would be to create a stored procedure that updates based on all the fields in spreadsheet. If this is the right avenue to follow how would I loop through all the rows?

Any other suggestions people could offer would be greatly appreciated.

Fionnuala · Accepted Answer · 2012-10-02 11:08:15Z

Some notes on updating SQL Server. This [ODBC;FileDSN=z:\docs\test.dsn] can be any valid connection string.

Dim cn As Object,  rs As Object
Dim scn As String, sSQL As String, sFile As String

    sFile = ActiveWorkbook.FullName

    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    ''Late binding, so no reference is needed

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Open scn

    sSQL = "SELECT * INTO [ODBC;FileDSN=z:\docs\test.dsn].FromXL " _
         & "FROM [Sheet8$]"
    cn.Execute sSQL, recs
    Debug.Print recs

    sSQL = "INSERT INTO [ODBC;FileDSN=z:\docs\test.dsn].FromXL " _
         & "SELECT * FROM [Sheet8$]"
    cn.Execute sSQL, recs
    Debug.Print recs

    sSQL = "UPDATE [ODBC;FileDSN=z:\docs\test.dsn].FromXL x " _
         & "INNER JOIN [Sheet8$] w " _
         & "ON x.ID = w.ID SET x.Field =w.field"
    cn.Execute sSQL, recs
    Debug.Print recs

    rs.Open "SELECT * FROM [ODBC;FileDSN=z:\docs\test.dsn].FromXL", cn
    Debug.Print rs.GetString

jTC · Accepted Answer · 2012-10-01 21:08:28Z

You have to familiarize yourself with Microsofts COM Object. My VBA is rusty so watch out.

str t = Range("A1:A4").Value

If you are doing a lot of dynamic processing you should make yourself a wrapper method to some of the microsoft stubs.

function getValue(str location)
    return Range(location + ":" + location).Value
end function

I've done large VBA projects before and reading values from the front end is a costly operation. You should set up objects to hold these values so you never have to read the same value more than once.

Lastly, you need lots and lots of validation, from everything between checking the database to make sure the user has the latest version to making sure that the spreadsheet isn't in print view (because this affects the range being able to read).

Verifying the version is important because you are going to need to release fixes and you need to verify that those fixes are being used.

Collectives™ on Stack Overflow

Update SQL database through excel VBA

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related