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:

 

[xenou]

I think you could:

1) Dim the recordset as a private (module-scope) or public (project-scope) variable.

2) Pass it by reference from one sub to another, as with any other object variable.

 

[Blade Hunter]

Copy RS in to a global array or a sheet.

 

[Irish_Griffin]

Copy RS in to a global array or a sheet.

So a global record set is not possible..... that's a bummer

WELCOME BACK FROM THANKS GIVING EVERYONE!! Hope you scored big with tasty left overs !!!

 

[Irish_Griffin]

Copy RS in to a global array or a sheet.

I want to avoid connecting to the ADO database as much as possible......

So is the best solution:
1) Create Record set
2) Save to global Array
3) When needed: Convert Array to ADO recordset
4) Preform SQL code
5) Use results however you want.......

 

[xenou]

So a global record set is not possible..... that's a bummer

Have you tried the suggestion (post 51) to dim the recordset with module-level or project-level scope?

 

[Irish_Griffin]

Have you tried the suggestion (post 51) to dim the recordset with module-level or project-level scope?

Yep, working on it right now..... I think the problem was I was trying to :

Global RS as New ADO.Recordset

But now I'm trying

Global RS as Recordset

Thanks

 

[xenou]

I see. Actually the library you want is ADODB -

Public rs as [B]ADODB[/B].Recordset

Don't ask me why! No idea.

Note:
With VBA, is it the Public keyword you want too?
So,

Public gRecordset As ADODB.Recordset '//Project scope
Private mRecordset as ADODB.Recordset '//Module scope
Sub foo()
Dim rs as Recordset '//procedure scope
End Sub

I see this global keyword used from time to time in posts but I don't know if it actually works in vba...

 

[Irish_Griffin]

OK cool, Excel was accepting "Global" but I haven't gotten far enough in the code to prove it worked out......

I'm just going to switch up to the best practice:

Public RS1 As ADODB.Recordset 'Project Scope

Thanks

 

[Irish_Griffin]

OMG - Today has been going awesome!!!! Only been interrupted once

Getting so much code done, wish this was everyday cause time is flying by!!!

 

[Irish_Griffin]

OK, I'm stuck on the Public Recordset issue..... please help

The recordset setup:

Public RS1 As ADODB.Recordset 'Project Scope

Here is the test code calling on the tool:

Sub temporary_sub5()

'PREFORM SQL CODE TO GENERATE GLOBAL RECORDSET
'\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_
Dim DB_location_name As String ' Full path and file name of database
Dim Table_Name As String ' Name of db table to delete
Dim SQL_Text As String

DB_location_name = "C:\test.mdb"
SQL_Text = "SELECT * FROM New_Table WHERE Part_type='Seal'"

Call SQL_to_RS1(DB_location_name, SQL_Text)
'OR USE :
'Call SQL_to_RS2(DB_location_name)
'Call SQL_to_RS3(DB_location_name)
'Call SQL_to_RS4(DB_location_name)

'\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_/^\_

'TEMP CODE BELOW FOR TESTING
Dim RS1_to_Array() As Variant
RS1_to_Array = RS1.GetRows
'END TEMP CODE

End Sub

And here is the tool itself.... from what I can tell, the recordset is deleted after the code exits this sub-routine tool.

Sub SQL_to_RS1(DB_location_name As String, SQL_Text As String)
' This code will run SQL on an existing table and results will be outputed to a new db table

'Opens a connection to the database File
Dim ADO_Connection As ADODB.connection
Set ADO_Connection = New ADODB.connection
Connection_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_location_name & ";"
ADO_Connection.Open (Connection_String)

Dim CMD As New ADODB.Command

CMD.ActiveConnection = ADO_Connection
CMD.CommandType = adCmdText
CMD.CommandText = SQL_Text
Set RS1 = CMD.Execute

'Close the connection so that computer resources are not used
ADO_Connection.Close
Set ADO_Connection = Nothing

End Sub