How to build "Virtual Table" in Access?

_{Thursday, February 4, 2016 7:21 AM}

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.

_{Friday, February 5, 2016 4:33 PM ✅Answered}

If normal recordset opened by tablename will load the whole table into memory (Before I can test it, or someone can confirm me specially on this point, I doubt that it is just a temporary/programmatic query, not generate table in memory)

I don't believe it is the case that a table-type recordset is always loaded immediately into memory.  I could be wrong, but if I were you I would test it before relying on that behavior.  I think a recordset will load some initial number of records immediately and load the rest as they are required or on execution of a .MoveLast, regardless of recordset type.

How big is your table?  If you're always going to be performing lookups on a key value, have you considered using a Collection object or a Scripting.Dictionary object?

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Friday, February 5, 2016 8:23 PM ✅Answered}

Hi Dirk Goldgar, Scripting.Dictionary is surely my beloved one. There were 3 reasons I didn't adopt it in this case:

1.One dictionary can facilitate for translating from Language A to Language B, but not vice versa, and no good for my case which need translating among several language.

2.Not as good as table, in terms of data maintenance.

3.Storing data source in table can be used by both VBA and Queries.

.....Wait....Your suggestion has pushed me into a reflection ......

If I accept loading a physical table into memory for just reading (e.g. by recordset), why can't I load it into Static Dictionary object (since I just translate from one language to another language in each time)?? Dictionary is a promising indexed object, different from the Array I hate.

I quite believe you have solved my problem. I even don't need to do testing any more.

Thank you and all people's help here!!

_{Thursday, February 4, 2016 9:52 AM}

I think there is no such object as a "virtual table" in Access. If the problem is a slow connection to the server, the only option may be to first copy the data from the server to a local table, do the work on it, and then write the data back to the server. An alternative may be the Recordset.GetRows method, but this returns a two-dimensional array and you cannot use DLookup etc. directly on it.

Matthias Kläy, Kläy Computing AG

_{Thursday, February 4, 2016 3:29 PM}

you can make a table using a MakeTable query

as MK points out there is no such thing formally as 'virtual table' so you need to define what is its purpose...

in many cases I need a scratch pad type table.... I make these permanently in the front end only whenever all the fields are known...and then append data to them and clear data from them as needed to perform the needed task...

if you don't know the fields then you must do a MakeTable query on the fly.....

_{Thursday, February 4, 2016 3:55 PM}

Hi pat,

Access can only accommodate temporary tables or temporary databases.

_{Thursday, February 4, 2016 5:11 PM}

Thanks All for your confirmation. Knowing a "NO" answer is still better than struggling :)

What I care is the performance and easier maintenance of data. so for easier maintenance, I prefer storing data in a table. For performance, I don't select array due to its lack of built-in index. To combine both, I prefer a Virtual table in memory, supporting index. N.B. Normally I just need to frequently read and match data during process, not write.

Matthias mentioned about Recordset. If I open Recordset by the method of "Tablename" (instead of full sql query), so that I can apply .index and .seek, will Acces load whole data and index into memory by once? Or will it just seek physical table again and again whenever I apply new .seek? If memory is used, it will be a good alternative solution for me, since it can fulfill my purposes, even though I cannot apply standard tools like DLookup on it.

_{Thursday, February 4, 2016 5:39 PM}

Hi. Don't quote me on this because I don't have a reference handy, but I believe a recordset reads all qualifying records into memory at once. However, the "connection" to the table is still active, which means it's possible back and forth reads could be happening as you use the recordset. One possibility you could try is an ADO disconnected recordset. With it, I think you can do whatever you want with the recordset and not affect the table. Just my 2 cents...

_{Thursday, February 4, 2016 8:56 PM}

Keep in mind that manipulating recordsets to get a dataset rarely outperforms a query designed to do the same thing.  Oftentimes creating a temporary table (or even a temporary table in a temporary database as dbGuy has noted) using the output of query can give a boost in performance if that same query needs to be utilized multiple times in the completion of some task.  The downside is that a temporary table is a snapshot of your data at a singular point in time and thus does not reflect changes made to underlying data once it has been created.  A query based on the same underlying data is constantly monitoring for changes made to that underlying data.  Creating a temporary table eliminates that overhead and can boost performance.  You can also create DAO or ADO recordsets which are static copies of your data and which can be disconnected from the underlying tables but in general they will be much slower to get a final dataset from.

-Bruce

_{Friday, February 5, 2016 6:56 AM}

Sorry I think I should mention 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. Therefore, I guess it should be nothing related to temporary table.

If normal recordset opened by tablename will load the whole table into memory (Before I can test it, or someone can confirm me specially on this point, I doubt that it is just a temporary/programmatic query, not generate table in memory), then I am also considering to declare it as static. Thanks DB Guy & Bruce for introducing me ADO disconnected recordset. It is the 1st time I listen it. It seems to be a promising solution for me. I will try to test and compare these 2 solutions. (Just curious in term of what Bruce said "much slower": Using Recordset, or using Disconnected recordset?)

I get clearer testing and solving direction now. :)

_{Friday, February 5, 2016 7:26 PM}

in following this thread I question as to whether "in memory" coding methodology is even a germane topic for a PC database.  To my eye it is a topic only relevant to main frame sized data sets whereby one seeks an efficient method to work with records sets (....and for which there is a robust set of commercial vendors).