{"id":24,"date":"2008-07-11T15:06:59","date_gmt":"2008-07-11T15:06:59","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=24"},"modified":"2020-04-01T12:58:36","modified_gmt":"2020-04-01T11:58:36","slug":"dlookup-using-vba-collections","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2008\/07\/dlookup-using-vba-collections\/","title":{"rendered":"Faster lookup using VBA Collections"},"content":{"rendered":"

Problem:<\/strong>
\nReading records from the database and instead of using DLookup or querying the database all the time, to improve performance we can read them into a VBA collection object and refer to it in memory. Especially, if the data in particular tables are not changing at all or changing very rarely, we can use this facility and hold the data in memory while the application is running. Beware of the number of records in your table and the amount of memory it may consume.<\/p>\n

First, a very simple table to hold our data, with an ID (primary key) field to store unique numbers and a simple character field to save alphanumeric data.<\/p>\n

\"\"<\/a><\/p>\n

Then, based on our table, a VBA class definition to store and manipulate data in memory.
\n\"\"<\/a><\/p>\n

\r\n'clsTable1\r\nOption Compare Database\r\nOption Explicit\r\n\r\n'definition same as Table1\r\nPublic id As Long\r\nPublic data As String\r\n\r\n'Flag to be set TRUE when loaded fine\r\nPublic isOK As Boolean\r\n\r\nPrivate Sub Class_Initialize()\r\n    isOK = False\r\n    id = 0\r\n    data = \"\"\r\nEnd Sub\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\"Initialization<\/a><\/p>\n
\"Getting<\/a><\/p>\n
\"Initialization<\/a><\/p>\n
\"Getting<\/a><\/p>\n
Here is the code of module modMain:<\/p>\n
\r\n'modMain\r\nOption Compare Database\r\nOption Explicit\r\n\r\n'simple collection\r\nPrivate myColl_simple As Collection\r\n\r\n'collection to hold records of Table1 by id's\r\nPrivate myColl As Collection\r\n\r\nFunction myColl_init() As Boolean\r\nOn Error GoTo myErr\r\n    Dim rs As DAO.Recordset, sKey As String, r As clsTable1\r\n    Set myColl = New Collection\r\n    Set rs = CurrentDb.OpenRecordset(\"table1\")\r\n    rs.MoveFirst\r\n    Do While Not rs.BOF And Not rs.EOF\r\n        Set r = New clsTable1\r\n        r.id = rs.Fields(\"id\").Value\r\n        r.data = rs.Fields(\"data\").Value\r\n        r.isOK = True\r\n        sKey = \"A\" & r.id\r\n        myColl.Add r, sKey\r\n        rs.MoveNext\r\n    Loop\r\n    rs.Close: Set rs = Nothing\r\nmyExit:\r\n    myColl_init = (myColl.Count > 0)\r\n    Exit Function\r\nmyErr:\r\n    Resume myExit\r\nEnd Function\r\n\r\nFunction myColl_get_simple(i As Long) As String\r\nOn Error GoTo myErr\r\n    Dim k As String, v As String, r As clsTable1\r\n    k = \"A\" & i 'key\r\n    Set r = myColl_simple.Item(k)\r\n    v = r.data\r\nmyExit:\r\n    myColl_get_simple = v\r\n    Exit Function\r\nmyErr:\r\n    v = \"\" 'return an unexpected value\r\n    Resume myExit\r\nEnd Function\r\n\r\nFunction myColl_get(i As Long) As clsTable1\r\nOn Error GoTo myErr\r\n    Dim k As String, r As clsTable1\r\n    k = \"A\" & i 'key\r\n    Set r = myColl.Item(k) 'Flag is fine already\r\nmyExit:\r\n    Set myColl_get = r\r\n    Exit Function\r\nmyErr:\r\n    Set r = New clsTable1 'return an object, flag False\r\n    Resume myExit\r\nEnd Function\r\n\r\nFunction myColl_init_simple() As Boolean\r\n    Dim i As Long, k As String, v As String, t As clsTable1\r\n    Set myColl_simple = New Collection\r\n    Randomize\r\n    For i = 1 To 26\r\n        v = Chr(64 + i) 'A .. Z\r\n        v = v & v & v  ' AAA .. ZZZ\r\n        Set t = New clsTable1\r\n        t.id = i\r\n        t.data = v\r\n        t.isOK = True\r\n        k = \"A\" & i ' key\r\n        myColl_simple.Add t, k\r\n    Next i\r\n    myColl_init_simple = (myColl_simple.Count > 0)\r\nEnd Function\r\n<\/pre>\n
\"Code<\/a><\/p>\n
\"Putting<\/a><\/p>\n
\"Putting<\/a><\/p>\n
\"Putting<\/a><\/p>\n
\"Putting<\/a><\/p>\n
Code of the form frmTest:<\/p>\n
\r\n'frmTest\r\nOption Compare Database\r\nOption Explicit\r\n\r\nPrivate Sub cmdGet_Click()\r\nDim id As Long, r As clsTable1\r\n    id = CLng(Nz(Me.txtID, 0))\r\n    If id > 0 Then\r\n        Set r = myColl_get(id)\r\n        If r.isOK Then\r\n            Me.txtData.Value = r.data\r\n        End If\r\n    End If\r\nEnd Sub\r\n\r\nPrivate Sub cmdGetSimple_Click()\r\nDim id As Long, v As String\r\n    id = CLng(Nz(Me.txtID, 0))\r\n    If id > 0 Then\r\n        v = myColl_get_simple(id)\r\n        If v <> \"\" Then\r\n            Me.txtData.Value = v\r\n        End If\r\n    End If\r\nEnd Sub\r\n\r\n\r\nPrivate Sub Form_Load()\r\n    If Not myColl_init_simple() Then\r\n        MsgBox \"Error loading records!\"\r\n    End If\r\n    If Not myColl_init() Then\r\n        MsgBox \"Error loading records!\"\r\n    End If\r\nEnd Sub\r\n<\/pre>\n
Feel free to download<\/a> the whole database.<\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: Reading records from the database and instead of using DLookup or querying the database all the time, to improve performance we can read them into a VBA collection object and refer to it in memory.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[15,17],"class_list":["post-24","post","type-post","status-publish","format-standard","hentry","category-technology","tag-access","tag-vba"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/24","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=24"}],"version-history":[{"count":3,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/24\/revisions"}],"predecessor-version":[{"id":986,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/24\/revisions\/986"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=24"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=24"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=24"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}