SQL UPDATE Query from within Excel 2002 [Archive]

View Full Version : SQL UPDATE Query from within Excel 2002

kenneth900

05-28-02, 19:58

Hi all, I'm new to VBA coding (especially for Excel) and was wondering if anyone knows how I might perform a SQL UPDATE Query from within an Excel worksheet? I have the worksheet setup to query from a MS SQL Server db (via ODBC) and populate the spreadsheet. I would *LOVE* the capability of editing particular cells and then performing a macro command to export the data back, updating the original database. I can do it manually via MS-Query because all data comes from a single table, but I would really like to automate the process. If anyone could point me in a direction to get me started, I'd be grateful. Thanks-
Kenneth

rnealejr

06-01-02, 00:43

While in vba go to tools -> references. Add the MS activex data objects library.

Dim rst As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQL As String

' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=mytable;User Id=sa;Password=; "
Cnxn.Open strCnxn

Set rst = New ADODB.Recordset
strSQL= "your update statement"
rst.Open strSQL, Cnxn, adOpenDynamic, adLockOptimistic, adCmdText

Cnxn.Close
Set rst = Nothing
Set Cnxn = Nothing

kenneth900

06-03-02, 02:12

That did the trick!!! THANK YOU THANK YOU THANK YOU!! It worked like a charm! I had an idea of what the logic would be like, but I didn't understand quite how to get there. ADO works great! Thank you so much.

kenneth