Posting a solution to a problem I had.
In the the following code I was getting an error something like "80004005
too many columns selected"
After long time searching the a2k help and this NW, I found the error. The
query string I was using to build the RS hadn't all the table fields! When I
select all the fields, even if I don't want to all the fields!
So here it is, in case it helps someone.
BTW, with an UPDATE query I had no problem having the work done. The thing
is, I dind't find out how to use an update query and having transactions at
the same time.
RV
sub abc()
Dim cnn As ADODB.Connection
Dim cnnStr, sSQL
Dim rs As ADODB.Recordset
Set cnn = CurrentProject.Connection 'BTW, is this a recommend
approach or shall I define my own cnnStr?
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockPessimistic
'this query string works fine
sSQL = "select * from my_table whey my_key=112233;" 'instead of *,
the list of fields would be better!
'this query string was making the problem
'sSQL = "select field1, field2 from my_table whey my_key=112233;"
rs.Open sSQL
rs.MoveFirst
cnn.BeginTrans
While Not rs.EOF
rs("field1") = 123
rs("field2") = "abc"
rs.Update 'the error was showing up here. And yes, these two
fields where changed for all rows of the table!
rs.MoveNext
Wend
cnn.CommitTrans
rs.Close
Set cnn = Nothing
end sub