Re: Is this the right method for opening recordsets in ADO ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Val Mazur (group51a_at_hotmail.com)
Date: 03/20/04 

Date: Sat, 20 Mar 2004 15:33:53 -0500

Hi Oscar,

My opinion is that best way to open recordset is to get it opened on a
client side, disconnect and work with it. In this case you do not keep
connection busy to support your opened recordset. Also it is always good
idea to use explicit INSERT, UPDATE and DELETE SQL statement to manipulate
records directly in a database, rather then to open recordset and
delete/update/insert records there. 

-- 
Val Mazur
Microsoft MVP
"Oscar" <oku@xs4all.nl> wrote in message 
news:405b2bad$0$32699$e4fe514c@dreader17.news.xs4all.nl...
> What is the good way to open recordsets in ADO. I've seen that I can open 
> a
> connection object just one time and have all recordsets when they are
> created refer to this and only connection object. However, I also have 
> seen
> that SQL server reports different errors in case that more than one
> recordsets are coupled to the same connection object. This might happen 
> when
> a new (large) recordset is created while former (large) recordsets have 
> not
> been closed yet. All these problems disappeared in case that I created a 
> new
> connection object before that I assigned a new recordset to the connection
> object.
> However creating a new connection object each time that a new recordset 
> has
> to be created might be too much time consuming especially in case that the
> database resides on a webserver at the internet.
>
> Please advise me whether I apply a good approach to opening of recordsets 
> or
> should I follow another method.
>
> environment : Visual Basic , SQL Server 2K,  ADO2.7,  OLEDB
>
> regards,
> Oscar
>
>

Relevant Pages

  • Re: delete records from database
    ... ' Creating Recordset Object and opening the database ... Dim rsDeleteEntry 'Holds the recordset for the record to be deleted ... 'Set an active connection to the Connection object using a DSN-less ... The best way to solve this is to use a saved parameter query. ...
    (microsoft.public.inetserver.asp.db)
  • Re: delete records from database
    ... ' Creating Recordset Object and opening the database ... Dim rsDeleteEntry 'Holds the recordset for the record to be deleted ... 'Set an active connection to the Connection object using a DSN-less ... The best way to solve this is to use a saved parameter query. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Iterating Functions
    ... Re-Read the table "ORDER BY" the sequence number ... indicates a loop!! ... nested Recordset objects but one Connection object with success. ...
    (microsoft.public.scripting.vbscript)
  • Re: SQLs
    ... Microsoft FrontPage MVP ... || Web Database Development Step by Step .NET Edition ... > 'Set an active connection to the Connection object using ... > 'Create an ADO recordset object ...
    (microsoft.public.frontpage.programming)
  • Re: SQLs
    ... the database ... 'Set an active connection to the Connection object using ... 'Create an ADO recordset object ... 'Initialise the strSQL variable with an SQL statement to ...
    (microsoft.public.frontpage.programming)