Advice? Excel "Database" vs ADO connection?

[ClimoC]

Hello,

so kind of two questions:

I have a 'data' sheet (table) hidden, and to optimise speed at run-time I load the thing in an array...

Global RecDict As Object, Database()  As Variant,

Sub Startup()

Dim RecStore As Range

Database() = Sheets("Data").Range("A2:Z" & Sheets("Data").Range("A65000").End(xlUp).Row).Value
Set RecDict = CreateObject("Scripting.Dictionary")

For Each RecStore In Sheets("Data").Range("A2:A" & Sheets("Data").Range("A65000").End(xlUp).Row)
    If Not RecDict.exists(RecStore.Value) Then RecDict.Add RecStore.Value, RecStore.Row
Next

...as well as having a bunch of Global Bytes for 'Finding' the column headers for use in reading/writing to the DB.

But considering we estimate this thing to get to 30/40,000+ rows in time at it's greatest, I thought that loading this all into memory every time we run a procedure that looks up values from the table, would just become tedious (and obviously, the reason you usually do anything in VBA is to save time and hassle...)

So my first question is probably a REALLY stupid one...
1. Is there a way to load a 'Variant Array' like mine into memory, and it's just permanently there for all future procedures to just go straight to the read/writing without having to load it each time?

2. Is it FASTER (for future proofing), and ideally, provides the kind of 'live' and 'ready-access' functionality described about, To do this all through ADO with a little .mdb file created? And by that I mean without 'Access' and servers, I mean write out the 'Data' sheet to an .mdb file when you first open the workbook to the C:\ and link into that instead.

Any advice on these would be appreciated. I don't want to be asked to rewrite the code in 6 months time because it takes 30 seconds to do ANY of the whizzy functions I'm building in to the UI side of it.

Thanks
C

 

[Norie]

How about using a database for storage of all the data bytes?

Could be any database and you can use ADO with most of them.

Or you could go the whole hog and do the whole thing in a database - some of them do have nice UI facilities.

 

[Andrew Poulsom]

This may interest you:

http://msmvps.com/blogs/xldynamic/archive/2010/10/10/vba-has-no-class.aspx

 

[ClimoC]

This may interest you:

http://msmvps.com/blogs/xldynamic/archive/2010/10/10/vba-has-no-class.aspx

Ok, I gave this a go, and it seems to create it all fine.

But... (again, probably a stupid question)... I can't seem to get anything 'out' of it? And is there a way of knowing it is still loaded in memory when the sub finishes?

I did this all in a standard Module, not sure if that's right or not...

Do I have to do this all in a form or something? The users are going to need to be able to do things in one of the output sheet inbetween searching, pulling, reading/writing to the DB.

 

[ClimoC]

...To clarify, I basically want to open the workbook, have it load the 'Data' sheet table/DB into a recordset/database, and then as I'm la-la-la-ing around in the worksheet doing various things, if I use my modal and non-modal forms and ActiveX Command buttons etc, I can run individual subs and functions that (more than likely) will be calling back and writing to the DB I initialised when the Workbook first opened...

If I do as Bob says, run it, store it all, and then run a new function or sub to extract a record (READ), all I get it 'object required' - meaning Its not available across subs which is sort of what I need...

 

[ClimoC]

How about using a database for storage of all the data bytes?

Could be any database and you can use ADO with most of them.

Or you could go the whole hog and do the whole thing in a database - some of them do have nice UI facilities.

And just to respond in kind to you Norie, It's the same old "We use excel, we've always used excel... blah blah"... Unimaginative folk'll in resource scheduling.

I've taken their manual "Lets merge some cells in what look like random patterns, colour them, and call that a forward plan" excel sheet, and given them restrictive entry, stricter data validation, a database behind it all, and lots of whizzy conditional formatting and time-centric calculators, with floating non-modal forms for 'More Info'-type tabs etc etc etc. All so that all the work they're doing can actually be used as a DB further down the track by other departments. A total manhour saving across the business of about 250hrs per week...!

All this, in what is essentially the frontline of data input and scheduling/planning departments.

 

[xenou]

A lot depends (it seems to me) on what you mean by "doing various things". Also, the article doesn't cover writing additions, deletions, and changes back to the "database" - this is something else to think about here.

Your original routine, by the way, would be faster if instead of looping the range you looped your array in memory. I would also only load the really used range into the array, not the entire worksheet.

Be careful - if you store data in memory it will be lost if VBA crashes at any time, so you could lose information if it hasn't been committed to disk storage. I think there are some other cases of when/how state is lost.


------
BTW, interesting article Andrew. To add to it:
http://www.dailydoseofexcel.com/archives/2010/12/16/disconnected-recordsets-in-vba-classes/
http://www.4guysfromrolla.com/webtech/080101-1.shtml

 

[ClimoC]

Thanks xenou. I've got my answer about why I couldn't call from it...

I didn't make any of it Public *D'Oh!*

I've found plenty of articles now on Disconnected Recordsets, so I think I can find my way around the adding and updating side of things. And really, I need to now test the actual time it would take to do 30,000 rows of a sheet (which I should point out I believe my original is doing, I'm just so used to using "...A65000).end(xlup).row" I can't shake it :P )

I've never had any problems with VBA crashing... only really with the dreaded mousewheel whilst the pointer is over an ActiveX listbox (which I don't use anymore these days)

 

[RoryA]

If you have that much data, I'd store it in a database (even if that's just a separate Excel file, it would be better IMO) especially if you need more than one person accessing it at a time. On the other hand, that is an upgrade that can be done as and when required.

 

[Norie]

ClimoC

I apologise if I said something out of turn.

I kind of realised that you probably had some sort of situation as you describe - been there a few times.

I'd still recommend using a database, it just doesn't make sense to me to load an array with data that you already have.

Perhaps it's just me but it's seems a bit like storing the data twice, on the worksheet and in the array.