Re: Which is best - recordsets/VBA V SQL statements for updating data

SQL is better than Recordset Updating and Vice Versa.

When using a Server DB, SQL happens on the Server. Suppose the SQL is
demanding and takes 5 minutes to run. Suppose 20 Users call the SQL (on
discrete sets of records) right now. Can the Server machine deal with
the demand? Probably, it can. But is this more efficient than each User
getting his/her set of records and processing them on the Client
machine? Who knows? It may take some thought, perhaps even testing to
decide.

Sometimes we inherit non-normalized DBs. The data is related but there
is no clear rule that defines the relationship. We find we have to
examine (with a function in code) much of the data in each record to
find which record in Table1 relates to which record in Table2. And then
we have to do the work, eg, create genealogical html files and upload
them to our genealogy site. Is this a job for SQL or for Recordset
manipulation? I use Recordset manipulation.

Sometimes we process data in multiple scans. During these scans we are
trying, testing, discarding and choosing. After each scan we assess our
results with respect, not to one row, but to the whole data. Is a
Recordset a good tool for this? I think so. If we are going to change
the value of something 47 times, do we want to write each of those
changes to our DB? Disconnecting the Recordset and doing our work in
memory seems a better idea to me.

With recent versions of MS-SQL one can do almost anything that one can
do with a Recordset and VBA on the Client machine, with a Stored
Procedure or UDF on the Server machine, using loops, conditional,
cursors or whatever. The first situation I describe above becomes quite
important. How much work do you want the Server machine to do? How much
work do you want the Client machine to do? The Server machine is
serving many Clients. The Client machine is probably serving one
Client.

Disconnected Recordsets, reconnection and UpdateBatch make your
question much more pertinent than it was a few years ago. With them we
can have a mini independent data store on our Client machines. We can
play with it forever, then reconnect it and save our changes in an
instant.

And of course, with a clever use of Table and Index Creation within
Transactions we can effect disconnected Recordsets in DAO. [We always
Rollback the Transaction]. Nothing is ever written to the DB (unless we
want it to be). Such procedures while demanding to code can be
extremely fast and powerful. (They can blow SQL away after the Tables
and Indexes have been created, but we have to have enough work for them
to so that this advantage makes the time required for their creation
worthwhile).

After all that, I must note that I estimate that I use SQL 95%of the
time.

.

Relevant Pages

  • Re: Geschwindigkeit optimieren ADO
    ... öffnen mit jeweils anderem SQL String ... -SQL Server Stored Procedure mit Command Objekt aufrufen-> Recordset ... adOpenForwardOnly, adLockReadOnly, auch mal Firehose Cursor genannt. ...
    (microsoft.public.de.sqlserver)
  • Re: Simple Insert Into...
    ... The form is based on an SQL in the form's Record Source property, ... holds the current text in the control, ... Sometimes the unbound controls do have a value (such as when they are set ... I need this value to assign a unique ID to the entire recordset being ...
    (microsoft.public.access.modulesdaovba)
  • Re: Which is best - recordsets/VBA V SQL statements for updating data
    ... most of my work involves SQL Server rather than Jet. ... Is this a job for SQL or for Recordset ... >> do with a Recordset and VBA on the Client machine, ... >> Procedure or UDF on the Server machine, using loops, conditional, ...
    (comp.databases.ms-access)
  • Re: I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it
    ... > and if I do the same sql querys using query analizer, the sql server ... maintaining your code know what each recordset contains? ... Always use an explicit connection object. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Which is best - recordsets/VBA V SQL statements for updating data
    ... I doubt that I would do the same, though I think I'll use SQL ... Is this a job for SQL or for Recordset ... > do with a Recordset and VBA on the Client machine, ... > Procedure or UDF on the Server machine, using loops, conditional, ...
    (comp.databases.ms-access)