In-memory Database in Excel

Question

Hello,

I am looking for a way to import a datatable from Access into an Excel variable and then run queries through this variable to speed up the process. I am trying to migrate from C# .NET where I read a data table from an access database into memory and then used LINQ to query this dataset. It is MUCH faster than how I have it currently coded in VBA where I must make lots of calls to the actual database, which is slow. I have seen the QueryTable mentioned, but it appears that this requires pasting the data into the 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 didn't need to use Excel+VBA to do this, but we're kind of stuck with that for now. Thanks for the help!

Answer 1

I don't know of anything like LINQ for VBA.

If you keep the ADO Connection option in scope by making it Public, you can Excecute Commands against it. It's not as fast as LINQ, but it's definitely faster than creating and destroying Connection objects for every call.

If the tables aren't too huge, I tend to read the tables into custom classes in VBA with the appropriate Parent/Child relationships set up. The very obvious downside to this is that you can't use SQL to get a recordset of data from your classes. I have to use a lot of looping when I need more than one specific record. And that means if you have 1m records, it would be quicker to call the database.

If you're interested in the last one, you can read some of the stuff I've written on it 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/ (read Rob Bruce's comment)

Answer 2

I would just read it into an ADO recordset, then get the data I need from the recordset as I need it. Of course this will depend on the size of the table you want to read.