Solved: ADO Connection Question

[CreganTur]

I've got a best practice question regarding ADO connections when working with ADODB.Recordsets.

I've got 2 procedures that accomplish the same thing. The only difference is that one of them opens an ADODB.Connection, and then the Recordset, while the second procedure opens just the Recordset.

This version opens a Connection, and then opens the Recordset
Sub RecSetOpen2()
'exports entire recordset into .dat file
Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Acc07_ByExample\Northwind.mdb"

Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM Customers", _
conn, adOpenForwardOnly
.Save "C:\Acc07_ByExample\MyRst.dat"
.Close
End With
Set rst = Nothing

End Sub

This version opens just the Recordset:
Sub RecSetOpen()
'exports entire recordset into .dat file
Dim rst As ADODB.Recordset
Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Acc07_ByExample\Northwind.mdb"

Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM Customers", _
strConnection, adOpenForwardOnly
.Save "C:\Acc07_ByExample\MyRst.dat"
.Close
End With
Set rst = Nothing

End Sub

Is there any benefit to opening a Connection, as shown in the first procedure? To me it seems like extraneous code, since the exact same thing can be accomplished by just opening the recordset directly, as shown in the second procedure.

This may be one of those 'personal choice' issues, but I do want to know what best practice would be.

I've cross posted this issue here: http://www.proofficedev.com/members/forums/showthread.php?p=5428#post5428

[stanl]

Your question may be a moot point if you are just using Access, but assume you are writing code to interogate linked files [Oracle, SQL Server].

ADO operates under 'implicit' referencing (for lack of a more definitive phrase).

Opening just a recordset opens an implicit connection; opening just a connection then using connection.execute() opens an implicit recordset.

Where it comes to bite you in the arse - certain properties can only be set from explicit connections - the most important being the connectiontimeout and the commandtimeout.

You can open a recordset, say

oRS = CreateObject("ADODB.Recordset")

then issue

oRS.ActiveConnection.CommandTimeout = 600

however, you cannot issue

oRS.ActiveConnection.ConnectionTimeout = 0 'unlimited

So assuming you process a procedure or large SQL statement that takes several minutes to complete. If you just open a recordset object, regardless of the commandtimeout, you can still get an error - SQL Connection Timeout.

Trust me, I learned this the hard way with SQL Server.

.02 Stan

[Dr.K]

I totally agree, I use a specific Connection Object that I explicitly create and destroy every time.

As mentioned above, you can do a lot more with an explicit Connection Object then an implicit one. A big one for me is the ability to set up a persistent security schema when attaching to my DB2 Data Mart. This makes the SQL easier to read, and less typing.

For example, normally I would need to prefix every single Field and Table with my schema: ie instead of: "MSECURTY.MSE_FUND_NAME", I would have to use "DB2S4.MSECURTY.MSE_FUND_NAME".

I make my connection private module level, and I use a Sub to open it. Here is an example:


Private Const dmcUser As String = "Login ID Goes here"
Private Const dmcPW As String = "Password Goes here"
Private Const dmcSchema As String = "Schema Prefix Goes here"

Private strSQL As String
Private conDM As ADODB.Connection



Private Sub OpenDMConn()

Dim objCommand As New ADODB.Command
Set conDM = New ADODB.Connection

strSQL = "Password=" & dmcPW _
& ";User ID=" & dmcUser _
& ";Data Source=DCMP;Persist Security Info=True"

With conDM
.ConnectionTimeout = 1900
.CommandTimeout = 1900
.Open strSQL
End With

With objCommand
.ActiveConnection = conDM
.CommandType = adCmdText
.Prepared = True
.CommandText = "set schema " & dmcSchema
.Execute
End With

Set objCommand = Nothing
End Sub


Also, I recall reading an MSDN article on the subject that said that it creates a NEW implicit Connection each time you do it that way, and VBA is not always able to remove the Object from memory until execution ceases completely. This totally clutters up your memory, so, as always, proper explicit Object creation and Releasing consumes less resources, and runs faster.