Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Best Practices -> ADODB Recordset vs DoCmd.RunSQL

Posted on 2006-11-02
3
Medium Priority
?
1,101 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

877 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