Unclear on Reuse of Connection & Recordset Objects 
Author Message
 Unclear on Reuse of Connection & Recordset Objects

I've read much on the topic of the advantages of recordset and connection
object reuse and I must tell you I'm a little unclear on how to implement
it. Up to now I've used a createobject("adoce.recordset.3.1") everytime I
need to access the database (Chris T., I know your frowning :)

I've decided to take this week and try to change my existing code to reuse
the objects as much as possible. I'm clear that for every connection to a
database I need a seperate Public connection object in a module. I'm dealing
with only two databases right now so I have the following in the
declarations section of a module

Public cn as adoce.connection 'Clinical Database
Public scn as adoce.connection 'Support database

Now's where I become confused...I do recordset manipulation both in other
modules and within form modules. Where is the rs variable declared and the
set rs=createobject("adoce.recordset.3.1") code placed? Are they both done
on the form load event and if thats true what about recordset manipulation
within seperate modules. My current code usually goes as follows:

  dim rs as adoce.recordset  dim SQL as string  set rs =
createobject("adoce.recordset.3.1")    SQL = SELECT * from Whatever
rs.open SQL, dbpath, adOpenKeySet, adLockOptomistic    'Manipulate the
recordset    rs.close  set rs = nothing

I know this is not efficient (but it works). Before I go further I would
like to understand the resuse of the recordset and connection objects better

Any advice would be appreciated

Respectfully,

_____________________________

John D Kostenbader
Med-Media, Inc.



Sat, 16 Oct 2004 00:10:11 GMT  
 Unclear on Reuse of Connection & Recordset Objects
What I typically do for situations where only one object (like a recordset)
will be used is to declare a Global like this:

Public m_rs As ADOCE.Recordset

I then use an accessor method like this:

Public Function GetRecordset() As ADOCE.Recordset
  If IsEmpty(m_rs) Then
    Set m_rs = CreateObject("ADOCE.Recordset")
  end if

  Set GetRecordset = m_rs
End Function

Now any time you need a Recordset, you do something like this:

Sub MySub
  Dim rs As ADOCE.Recordset

  Set rs = GetRecordset()

  rs.Open .....
  .....
  rs.Close
End Sub

This way CreateObject is called only 1 time for the object during the life
of the app.

-Chris



Quote:
> I've read much on the topic of the advantages of recordset and connection
> object reuse and I must tell you I'm a little unclear on how to implement
> it. Up to now I've used a createobject("adoce.recordset.3.1") everytime I
> need to access the database (Chris T., I know your frowning :)

> I've decided to take this week and try to change my existing code to reuse
> the objects as much as possible. I'm clear that for every connection to a
> database I need a seperate Public connection object in a module. I'm
dealing
> with only two databases right now so I have the following in the
> declarations section of a module

> Public cn as adoce.connection 'Clinical Database
> Public scn as adoce.connection 'Support database

> Now's where I become confused...I do recordset manipulation both in other
> modules and within form modules. Where is the rs variable declared and the
> set rs=createobject("adoce.recordset.3.1") code placed? Are they both done
> on the form load event and if thats true what about recordset manipulation
> within seperate modules. My current code usually goes as follows:

>   dim rs as adoce.recordset  dim SQL as string  set rs =
> createobject("adoce.recordset.3.1")    SQL = SELECT * from Whatever
> rs.open SQL, dbpath, adOpenKeySet, adLockOptomistic    'Manipulate the
> recordset    rs.close  set rs = nothing

> I know this is not efficient (but it works). Before I go further I would
> like to understand the resuse of the recordset and connection objects
better

> Any advice would be appreciated

> Respectfully,

> _____________________________

> John D Kostenbader
> Med-Media, Inc.



Sat, 16 Oct 2004 00:25:32 GMT  
 Unclear on Reuse of Connection & Recordset Objects
Thanks very much Chris (as always), I'm changing everything over as we
speak!!!

Respectfully,

John D Kostenbader
Med-Media, Inc.



Quote:
> What I typically do for situations where only one object (like a
recordset)
> will be used is to declare a Global like this:

> Public m_rs As ADOCE.Recordset

> I then use an accessor method like this:

> Public Function GetRecordset() As ADOCE.Recordset
>   If IsEmpty(m_rs) Then
>     Set m_rs = CreateObject("ADOCE.Recordset")
>   end if

>   Set GetRecordset = m_rs
> End Function

> Now any time you need a Recordset, you do something like this:

> Sub MySub
>   Dim rs As ADOCE.Recordset

>   Set rs = GetRecordset()

>   rs.Open .....
>   .....
>   rs.Close
> End Sub

> This way CreateObject is called only 1 time for the object during the life
> of the app.

> -Chris



> > I've read much on the topic of the advantages of recordset and
connection
> > object reuse and I must tell you I'm a little unclear on how to
implement
> > it. Up to now I've used a createobject("adoce.recordset.3.1") everytime
I
> > need to access the database (Chris T., I know your frowning :)

> > I've decided to take this week and try to change my existing code to
reuse
> > the objects as much as possible. I'm clear that for every connection to
a
> > database I need a seperate Public connection object in a module. I'm
> dealing
> > with only two databases right now so I have the following in the
> > declarations section of a module

> > Public cn as adoce.connection 'Clinical Database
> > Public scn as adoce.connection 'Support database

> > Now's where I become confused...I do recordset manipulation both in
other
> > modules and within form modules. Where is the rs variable declared and
the
> > set rs=createobject("adoce.recordset.3.1") code placed? Are they both
done
> > on the form load event and if thats true what about recordset
manipulation
> > within seperate modules. My current code usually goes as follows:

> >   dim rs as adoce.recordset  dim SQL as string  set rs =
> > createobject("adoce.recordset.3.1")    SQL = SELECT * from Whatever
> > rs.open SQL, dbpath, adOpenKeySet, adLockOptomistic    'Manipulate the
> > recordset    rs.close  set rs = nothing

> > I know this is not efficient (but it works). Before I go further I would
> > like to understand the resuse of the recordset and connection objects
> better

> > Any advice would be appreciated

> > Respectfully,

> > _____________________________

> > John D Kostenbader
> > Med-Media, Inc.



Sat, 16 Oct 2004 02:39:32 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Unable to reuse ADO.Recordset connection using VBA script in Excel

2. Reuse application-level connection object

3. Reusing Connection objects with MSDAIPP

4. Problem reusing ADO.Recordset object in VBA script in Excel

5. Recordset reuse/Rebind Controls Versus Many Recordsets/Binding once

6. opening connection object, recordset object

7. Reusing ADO connections in HTA applications

8. ADO Connection Reuse

9. Question re: reusing the same ADODB.Connection in my VB app

10. Connection reuse

11. Connection Reuse

12. ADO Recordset Cursor Types & Connections Question

 

 
Powered by phpBB® Forum Software