Fog Creek Software
Discussion Board

Managing ADO/DAO Connections and Recordsets

Anyone who has worked with ADO or DAO in the VB/VBScript environment has had to deal with connections and recordsets.  My question is, 'How do you manage these?'.  Do you use classes, functions or simply code everything inline?  Do you experience a slow down when using classes?

I would also like to know if setting a recordset to nothing is safe without also closing and setting the connection to nothing.  Does'nt setting the recordset to nothing implicitly close AND destroy its connection?  I have'nt been able to dig up any documentation on this.  Although MS sample code seems to simply set the Recordset to nothing.

Here is a 'sample' class that I use.

'class CDatabase
  Private strCon as String, Conn as ADODB.Connection
  Public rs as ADODB.Recordset
  Private Sub Class_Initialize
    strCon = "..."
    set Conn = new ADODB.Connection
    call Conn.Open(strCon,"...","...")
  End Sub
  Public Sub OpenRS( strSQL )
    set rs = Conn.Execute( strSQL )
  End Sub
  Public Sub ExecuteSQL( strSQL )
    call Conn.Execute( strSQL )        
  End Sub    
  Private Sub Class_Terminate
    set rs = nothing
    set Conn = nothing
  End Sub
'End Class

Tuesday, February 4, 2003


Check out:

We have been discussing a similar ADO issue today. And looking at your code, I can see a few things you should change, which the list will be able to help you with.


Matthew Wills
Tuesday, February 4, 2003

Use classes, especially if it helps reuse. It's hard to cut and paste without a clearly defined interface. Makes maintaining sites hell. Most companies seem to use VBScript capabilities like include files, class members, private scoping, object life-times at the very least.
If you over abstract things you'll notice things will run at 1/2 or 1/3 speed at the very worst. If you really need to shave off that extra hour and you know how to write elegant and easy to read code without all that abstraction --go for it.

If you write a really bad program you could get 10% of the potential performance. But it's hard to tell without a little reading. With or without classes. Same with ADO. For example the other day I sped up a import job from 2 days to maybe an hoour or two by using one massive ugly TransactSQL stored procedure called via ADO command object instead of ADO adds/selects/updates. It need not be one massive stored procedure, it could be three. But that's not the point, we are just trying to walk around the limits of various technologies and use their strengths instead. The obvious solution was so slow, so we searched on the net for alternatives.

Li-fan Chen
Wednesday, February 5, 2003

*  Recent Topics

*  Fog Creek Home