If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Virtual Tables
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. |
#2
|
|||
|
|||
Virtual Tables
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 wrote: 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. |
#3
|
|||
|
|||
Virtual Tables
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. "Banana" wrote: 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 wrote: 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. |
#4
|
|||
|
|||
Virtual Tables
Shutterbug wrote:
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, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#5
|
|||
|
|||
Virtual Tables
Shutterbug wrote:
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 -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
Thread Tools | |
Display Modes | |
|
|