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:

 

[Blade Hunter]

Basic ADO shell for you, this is the only way I use SQL from VBA.

Sub Main()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Dim UID As String
Dim PWD As String
Dim Server As String
    Application.Calculation = xlCalculationManual
    UID = "" 'Enter the User ID
    PWD = "" 'Enter the password
    Server = "" 'Enter the name of your ODBC connection string
    Set Data = Sheets("Main") 'Change this to the name of the sheet you want to return data to
    Data.Select
    Range("A:F").ClearContents 'Change A:F to the range you expect data to return to
    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & "USER ID=" & UID & ";PASSWORD=" & PWD 'Note, I am using MSDAORA as I use an ORACLE DB, you will need to change it for what DB you are using
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    sqlText = "select Something from somewhere where something = something" 'Put your SQL Statement in here
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For X = 0 To 4 'Change the 4 to the number of columns you are selecting MINUS 1
        Data.Cells(1, X + 1) = RS.Fields(X).Name
    Next
    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
    Loop
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
End Sub

 

[Irish_Griffin]

Links and examples is exactly what I'm looking for!!

It's hard as a noob to make this step because there is soooooo much bad code online.....

I get it to work after hours and hours only to find out that the code is not efficient. Not to mention, an experienced coder thinks the author was smoking crack at the time....

Give me links to the truely best practices of SQL in excel...
.... and I will give you guys a great tutorial to point stupid noobs to

Thanks,
Griff

 

[Blade Hunter]

Links and examples is exactly what I'm looking for!!

It's hard as a noob to make this step because there is soooooo much bad code online.....

I get it to work after hours and hours only to find out that the code is not efficient. Not to mention, an experienced coder thinks the author was smoking crack at the time....

Give me links to the truely best practices of SQL in excel...
.... and I will give you guys a great tutorial to point stupid noobs to

Thanks,
Griff

The instructions are in the code as comments.

 

[Irish_Griffin]

The instructions are in the code as comments.

No doubt, and that is PERFECT. Please include notes as much as possible.

But us noobs need in really broken down into small bites. My professional job is to write technical instructions for others to learn from. Pretty good if I say so myself

I thought it would be cool to make a nice tutorial document for your community in exchange for getting taught from the community

OK, it's time to clock out of work. I'll catch you guys tomorrow.

-Griff

 

[Norie]

Griff

I can't think of the top of my head of any particular sites to recommend.

I actually use Microsoft quite regularly for reference for this sort of thing.

Here's one link I sometimes visit.

It's not SQL specifit, but then again I sometimes use this link.

 

[xenou]

I also refer to the link that Norie provided.
To add to it (and include it again) to some further online ADO resources (it's been a while - I'm not even sure what's in all of these any more. I know erlandsen has a quite a lot on ADO):

http://msdn2.microsoft.com/en-us/library/ms678086(VS.85).aspx
http://www.erlandsendata.no/english/
http://www.beyondtechnology.com/geeks023.shtml
http://support.microsoft.com/kb/257819
http://www.rondebruin.nl/ado.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;257819
http://www.mrexcel.com/forum/showthread.php?p=1207314
http://www.datawright.com.au/index.htm

Of greatest value to me (I think) has been:
1) Wrox's Excel VBA Programmer's Reference (I've used 2003 edition by Kimmel at al. but 2007 is now out by Green et al)
2) w3schools is excellent on ADO objects and methods: http://www.w3schools.com/ado/default.asp

Just to note that DAO is fine - I'm not as used to implementing it from Excel, but I think it will be supported for years yet. It works very well with Access, in fact.

 

[ukmikeb]

Links and examples is exactly what I'm looking for!!

It's hard as a noob to make this step because there is soooooo much bad code online.....

I get it to work after hours and hours only to find out that the code is not efficient. Not to mention, an experienced coder thinks the author was smoking crack at the time....

So one has graduated from noob to Guru in 4 posts!

If the code achieves the objective set by the op does it really have to be ultra efficient?

Also if there are glaring deficiencies in the code the Gurus generally add a note to point the responders in the right direction.

Mike

 

[Irish_Griffin]

So one has graduated from noob to Guru in 4 posts!

If the code achieves the objective set by the op does it really have to be ultra efficient?

Also if there are glaring deficiencies in the code the Gurus generally add a note to point the responders in the right direction.

Mike

Hmmmmm, well ultra efficient is not my goal. To better define it:

Goal is to create:
- Standard template where the user needs to have SQL capabilities in excel using ADO
- Extensive detailed notes so that the code is easy to learn from
- Module for many common functions
- ROBUST : flexible template that meets most project needs and does not break !!


Well gents, haven't forgotten about my task. Finally got some time at work to continue. Go grabbin a cup of Joe and placing my thinkin cap on.

Time to get my ADO learn on!!

 

[Irish_Griffin]

THANKS FOR ALL THE LINKS!!!!!

Blade Hunter - You da man for giving me a great head start!!

 

[Blade Hunter]

Happy to help, post back if you want any other working examples, I wrote one a while back for uploading rows of data to a database then reading back and verifying the data. There are other ones I have done which make calling Stored Procs a breeze and simply report back true or false from the function to say if it ran or not.