connection.Execute command

[multiplex77]

Hi,

Is the command:

connection.Execute "Update table1 set field1 = 'text1'"

...recommended? or is it better to use:

recordset.Open "Update ..."?

I was told that the Execute command gives problems esp. in a multi-user environment. Is this true? What other problems (if any) does it cause.

I don't need to view or manipulate the recordset. Just need to execute the Update or Insert query.

Thanks for the help!

 

[softwarescience]

Hello

Yes there are issues if you use the connection object to execute.

It is best to use the command object i.e.

Dim comA As New ADODB.Command
comA.ActiveConnection = ConnectionObject
comA.CommandText = "Update table1 set field1 = 'text1'"
comA.CommandType = adCmdText
comA.Execute

This relieves multiuser issues.

Hope this helps

 

[mccloud1982]

if that the case, is it advisable to use a recordset then instead of using the execute?


is it possible to define what is pro and con then and what are the tradeoff?

oh ya i am also working on a project related to this

 

[softwarescience]

I would say if you can use a command to complete an update then that is prefferable, mainly because you are allowing the server to do the work and all you are passing across the network is the update query, rather then trying to maintain an updatable recordset.

There are some quite resonable white papers regarding ADO on microsofts MSDN site.

 

[adnanakram]

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
dim varQuery as String
-----------------------------------------------------------
With con
.Provider = "microsoft.jet.oledb.4.0"
.ConnectionString = App.Path & "\Table.mdb"
.Open
End With
----------------------------------
so in case of insertinon use this
----------------------------------
varQuery = "insert into tablename values ol.name1,col.name2,.....)"

----------------------------------
so in case of updation
----------------------------------
varQuery = "update tablename set col.ame = changevalue where col.name = against which thing u need"

rs.Open varQuery, con, adOpenDynamic, adLockOptimistic

surely this will work