Solved

Best Practices -> ADODB Recordset vs DoCmd.RunSQL

Posted on 2006-11-02
3
1,061 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

626 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