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.
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.
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.
patwwh,
Please give us more details on what you mean by Virtual Table in Access.
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.
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.
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.
If the table is too big to retain in memory then it will cache some of it to disk.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.
arent ALL access tables virtual?
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.
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.