In-memory database in Excel
I am looking for a way to import data from Access into an Excel variable and then run queries through that variable to speed things up. I'm trying to navigate from C # .NET where I read a data table from a memory access database and then used LINQ to query that dataset. This is much faster than the way I currently code it in VBA, where I have to make many calls to the actual database, which is slow. I saw the mention of QueryTable, but it seems to require inserting data into an excel sheet. I would like to keep everything in memory and minimize the interaction between the Excel sheet and the VBA code as much as possible.
I wish we had to use Excel + VBA to do this, but at this point we're kind of stuck. Thanks for the help!
a source to share
I don't know anything like LINQ to VBA.
If you keep the ADO Connection in scope by making it public, you can run Excecute Commands against it. It's not as fast as LINQ, but it's definitely faster than creating and destroying Connection objects for each call.
Unless the tables are too big, I tend to read tables in custom classes in VBA with parent and child relationship settings appropriately. A very obvious drawback is that you cannot use SQL to get a dataset from your classes. I have to use many loops when I need more than one specific entry. And that means that if you have 1m of records, it would be faster to call the database.
If you are interested in the latter, you can read some of the material I have written here. http://www.dailydoseofexcel.com/archives/2008/12/07/vba-framework/
http://www.dailydoseofexcel.com/archives/2008/11/15/creating-classes-from-access-tables/
http://www.dailydoseofexcel.com/archives/2007/12/28/terminating-dependent-classes/ (see comment by Rob Bruce)
a source to share