Solved

Best Practices -> ADODB Recordset vs DoCmd.RunSQL

Posted on 2006-11-02
3
1,045 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

738 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