recordset and connection question...


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: recordset and connection question...

  1. #1
    Sok Guest

    recordset and connection question...

    I have a general question regarding opening and closing of Connections and
    Recordset.

    In all of my routines, I basically Open and Close Connections and
    Recordsets. In an attempt to cut down on any over head, I am asking the
    following question.

    If I declared a variable to a recordset or connection, Do I still have to
    use Set xxxx = New command to instantiate the variable.
    Am I declaring the variable 2wice?

    Private Sub OpenConnections()
    Dim cnn as ADODB.CONNECTION
    'I've declared the cnn as Connecition Object

    Dim rs as ADODB.RECORDSET
    'Iv'e declared the rs as Recordset Object

    Dim strConnectionString as String

    strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\MyNewDB.mdb;"


    Set cnnConnection = New ADODB.Connection
    'Here, Do I have to do this?

    Set rs=New ADODB.RECORDSET
    'Here, Do I have to do this?

    cnn.open strConnectionString
    rs.open,"tbTable",cnn,,,adcmdtable

    rs.close
    'Here, is this closing the instantiation from Set rs=new... or Dim rs as
    Adodb.Recordset


    cnn.close
    set rs=nothing
    set cnn=nothing

    End Sub

    My code above works. However, I have similar codes running on all of my
    modules. If I can be efficient in this code, then I can be efficient
    overall.
    Any help would be appreciated....

    Sok



  2. #2
    tanws Guest

    Re: recordset and connection question...


    u not need to repeat the same code again n again,
    in MODULE,
    Public cnn As New ADODB.Connection
    Sub Main()
    cnn.Provider = "microsoft.jet.oledb.4.0"
    cnn.CursorLocation = adUseClient
    cnn.Open "\data\test.mdb"
    end sub

    in Form or Module

    Dim rsTemp As New ADODB.Recordset
    SQLStr = "SELECT * FROM SMSysNO WHERE SYSCode= '" & SYSCODE & "' AND rsTemp.Open
    SQLStr, adconn, adOpenKeyset, adLockOptimistic
    If rsTemp.RecordCount Then
    Msgbox rsTemp!SysPreFix
    endif
    rsTemp.close
    set rsTemp = Nothing

    cnn.Close
    set cnn = Nothing

    ok. hope can help u.
    from Tanws

    "Sok" <controlx@kornet.net> wrote:
    >I have a general question regarding opening and closing of Connections and
    >Recordset.
    >
    >In all of my routines, I basically Open and Close Connections and
    >Recordsets. In an attempt to cut down on any over head, I am asking the
    >following question.
    >
    >If I declared a variable to a recordset or connection, Do I still have to
    >use Set xxxx = New command to instantiate the variable.
    >Am I declaring the variable 2wice?
    >
    >Private Sub OpenConnections()
    > Dim cnn as ADODB.CONNECTION
    > 'I've declared the cnn as Connecition Object
    >
    > Dim rs as ADODB.RECORDSET
    > 'Iv'e declared the rs as Recordset Object
    >
    > Dim strConnectionString as String
    >
    > strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=C:\MyNewDB.mdb;"
    >
    >
    > Set cnnConnection = New ADODB.Connection
    > 'Here, Do I have to do this?
    >
    > Set rs=New ADODB.RECORDSET
    > 'Here, Do I have to do this?
    >
    > cnn.open strConnectionString
    > rs.open,"tbTable",cnn,,,adcmdtable
    >
    > rs.close
    > 'Here, is this closing the instantiation from Set rs=new... or Dim rs

    as
    >Adodb.Recordset
    >
    >
    > cnn.close
    > set rs=nothing
    > set cnn=nothing
    >
    >End Sub
    >
    >My code above works. However, I have similar codes running on all of my
    >modules. If I can be efficient in this code, then I can be efficient
    >overall.
    >Any help would be appreciated....
    >
    >Sok
    >
    >



  3. #3
    DeGojs Guest

    Re: recordset and connection question...

    You have two choices:

    1) Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    2) Dim rs As New ADODB.Recordset

    Basiclly it doesn't matter which one you use (although there's the
    difference, but that's another LONG story..)

    As I can see form your code, you're doing just fine.



    "Sok" <controlx@kornet.net> wrote in message news:3a821561@news.devx.com...
    > I have a general question regarding opening and closing of Connections and
    > Recordset.
    >
    > In all of my routines, I basically Open and Close Connections and
    > Recordsets. In an attempt to cut down on any over head, I am asking the
    > following question.
    >
    > If I declared a variable to a recordset or connection, Do I still have to
    > use Set xxxx = New command to instantiate the variable.
    > Am I declaring the variable 2wice?
    >
    > Private Sub OpenConnections()
    > Dim cnn as ADODB.CONNECTION
    > 'I've declared the cnn as Connecition Object
    >
    > Dim rs as ADODB.RECORDSET
    > 'Iv'e declared the rs as Recordset Object
    >
    > Dim strConnectionString as String
    >
    > strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=C:\MyNewDB.mdb;"
    >
    >
    > Set cnnConnection = New ADODB.Connection
    > 'Here, Do I have to do this?


    YES !
    >
    > Set rs=New ADODB.RECORDSET
    > 'Here, Do I have to do this?


    YES !
    >
    > cnn.open strConnectionString
    > rs.open,"tbTable",cnn,,,adcmdtable
    >
    > rs.close
    > 'Here, is this closing the instantiation from Set rs=new... or Dim rs

    as
    > Adodb.Recordset
    >
    >
    > cnn.close
    > set rs=nothing
    > set cnn=nothing
    >
    > End Sub
    >
    > My code above works. However, I have similar codes running on all of my
    > modules. If I can be efficient in this code, then I can be efficient
    > overall.
    > Any help would be appreciated....
    >
    > Sok
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center