lifeson
11-06-2008, 02:18 AM
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?
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?