Results 1 to 5 of 5

Thread: [Excel 2007 VBA-SQL]updating records

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    [Excel 2007 VBA-SQL]updating records

    I have successfully written some VBA script that allows me to loop through an ADODB recordset extracted from an MS-SQL table using a SQL SELECT statement.

    Now I want to update the records and VBA is being very unhelpful.

    I originally assumed that I would simple set the fields to new values and then use recordset.update to update the SQL table. The problem with this appears to be that the recordset is not open in update mode.

    I will put the code I am using below (because I know this expected!) but my question is not so much "what is wrong with this code?" but rather "what is the correct way in VBA to update selected records in a SQL table?"
    Code:
         strSQLCommand = "SELECT [list of fields]" & _
            " FROM Transactions" & _
            " WHERE (TransTypeID = 14)" & _
            " ORDER BY CurrencyID, CustomerID, TradeDate;"
        
        Set rsData = objConn.Execute(strSQLCommand, , adCmdText)
        
        rsData.MoveFirst
        Do While rsData.EOF = False
            'calculate intMatchQty 
            rsData.Fields(38) = rsData.Fields(38) + intMatchQty
    ERROR 3251
    Current Recordset does not support updating
    Last edited by si_the_geek; Nov 11th, 2010 at 08:21 AM. Reason: added Code tags

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [Excel 2007 VBA-SQL]updating records

    Welcome to VBForums

    The recordset is not updateable because you used <connection>.Execute to open it, which means it is opened as Read-Only and Forward-Only. In order to have either of those different, you need to use <recordset>.Open instead, eg:
    Code:
        Set rsData = New ADODB.Recordset
        rsData.Open strSQLCommand, objConn, adOpenForwardOnly, adLockOptimistic, adCmdText
    For more information about .Open, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

    One point about your code is that using positions for fields (such as rsData.Fields(38) ) is not a wise move, because a change to the SQL statement means that your code will alter the wrong fields, and you are unlikely to know about it. If you use field names instead (such as rsData.Fields("MyFieldName") ) then you wont get that kind of issue - instead you will get an obvious error, which means you can correct the problem before it does any damage.


    As to what method is "correct", it is debatable based on the circumstances, and using a recordset is an acceptable method in many cases.

    Another method is to get the database to do the work by sending it an Update statement, which in this case would be something like this:
    Code:
         strSQLCommand = "UPDATE Transactions" & _
            " SET [field38] = [field38] + " & intMatchQty & _
            " WHERE (TransTypeID = 14)"    
        objConn.Execute strSQLCommand, , adCmdText + adExecuteNoRecords
    This generally runs faster (because the data doesn't get sent back and forth), particularly as the amount of records increases. It does however require that the alterations are something that can be described in an SQL statement, which isn't always the case.

    Unfortunately moving away from the recordset can cause problems, because now you need to make sure that values are formatted in a way that the database engine expects them to be, which is worse when input comes from a user. The way to deal with that is to use a Command object and Parameters. Further info in the FAQ articles Why should I use Parameters instead of putting values into my SQL string? and How do I use an ADO Command object?

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: [Excel 2007 VBA-SQL]updating records

    Code:
        Set rsData = New ADODB.Recordset
        rsData.Open strSQLCommand, objConn, adOpenForwardOnly, adLockOptimistic, adCmdText
    This works - thank you very much

    I had originally tried to use:
    Code:
        rsData.Open strSQLCommand, objConn, adOpenKeyset, adLockReadOnly, adCmdText
    but this gave me problems (I cannot remember the details). My main reference work ("Excel 2007 VBA" from Wrox) seems to suggest that I need to use adOpenKeyset, adLockReadOnly but obviously this is not so.

    What happens if between reading the recordset and updating the SQL table another user updates a record?
    Last edited by fuzzbug; Nov 11th, 2010 at 09:36 AM.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [Excel 2007 VBA-SQL]updating records

    You will get an error, the FAQ article about .Open briefly explains where/when it will happen (it depends on the lock option).

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Posts
    14

    Re: [Excel 2007 VBA-SQL]updating records

    Quote Originally Posted by si_the_geek View Post
    You will get an error, the FAQ article about .Open briefly explains where/when it will happen (it depends on the lock option).
    How can I give my process sole access to the file while the update is running? (It is an End of Day process so other users should not be in the database but they might be in which case the EoD should refuse to run)

Posting Permissions

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



Click Here to Expand Forum to Full Width