recordset and connection question...

[Sok]

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

[tanws]

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
>
>

[DeGojs]

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
>
>