Solved

Best Practices -> ADODB Recordset vs DoCmd.RunSQL

Posted on 2006-11-02
3
1,027 Views
Last Modified: 2008-02-01
Hi Experts,

I'm making good use of ADODB recordsets. The capabilities they offer are amazing.

I have some operations, however, where I don't really need all the capabilities of a recordset. A simple SQL statement will do the job just fine.

Are there any recommendation when to use recordsets vs. plain SQL?

Thanks for your help.
0
Comment
Question by:arettig
  • 2
3 Comments
 
LVL 8

Expert Comment

by:dds110
ID: 17860662
You can use the ADODB Connection object to run execute Insert, Update, and Delete SQL statements without having to open a Recordset Object.

For example:

Dim db As New ADODB.Connection
With db
  .Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='yourDbPath\blah.mdb';User Id=Admin;Password=;")
  .Execute ("INSERT INTO Table1 (blah) VALUES('blah')")
  .Close
End With
0
 
LVL 8

Accepted Solution

by:
dds110 earned 500 total points
ID: 17860689
In addition to my previous post, The recordset object is good for returning records but tends to bulk things up when all you need to do is update, delete, or insert.   DoCmd.RunSql is access specific and won't work, for instance, if you decide to put a web front end on top of your db.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 17861351
There is no right answer, only some tradeoffs to consider.  The matter is very similar to the difference between using sql set operations or cursors. Performance is one issue--not only which is more efficient, but where do you want to put the workload.  Maintainability is another issue.

SQL operations occur predominantly within the DBMS and have an efficiency advantage over recordsets. To effect an SQL command the DBMS may construct an implicit cursor (much like a recordset) internally, but it is nearly always assured to use less resources than an explicit cursor that does the same thing. Performance advantage goes to SQL set operations.

SQL operations yield a relatively thin client, meaning the DBMS does most of the work.  They also tend to put less traffic on the network.  I can't really say that is an advantage, it depends on which parts of the system you desire to burden.  Sometimes you may want to leverage the more powerful DBMS server and give the network a break. Other times it may be preferable to distribute the workload to tens or hundreds of clients.  Be aware, that it also depends on the particular thing you are doing.  Sometimes the tradeoff is between a lot of work on the server and a little work on the client, in which case the client can do it faster anyway.  For example, suppose we have a table with ten rows and we need to look at each row twice.  With SQL we would be making 20 requests to the DBMS and sending 20 rows over the network.  With a recordset, we make 1 request to the DBMS and send only 10 rows over the network.  Also, note that with an Access backend, this maybe somewhat of a non-issue because all the work is done at the workstation, anyway.  But with more traditional RDBMS's it may be very significant.

From a maintenance and development standpoint, I give recordsets the advantage.  In my opinion the SQL string constructions are often simpler and the follow-on code is easier to follow. Notwithstanding that, some things that are very simple to do with a recordset are very difficult with SQL.  Ever try returning a field containing a concatenated list someone's children's names?  Trivial with a recordset, inexorably difficult with SQL.  IMHO, recordset techniques are more flexible and usually easier to maintain than native SQL. They also lend themselves to a more procedural programming style.  While not necessarily a good thing, many programmers are more comfrotable that way.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question