CODEWITHSUNDEEP
sqlexcelvba
rmon2852
rmon2852

Reputation: 220

Update SQL database through excel VBA

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.

Upvotes: 0

Views: 9027

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

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

Upvotes: 1

jTC
jTC

Reputation: 1350

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.

Upvotes: 0

Related Questions