Cache Your Repeated Calls to the Database

Cache Your Repeated Calls to the Database

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


Share the Post: