BEST PRACTICES -> SQL with DAO

Irish_Griffin

Irish_Griffin

Board Regular
Joined
Jan 16, 2009
Messages
138
Hi guys,
I've discovered excel can do SQL !!! It feels like I'm the primitive caveman discovering fire :laugh:

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 :cool:

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:
QrUHZ.jpg
 
Sort by date Sort by votes
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

1ZLXq.jpg
 
Last edited:
Upvote 0
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 :biggrin:
 
Last edited:
Upvote 0
Griff

I think DAO has actually been sort of superseded by it's anagram ADO.:)
 
Upvote 0
Norie said:
Griff

I think DAO has actually been sort of superseded by it's anagram ADO.:)
Click to expand...

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
 
Upvote 0
Griff

What are you actually trying to do or demonstrate?
 
Upvote 0
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. :cool:

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

1A1K6.jpg
 
Upvote 0
Norie said:
Griff

What are you actually trying to do or demonstrate?
Click to expand...

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.
 
Upvote 0
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.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
592
Solon Aquila
S
E
  • Question Question
VBA growing a tree from a seed to use DFS to traverse
Replies
0
Views
2K
equinox
E
N
  • Solved
Separate One Cell Data into Multiple Rows
Replies
6
Views
1K
Fluff
Fluff
C
  • Question Question
Best coding practices in VBA (specifically, code structure, readability, compactness, and the TEBV & TBTO rules)
Replies
1
Views
6K
Marc L
Marc L
I
  • Question Question
VBA - Updating label with information from another UserForm (and other small doubts from building a GUI in VBA)
2
Replies
16
Views
6K
Instantaneo
I

Forum statistics

Threads
1,226,796
Messages
6,193,048
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top