Maintaining a ADO connection

[lifeson]

I have a project that reads from a access database using thefollowing to open the connection and get the records

Sub GetTable(ws As Worksheet, filter As String)
Dim src As String
Dim DBFullName As String
Dim Cnct As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim col As Integer
Application.Cursor = xlWait
Application.ScreenUpdating = False

DBFullName = frmPassword.DBFullName

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset

With Recordset
' filter is passed from called function
src = filter
.Open Source:=src, ActiveConnection:=Connection
' Write the field names
ws.Cells.Clear

For col = 0 To Recordset.Fields.Count - 1
ws.Range("A1").Offset(0, col).Value = Recordset.Fields(col).Name
Next
' Write the recordset
ws.Range("A1").Offset(1, 0).CopyFromRecordset Recordset
Set Recordset = Nothing
End With

Connection.Close
Set Connection = Nothing
Application.Cursor = xlDefault

End Sub

This works great but can be called many times in a single routine :whistle:

In terms of efficiency, is there any benefit in coding the project so the connection is created and not closed each time it is called?

[Bob Phillips]

Absolutely, creating the connection is the most expensive action (assuming the database retrieve isn't huge), so you will get obvious benefits.