-
Nov 11th, 2010, 08:13 AM
#1
Thread Starter
New Member
[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
-
Nov 11th, 2010, 08:45 AM
#2
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?
-
Nov 11th, 2010, 09:32 AM
#3
Thread Starter
New Member
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.
-
Nov 11th, 2010, 09:46 AM
#4
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).
-
Nov 11th, 2010, 10:12 AM
#5
Thread Starter
New Member
Re: [Excel 2007 VBA-SQL]updating records
Originally Posted by si_the_geek
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|