Using SQL on a worksheet vs. AutoFilter

I'm preparing to develop a worksheet that is essentially a holding area for rows of invoicing data not yet released for billing.
(Previously, all our billing was pay-as-you-go. Now we have some projects where billing can only happen
after the project is signed off. So anyone billing to that project has their invoicing data held up until it's released)

Knowing Excel supported SQL in a basic form, I guessed the best way to do this would be to pipe rows that qualify
for holding over to a single table in a separate workbook. That way I could use SQL to pull records released for billing,
delete them from the "database," and also add new records without the hassle of doing all the usual range
variable calculations (lastrow, etc).

I am already familiar with AdvancedFilter techniques in VBA for doing the "query" aspect, and I can easily
write code to manage adding and deleting rows in that table.

This holding area will likely never exceed a thousand rows of data (yes, I know you've heard that one before).

My Question is this:
I was just curious if any of you have enough experience in SQL versus AdvancedFilter to help me
decide which is the better way to go: SQL using an Excel table OR AdvancedFilter plus row management?

(I tried searching the web and the forum first, but I'm not getting much help with this topic, other than this item
at stackoverflow: http://stackoverflow.com/questions/7...cel-autofilter which is helpful, but
not complete.)

I know this is not a very specific question, but I hope it has a fairly specific answer.

Thanks!

Originally Posted by Mad-Mizer

My opinion, which you can take however you want, would be to write SQL instead of using the advanced filter. I say that because if you are pulling from a database to begin with, you might as well organize the data exactly how you want it before it comes into Excel. To do it in Excel with Advanced Filter seems to be an extra step in my opinion.

However, Excel would probably require less code writing (I'm guessing) so that may be the trade off.

My 2 bucks.

Thanks, Mad-Mizer

I'm guessing that's correct. Looks like I can just setup a connection to the "database" and do a few simple database operations to get all the functionality I need.

I'll leave this "unresolved" for a little while to see if anyone else weighs in.

(I'm wondering about potential gotchas involved. Especially since this storage table is like a queue, and not a permanent data collection.)

Originally Posted by buran

Hi,
I would go for SQL as you can query different workbook even if it is closed, while for the advanced filter you will have to open the DB workbook. Note that you cannot delete records with ADO when using with excel workbook. you can update records, so you must have a flag field, instead of deleting rows.
If you are going to implement UI for insert and update/delete of records in the DB workbook, why don't you go a step further and use MySQL or sqlite?

Well there's a gotcha, for sure. Thanks!

So if I understand you, using ADO I can ADD records, pull records based on query parameters, but not delete records.
Thus your recommendation is I'd flag the rows for deletion and do that in a maintenance cycle, right?
That doesn't seem too painful. (As long as I don't forget to do the maintenance or write some post-processing code
to do it after I'm done for the day.)

I'm certain a move to a database is in our future, but for this one table seemed like overkill. I'll probably migrate the entire system to MySQL, and just use Excel for reports. But that's something I hope to tie into a web-based solution. Right now it's all incoming spreadsheets. Very messy.

Thanks, buran!

Good call on the Workbook_BeforeClose event.

I guess I'll go ahead and code it up as an ADO connected db, and see how I like it. It's a step toward pulling the same data from the server when we get the web app running, anyway, so may as well roll up my sleeves now.

Unless you can think of any other "gotchas" or pointers I'll mark this solved in an hour or so.

Thanks!

No problem! Thanks! I run several versions of excel here. (I do my dev work in Excel 2000, but the actual system runs on Excel 2010)