Use this code to cache your repeated calls to the database:
Dim mLookup As Scripting.DictionaryDim cn As ADODB.ConnectionEnum enumLookup ltCustomers ltVendors ltSalespeopleEnd EnumPrivate Sub Form_Load() Dim rstest As ADODB.Recordset Set cn = New ADODB.Connection cn.ConnectionString = "Provider=sqloledb;data source=myserver;initial catalog=mydb;integratedsecurity=sspi" cn.Open Set rstest = GetCache(ltCustomers)End SubPrivate Function GetCache(eLookupType As enumLookup, Optional bRebind As Boolean) AsADODB.Recordset Dim sSQL As String Dim rs As New ADODB.Recordset If mLookup Is Nothing Then Set mLookup = New Scripting.Dictionary End If 'Remove if necessary If bReplace Then If mLookup.Exists("L" & CStr(eLookupType)) Then mLookup.Remove ("L" & CStr(eLookupType)) End If End If Select Case eLookupType Case ltCustomers sSQL = "SELECT * FROM Customers" Case ltVendors sSQL = "SELECT * FROM Vendors" Case ltSalespeople sSQL = "SELECT * FROM Salespeople" End Select 'Cache the dataset If mLookup.Exists("L" & CStr(eLookupType)) Then Set GetCache = pLookupDictionary.Item("L" & CStr(eLookupType)) Else GetCache.Open sSQL, cn pLookupDictionary.Add "L" & CStr(eLookupType), GetCache End If Set rs = NothingEnd FunctionPrivate Sub Form_Unload(Cancel As Integer) cn.Close Set cn = Nothing Set mLookup = NothingEnd Sub
