Results 1 to 10 of 10
  1. #1
    patwwh is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4

    How to build "Virtual Table" in Access?


    I want to build a Virtual Table in Access, so that I can treat it as normal table, and apply DLookup() etc. on it. How to do it?
    N.B. Query is not a Virtual Table for me. It is just an action command, and re-load every time.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't believe it can be done - you can create a recordset in memory which can be filtered, sorted etc, but dlookup won't work on it. Dlookup needs a physical object (i.e. a table or query) to work with. Although I guess you could write your own

    Also, recordsets are not indexed (although I seem to recall reading somewhere there was a way to do it) so all processing is sequential which is significantly slower than indexed data, most notable on large datasets. So for a large dataset (say 100k records) a memory data search may be slower than an indexed disk based data search.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    patwwh is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Ajax, recordset have at least 2 modes. The 1st mode is to open it by just table name, but not a full sql string (2nd mode), then we can apply .index and .seek to utilize the existing table index. The thing I am not sure is: Will Access "in 1st mode" load whole table into memory by once (higher performance for batch actions), or just seek the physical table with the physical index every yime when I apply new .seek. If the answer is former, then I think I can declare the recordset as static. As a result, even though I cannot apply Dlookup etc on it directly, the performance can still be benefitial from its index in memory, better than using normal non-index array.

    Orange, my meaning of virtual table is a table stayed in memory, esp. used for achieving higher performance is batch actions. A concept somehow come from sql. Since its feature is similar to a physical, I can apply something like dlookup on it.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It sounds to me like your virtual table would be a named, saved query. As to how much of it resides in memory would seem to be related to how big is the table and the related indexes, how much memory you have and what else is going on at the same time.

    The internals of the database system (ACC) will take advantage of indexes. Some of the performance will be related to your coding.

    DLookup and other domain functions invoke routines that may be inefficient depending on where and how they are used.

    You may get more focused responses if readers knew more about what your transactions/processing logic involves.

    If performance is the issue, then you could do some testing with timers etc to set up your transactions such that production will be as optimized as much as is practical.

    Good luck. Let us know how things go.

  6. #6
    patwwh is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Sorry I haven't mentioned my usage and logic.
    I am just coding a mini-translator, to translate some technical terms, from one language to another one language based upon request, but the data table is storing several languages.
    So you can image: I will lookup each word from the content (from the content table) on the dictionary table, which need to be read-only.
    Thus, this process is related to 2 tables. On the side of dictionary table, since Lookup actions will be applied separately for many times (since it is for each word), a saved Query seems not to be a good choice for me. Otherwise, if the content has 100,000 words, it will read the physical table through the query for 100,000 times.

  7. #7
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Will Access "in 1st mode" load whole table into memory by once (higher performance for batch actions), or just seek the physical table with the physical index every time when I apply new .seek.
    If the table is too big to retain in memory then it will cache some of it to disk.

  8. #8
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    arent ALL access tables virtual?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    patwwh,

    Here is a link to a thread from a few years back. It was for a crossword helper program.
    The database (xword.mdb) in access2003 is still available for download. The dictionary contains almost 173000 words. You can check its performance before committing to any specific strategy.

    Good luck.

  10. #10
    patwwh is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Finally I have got time to finish the implementation.
    I keep using physical table to store my data, for easier maintenance, and storing multiple languages.
    During the process, the desire language-pair will be loaded into a static Dictionary object, which has intrinsic indexing, and support text-text translation directly.
    I drop using OpenRecordset, which actually just fetch one record under each request/loop under my testing.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  3. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  4. Replies: 3
    Last Post: 10-21-2010, 10:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums