Virtual Tables

[Shutterbug]

Is it possible to load a table into memory like an array but still use it
with field names? I have loaded tables into an array but I would like to do
so while still using the same field names that the table has. I saw where
SQL had something like virtual tables. I do not know if that is the same
thing. I'm using the Jet engine.

I have an application that downloads data from the ERP system and builds
order acknowledgements reports. There is not a lot of data in each table, 1
order with at most 20 order lines. If I could develop this with tables then
convert the tables all to memory maybe I could speed up the application.

 

[Banana]

With DAO, this isn't really possible; the DAO architecture involves a
disk I/O in some fashion.

However, it could be achieved with ADO recordsets where you create the
fields then insert the data into the recordset. Unforunately, the
manners of querying you can do upon a ADO recordset is very limited and
you can't even use SQL statement against the recordset, and I suspect
it's not that much better.

What is the table functions? If they are lookups, you can just make a
local copy so every users' front-end uses the local copy and thus never
bothers the server for the table's content and this will be quite fast
(I don't have any proof but I am fairly sure Jet does some kind of
caching to reduce disk I/O). You can then program something so you can
check for updates to lookup tables at the application's startup or do it
yourself manually.

HTH.

 

[Shutterbug]

I do build a lot of local tables like ShipVia, Terms, etc. And it is much
faster for us doing it this way. I also use FMS software to download new
versions to run on the local PC which is much faster than loading from the
network. I don't know if building tables in memory would increase my speed
much. My order acknowledgements take 60 queries to build and print. At
first it took 4 minutes to print, now I can do it all in less than 5 seconds.
I guess I'm just more curious about this than anything. It would be great
to just click on a table and define it as a memory table just like you would
define it as a hidden table.

Here at ROM we have over 75 Access applications running and I'm always
looking for new ways to do things.

 

[Tony Toews [MVP]]

Is it possible to load a table into memory like an array but still use it
with field names? I have loaded tables into an array but I would like to do
so while still using the same field names that the table has. I saw where
SQL had something like virtual tables. I do not know if that is the same
thing. I'm using the Jet engine.

I have an application that downloads data from the ERP system and builds
order acknowledgements reports. There is not a lot of data in each table, 1
order with at most 20 order lines. If I could develop this with tables then
convert the tables all to memory maybe I could speed up the application.

ADO has disconnected recordsets which might be what you want.

Otherwise see the TempTables.MDB page at my website which illustrates
how to use a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony

 

[Tony Toews [MVP]]

I don't know if building tables in memory would increase my speed
much. My order acknowledgements take 60 queries to build and print. At
first it took 4 minutes to print, now I can do it all in less than 5 seconds.

Sounds like you've encountered once of Access's problems with complex
reports.

I had a client report that took 60 seconds for a 1 page report. It
had some very complex criteria on the parent/child fields on several
subreports. But the base query ran very fast. By inserting those 5
or 100 records into a temp table that speeded up the report to about a
second.

One woman in the office said she loved me. I asked if she had a single
sister. <smile> Another woman said "Holy cr*p. What did you do? "
and other assorted comments when she ran the report for the first time
since it had been speeded up.

Tony