Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002

    Question Unanswered: SQL UPDATE Query from within Excel 2002

    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-

  2. #2
    Join Date
    Feb 2002
    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

    Set rst = Nothing
    Set Cnxn = Nothing
    Last edited by rnealejr; 06-01-02 at 00:47.

  3. #3
    Join Date
    May 2002

    thank you!

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts