Re: How to summarize recordset...Select Distinct alternative?

MP wrote:
Is creating a Dictionary object more efficient than creating a
Recordset object?

Yes. It's got a smaller footprint. you don't need full cursor
functionality to store a set of paired values (Item, Count). You just
need a keyed structure to allow you to access the values quickly. A
Dictionary provides this. Look it up in online help - it's really easy
to use.


and disconnect it before processing it
Set oRsPcmkCountAll.ActiveConnection = Nothing
'close the connection at this point - it's no longer needed

-------------------
now that's something I didn't know.
So if you use adUseClient, then the recordset is copied into local
memory?

Yes. ADO provides a Cursor Library that provides the cursor
functionality provided by most databases. When using a client-side
cursor, ADO retrieves all the data requested by the sql statement using
an efficient firehose cursor and deposits it into a static cursor from
the Cursor Library.



all my database actions create / connect / execute etc are wrapped in
a class i made to "encapsulate" (read compensate for my poor memory
<g> ) all the connection string stuff etc...all of which was new to
me and therefore not easy to remember all the syntax etc.

so now I could add disconnecting the connection...if the operation is
read only that is....as in this example it happens to be....


It doesn't have to be readonly. A disconnected recordset can be
reconnected to the database simply by setting the ActiveConnection to an
open connection object. You can perform all the functions associated
with a disconnected recordset (including Update, AddNew and Delete) that
you can perform with a connected recordset. The only difference is that
changes are not done to the database data ... unless you open the
recordset using a LockType of adLockOptimisticBatch, which allows you to
use the UpdateBatch method after reconnecting the recordset to cause
your changes to be sent to the database.




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: Populating a Pull-Down
    ... you are also forcing ADO to make a second trip to the database ... Why open a less-efficient static cursor? ... I'm surprised you are not getting an error from "User Query". ... This line is not necessary, and in fact, may cause the recordset to be ...
    (microsoft.public.scripting.vbscript)
  • Re: Gebundene Controls aus ADO-Recordset aktualisieren
    ... Engpass ist vor allem das LAN und der Server selbst. ... ob man mit einem SQL-Server oder der Jet-Engine ... Cursor und statischen Recordsets, egal welches Datenbanksystem ... dass eine Bewegung im Recordset eben auch ...
    (microsoft.public.de.vb.datenbank)
  • Re: Gebundene Controls aus ADO-Recordset aktualisieren
    ... Wozu ein serverseitiger Cursor? ... Ich kann also z.B. bei adOpenKeyset im Programmcode ... CursorLocation adUseServer bei Access sinnlos, ... wenn Du in Deinem Recordset zu einem anderen ...
    (microsoft.public.de.vb.datenbank)
  • Re: Suche mit SEEK
    ... ein Recordset mit serverseitigem Cursor ... Das heisst also es muss ein Recordset mit serverseitigem Cursor ... clientseitigem Cursor arbeiten und die Suche nach einem bestimmten Datensatz ...
    (microsoft.public.de.vb.datenbank)
  • Re: CursorType, LockType von ADO-Recordsets
    ... Use the CursorType property to specify the type of cursor that should be used when opening the Recordset object. ... If an unsupported value is set, then no error will result; the closest supported CursorType will be used instead. ...
    (microsoft.public.de.access.clientserver)