BEST PRACTICES -> SQL with DAO

Irish_Griffin · Nov 5, 2009

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:

Irish_Griffin · Nov 5, 2009

First you need to create the DAO database ( similar to access .mdb file )

Don't forget to:
- Dim your database and recordsets ( record sets are individual tables within the database )
- VBA Window -> Tools(TAB) -> References -> Checkbox "Microsoft DAO 3.6 Library"
- VBA Window -> Tools(TAB) -> References -> Checkbox "Microsoft Access 11.0 Object Library"

Code to place in thsWorkbook:

Code:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Sub Workbook_Open()
    Set db = OpenDatabase(ThisWorkbook.Name, False, False, "Excel 8.0; HDR=NO;") 'HDR refers to the excel header row.
    Dim SqlStr As String

    MsgBox (ThisWorkbook.Name)
    'Automaticly makes the entire excel sheet a recordset within the database
    Set rs = db.OpenRecordset("Standard_Components$")
    While Not rs.EOF
    
        Debug.Print rs(0)
        rs.MoveNext
    Wend
End Sub

Please Please help me pull this knowledge together.

Regards,
Griff

Irish_Griffin · Nov 5, 2009

Irish_Griffin said:
#1 - Is DAO the right choice ?

I've selected DAO because my friend said it will be easier but less capable.... But I just want to do basic SQL. Thoughts on item #1 ?

Irish_Griffin · Nov 5, 2009

THIS THREAD IS UNDER CONSTRUCTION....... PLEASE WAIT TO COMMENT UNTIL I POSE A LIST OF QUESTIONS...

**** Please correct me if I suggest any code that is not BEST PRACTICE ****

I'm a noob at this but will used this thread to pull all the right info together with everyone's help =-)

In VBA window -> thisWorklbook use this code to clean your database when you close the excel file:

Code:

Sub workbook_close()
    db.Close
    Set db = Nothing
End Sub

So now we have the Excel table in a record set and can preform SQL on it

Norie · Nov 5, 2009

Griff

I think DAO has actually been sort of superseded by it's anagram ADO.

Irish_Griffin · Nov 5, 2009

Norie said:
Griff

I think DAO has actually been sort of superseded by it's anagram ADO.

Sooo... I guess I'll start over and write the tutorial to ADO. =-)

I've been coding long enough to understand the true meaning of DO IT RIGHT THE FIRST TIME !!!! LOL.....

I really want to make this an easy step by step instructional so that anyone who is intermediate at VBA can make this leap.

SOooooooo...... Since I know zilch about ADO :
Please have open discussion on the ADO SQL basics, I will gather the information and put it into a new thread that is well structure and link this thread for everyone's credit.

Thanks,
Griff

Norie · Nov 5, 2009

Griff

What are you actually trying to do or demonstrate?

Irish_Griffin · Nov 5, 2009

HEY GURUS!!! Lets talk BEST ADO PRACTICES in using SQL

Start your reply with the topic number.
I will organize the information into a finalized step by step thread referncing this thread as the open disscussion that gathered the info.

TOPICS I NEED HELP WITH ( best practice only please.... ) :
#1 - How to create an ADO database
#2 - How to create an ADO recordset
#3 - How to preform an SQL staqtement onto a ADO recordset and create a new recordset
#4 - How to output an ADO recordset to an excel sheet
#5 - How to output an ADO recordset to an array

Please feel free to add more basic topics for people to respond to. Next topic number is #6

THANKS !!!
Griff

Irish_Griffin · Nov 5, 2009

Norie said:
Griff

What are you actually trying to do or demonstrate?

Ehe... no need explaining my project. Just want to gather the very basic best building blocks to use SQL in excel

Cause I will use this info OVER and OVER agian for all kinds of projects. Want to make sure I'm doing the critical stuff the best way possible.

Norie · Nov 5, 2009

Griff

1 There isn't really such a thing as an ADO 'database'. ADO like DAO is a means of working with data.

That data could be from an Access (or other) database, or some other data source - even a text file.

Though you could use ADO to create a database.

2 Again there isn't really such a thing.

3 You should probably be using SQL to create your recordset in the first place.

4 There are various methods, you could loop through the recordset you've created, you could use CopyRecordset, you could use GetRows.

5 Using GetRows with a recordset will create an array.

I know that probably isn't much help, I've only dabbled with ADO with Excel but I do have some simple examples.

I could post them if you want but if you do a search you should find a thread I posted to that uses ADO and Excel to populate a combobox etc.

It has a wee bit of SQL in it.

If you can't find it I'll try and post a link to the thread.

BEST PRACTICES -> SQL with DAO

Irish_Griffin

Board Regular

Excel Facts

Irish_Griffin

Board Regular

Irish_Griffin

Board Regular

Irish_Griffin

Board Regular

Norie

Well-known Member

Irish_Griffin

Board Regular

Norie

Well-known Member

Irish_Griffin

Board Regular

Irish_Griffin

Board Regular

Norie

Well-known Member

Similar threads

Forum statistics

Share this page

BEST PRACTICES -> SQL with DAO

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock