ADO Connection

  • Thread starter Thread starter Vayse
  • Start date Start date
V

Vayse

Hi
Just wondering what way people open their ADO recordsets.
Is one method better than another?

1) Dim an ADO connection

Dim cnnADO As ADODB.Connection
Dim rsSchedule As New ADODB.Recordset

Set cnnADO = CurrentProject.Connection

stSQL = "SELECT ScheduleID, ScheculeName FROM Schedule_Reports"
rsSchedule.Open stSQL, cnnADO, adOpenDynamic, adLockOptimistic
' Do stuff
rsSchedule.Close
cnnADO.Close

2) Use CurrentProject.Connection

Dim rsSchedule As New ADODB.Recordset

stSQL = "SELECT ScheduleID, ScheculeName FROM Schedule_Reports"
rsSchedule.Open stSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
' Do stuff
rsSchedule.Close


I've been using method 1 for as long as I can remember, but now I'm starting
to wonder why.
Btw, in method 2,I don't close CurrentProject.Connection. I think I heard
somewhere before that one should leave this connection open?
Anyway, even if I try
CurrentProject.Connection.Close
the currentproject.Connection.State will stay at 1.

Thanks
Vayse
 
Methods are identical: in both cases, you are using the same connection
object; with the distinction that in the first case, you are setting a new
reference to the object into a local variable. However, if you weren't
using the SET keyword before the assignment, then instead of reusing the
same object you would create a new one using the connection string provided
by CurrentProject.Connection.

ADP opens three connection objects (and sometimes a fourth). The first one
is used to display the list of objects in the database window and is the
same provided with CurrentProject.Connection; so if you want to use with a
transaction, you shouldn't have any problem of conflict with the retrieval
or saving of datas with bound forms. However, when dealing with
transactions, people often prefer to open a brand new connection object in
order to be sure to have no conflict. Excerpt if you want to connect to
another database, don't close the CurrentProject.Connection object or an
object set to it using the SET operator.

Using the line « Dim rsSchedule As New ADODB.Recordset » is not the best
thing and should be avoided with complexe objects such as those provided by
ADO. Instead, use the following syntaxe:

Dim rsSchedule As ADODB.Recordset
Set rsSchedule = New ADODB.Recordset

If I remember correctly, this is an official recommandation by MS in order
to avoid (performance?) problems with the WITH operator or with little
things like Recordset.Clone.
 
Thanks Sylvain, great answer.
Vayse

Sylvain Lafontaine said:
Methods are identical: in both cases, you are using the same connection
object; with the distinction that in the first case, you are setting a new
reference to the object into a local variable. However, if you weren't
using the SET keyword before the assignment, then instead of reusing the
same object you would create a new one using the connection string
provided by CurrentProject.Connection.

ADP opens three connection objects (and sometimes a fourth). The first
one is used to display the list of objects in the database window and is
the same provided with CurrentProject.Connection; so if you want to use
with a transaction, you shouldn't have any problem of conflict with the
retrieval or saving of datas with bound forms. However, when dealing with
transactions, people often prefer to open a brand new connection object
in order to be sure to have no conflict. Excerpt if you want to connect
to another database, don't close the CurrentProject.Connection object or
an object set to it using the SET operator.

Using the line « Dim rsSchedule As New ADODB.Recordset » is not the best
thing and should be avoided with complexe objects such as those provided
by ADO. Instead, use the following syntaxe:

Dim rsSchedule As ADODB.Recordset
Set rsSchedule = New ADODB.Recordset

If I remember correctly, this is an official recommandation by MS in order
to avoid (performance?) problems with the WITH operator or with little
things like Recordset.Clone.
Click to expand...
 
Back
Top