BEST PRACTICES -> SQL with DAO

[Irish_Griffin]

Hi guys,
I've discovered excel can do SQL !!! It feels like I'm the primitive caveman discovering fire

Well lets not talk about how much time I've wasted writing code that would have been soooooooo much easier in SQL .... Instead, lets talk about BEST PRACTICES using SQL in EXCEL

I would like to make this thread a good reference for others ( not much online surprisingly ). I'll make this a sweet thread if I can get the guru's help.

So first and foremost:
#1 - Is DAO the right choice ?

Remember to take learning one bite at a time:

 

[Norie]

I notice loop is being used to go through the recordset.

That isn't particular efficient way to get data.

If you are working with Excel you can use one line of code to copy the data from the recordset to a worksheet.

Worksheets("Main").Range("A1").CopyFromRecordSet RS

Also, and this is an Excel thing, you don't need to select a range to work with it.

For example to clear the columns A:F on the worksheet Main.

Worksheets("Main").Range("A:F").ClearContents.

As for ADO there are other methods of returning a complete recordset or multiple records from a recordset, I'm specifically thinking of GetRows.

 

[Blade Hunter]

Thanks Norie, I didn't even think of bringing the recordset in in one go. There is only one case where I can think that I would still need to do it line by line and this is it:

    Do While Not RS.EOF
        Row = Row + 1
        For Findex = 0 To RS.Fields.Count - 1
          Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
        Next Findex
        RS.MoveNext
        If Row Mod 20 = 0 Then
           Application.Calculate
        End If
    Loop

Reason is there are some charts that hang off the data and because the data takes a while to come back the user starts clicking and thinking it has hung, This way as the data comes back the chart builds in front of their eyes .

 

[Norie]

Blade

Creating charts in Excel from data from Access, that has got to be one of my pet hates.

No, wait a minute it's creating charts in Excel

Or maybe it's creating charts in Access - I can't make my mind up.

Oh, I've just thought of another candidate - trying to automate Excel from C#.

I really can't make my mind up - anybody got a 4-sided coin I could spin.

 

[Blade Hunter]

Hey guys I just did some testing to check out ther performance boost on this.

My recordset was 18,896 Rows with a select across a table and a fairly simple view joined on one unique key.

I ran it once off before testing to make sure that any caching would occur before testing and make all three tests fair. The results are as expected especially with the calculate every 20 rows.

CopyFromRecordset
12/11/2009 9:11:13 AM
12/11/2009 9:12:07 AM
54 Seconds

Row at a time
12/11/2009 9:16:46 AM
12/11/2009 9:18:11 AM
1 minute 25 seconds

Row at a time with calculate every 20 rows
12/11/2009 9:12:32 AM
12/11/2009 9:15:01 AM
2 minutes 29 seconds

Cheers

Dan

 

[Blade Hunter]

Blade

Creating charts in Excel from data from Access, that has got to be one of my pet hates.

No, wait a minute it's creating charts in Excel

Or maybe it's creating charts in Access - I can't make my mind up.

Oh, I've just thought of another candidate - trying to automate Excel from C#.

I really can't make my mind up - anybody got a 4-sided coin I could spin.

Got no choice mate, our users don't have access installed so it's either I do it myself in Business Objects and send it to everyone when they want charts or I build this self contained thing that they can run for themselves.

Users hey, can't live with em, can't live without em .

I should send you a copy of the reporting tool I wrote, it doesn't have charts but I reckon you would be scratching your head asking why anyone would write something in Excel that there are already tools to do (ie Business Objects as a database reporting tool).

Over 10,000 lines of code and 30 forms but as with everything I have to write for this place. It does a job that none of the existing tools will do the way the user wants, why educate the user when you can just code around them .

Edit: Oh almost forgot, None of my data comes from Access mate, its all from the Oracle server .

 

[Blade Hunter]

Actually Norie, I have a question for you.

Using Worksheets("Main").Range("A1").CopyFromRecordSet RS what is going to happen if I do that one a machine with XL < 2007 and there are over 65,536 rows of data in the record set? Will it just bring in the first 65K or will it error?

 

[Craig Ottley]

Actually Norie, I have a question for you.

Using Worksheets("Main").Range("A1").CopyFromRecordSet RS what is going to happen if I do that one a machine with XL < 2007 and there are over 65,536 rows of data in the record set? Will it just bring in the first 65K or will it error?

If i remember rightly it throws an error that doesn't make any sense at all...

I've found an old piece of work that i think will help you out greatly in using Sql and Excel. http://www.balls2cancer.co.uk/comparetool.xls

Enjoy

 

[Blade Hunter]

If i remember rightly it throws an error that doesn't make any sense at all...

I suppose the way to do it would be in your SQL statement just put in "AND Rownum < " & Rows.count

Not brilliant but it would work.

 

[Craig Ottley]

There's all sorts of weird and wonderfull things you can do, normally i just don't import the data if the record count is greater than 65535.

I once remember writing a 'dirty' piece of code funnily enough from oracle. They knew there would be anywhere between 5k-250k records, each spreadsheet would have a maximum of 25k records. You can't use Copy from recordset so a big decrease in performance when quarter figures got processed.

 

[Blade Hunter]

Well I just subbed in Norie's suggestion on one of my weekly reports. This report sucks in 15 different record sets to different tabs.

I am completely blown away by the performance increase. My DB calls have gone from a total of 20 mins to 1 min and 30 seconds.