Performance difference between DoCmd.RunSql and ado.Execute? 3

[steve728]

Is there any peformance difference between manipulating data using DoCmd.RunSql "..." versus ado.Execute "..." ???

Steve

 

[Zion7]

Since ADO requires opening and closing a connection object, I would assume RunSQL, has less overhead.
I use this often,
CurrentProject.Connection.Execute _
"INSERT INTO tblNumber(txtNumber)VAL..."

 

[JerryKlmns]

Although CurrentProject.Connection is always an open valid connection object, if you run non-select statements, add

CurrentProject.Connection.Execute sqlStatement, , 129 [green]' = adCmdText + adExecuteNoRecords[/green]

for returnig no recordset thus runs faster.

 

[steve728]

Thanks for your input! JerryKlmns, I noticed you entered "129" for the RecordsAffected argument. Is this the exact number of records to process or is it a code meaning we chose an option of adExecuteNoRecords? Can I optionally enter the adExecuteNoRecords for this argument?

I've never used these arguments before. I looked within the help and found no reference to the 129 number. Please give me a little more detail. This advise is REALLY helpful to me.

Steve G.

 

[RoyVidar]

129 is as JerryKlmns states, the constants adCmdText and adExecuteNoRecords added up. This is what is passed as the options arguement when using the .Execute method of the connection. Help file states something like:

"Options, Optional long value that indicates how the provider should evaluate the CommandText arguement. Can be a bitmask of one or more CommandTypeEnum or ExecuteOptionEnum values."

Here it's a combination of adCmdText meaning dynamic sql string, and adExecuteNoRecords which means that no recordset is returned by the operation.

Of other CommandTypeEnums that are often relevant, you can use adCmdTable for table (when specifying a table as "sqlstring", it will for some reason return the equivalent of "select * from thattable"), adCmdTableDirect - which makes it possible to use the .Seek method, adCmdStoredProc - for stored queries (Access) and stored procedures ... but I suppose there should be a term or two here to play with in the object browser (F2) or the help files;-)

Roy-Vidar