On Jun 25, 9:50 am, mark_aok <mark_...@hotmail.comwrote:
On Jun 21, 4:20 pm, Rich P <rpng...@aol.comwrote:
Hi Mark,
Instead of using a Recordset object try using an ADODB.Command object:
Sub UpdateUsingCmd()
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "Update tbl1 Set fld2 = 'test' Where fld1 = 'Joe'"
cmd.Execute
End Sub
The ADODB.Command object is more reliable and efficient than a Recordset
object. Best practice is to steer away from looping when possible (it
is not always possible to get away from looping - but how you create
your loop is also another thing to look at). Loops have more parts and
create more room for error(s).
Rich
*** Sent via Developersdexhttp://www.developersdex.com***
Thanks a lot Rich, the ADODB.Command object worked perfectly, and I've
tried to integrate it into my application whenever possible. The only
problem with it...is that it doesn't seem able to return recordset's
properly. Every time I use it to return a recordset...
Dim rs as adodb.recordset
...
Set rs = cmd.execute
...
It gives a readonly, only forward opening recordset with a recordcount
of -1!!! Is it possible to use ADODB.command to modify data??
Mark
1. Instead of using rs.Filter you could put the filter criteria in the
SQL itself, as in
rs.open _
"SELECT * FROM [ITEM] " & _
"WHERE [storageArea]='Cabinet A'", _
CurrentProject.Connection, _
adOpenStatic, _
adLockOptimistic
2. Instead of a For loop using rs.RecordCount you could just use
Do While Not rs.EOF
' do interesting stuff here
rs.MoveNext
Loop
3. As Rich hinted, in this case you don't need a loop at all. You just
need
CurrentProject.Connection.Execute _
"UPDATE [ITEM] SET " & _
"[storageArea]='Cabinet B' " & _
"WHERE [storageArea]='Cabinet A'"