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:

 

[Irish_Griffin]

My friend suggested looking into the ByVal / ByRef ...... finding the reading on this confusing =-/

 

[xenou]

Yes, Global seems to work. Seems like the compiler quietly accepts it as a synonym for Public...

I've found two threads saying this is for backward compatibility and Public is preferred.

You'll probably get up to speed on ByRef, ByVal pretty quick - generally, use ByVal for Numbers and Strings, and Use ByRef for Objects (such as Sheets, Workbooks, and ... Recordsets). The gotcha (I think) is that Arrays are sent ByRef even if you write ByVal - so consider them like Objects.

 

[Irish_Griffin]

UPDATE:
From my reading, seems like I have the wrong understanding of a recordset.

The new game plan is not to try and store the SQL results in a recordset because that makes no since.... LOL Disregard the previous Public RS questions above.....

Here is the new order of the code
1) ADO connection
2) SQL code to make recordset
3) Recordset saved as array
4) pass array through function

TaDa!!! Not stupid anymore!!

 

[Irish_Griffin]

Project Objectives:
-> Easy to use SQL in Excel template with "tool box" format.
-> Provide tools for most common operations
-> Tools should be modifiable to fit peoples needs ( customizable ADO code )
-> Clear code layout and note for quick learning

Here is the list of tools:
-> CREATE A DATABASE
-> DELETE A DATABASE
-> CREATE A TABLE IN THE DB FROM A RANGE
-> CREATE A TABLE IN THE DB FROM AN ARRAY ( in work right now... )
-> DELETE A TABLE IN THE DB
-> PREFORM SQL CODE TO GENERATE ARRAY WITH OUT TITLES
-> PREFORM SQL CODE TO GENERATE ARRAY WITH TITLES
-> PREFORM SQL CODE AND PASTE INTO RANGE

Please help me strengthen this template by:
-> Making suggestions to improve code contents ( example, help setting up user and pw in ADO connection)
-> Improvements to code efficiency
-> Improvements to code reliability
-> Suggest / Demonstate of common operations that should be in the "toolbox"

The above tools so far, working on my machine (XP pro, Excel 2003, ADO 2.8, ADO RS 2.8, ADO Ext 2.8)

INPUT IS GREATLY APPRECIATED AND WILL BE USED

 

[Irish_Griffin]

The Tool

Sub Create_DB(DB_location_name As String)
' This function can create either a local or network .mdb file for temporary or permenant use

Dim ADO_Catalog As ADOX.Catalog
Dim Connection_String As String
Connection_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_location_name & ";"
Set ADO_Catalog = New ADOX.Catalog

'Automaticly will delete previous file if present
On Error Resume Next
    Kill DB_location_name
On Error GoTo 0

ADO_Catalog.Create (Connection_String)

End Sub

Calling the Tool Above

'CREATE A DATABASE
' /////////////////////////////////////////////////////////////////////////////////////
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Dim DB_location_name As String

DB_location_name = "C:\test.mdb" '<----------------------------------------- INPUT
'RUN THE SQL TOOL                                                            =)
Call Create_DB(DB_location_name)
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'/////////////////////////////////////////////////////////////////////////////////////

 

[Irish_Griffin]

The tool

Sub Delete_DB(DB_location_name As String)
'This function can delete a .mdb file

On Error Resume Next
    Kill DB_location_name
On Error GoTo 0

End Sub

Calling the tool

'DELETE A DATABASE
' /////////////////////////////////////////////////////////////////////////////////////
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Dim DB_location_name As String

DB_location_name = "C:\test.mdb" '<----------------------------------------- INPUT
'RUN THE SQL TOOL                                                            =)
Call Create_DB(DB_location_name)
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'/////////////////////////////////////////////////////////////////////////////////////

 

[Irish_Griffin]

The tool

Sub Range_to_DB_Table(DB_location_name As String, New_Table_Name As String, Data_Range As Range)
'This function takes a sheet and pastes it into a created database

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

Dim Column_Title As String
Dim column_count As Integer
Dim SQL_Text As String
Dim i As Integer
Dim r As Integer
Dim c As Integer
Dim Box As Range
Dim Colan As String

'delte table with same name if it exisits
SQL_Text = "DROP TABLE " & New_Table_Name
On Error Resume Next
    ADO_Connection.Execute SQL_Text
On Error GoTo 0


column_count = Data_Range.Columns.Count

SQL_Text = "CREATE TABLE " & New_Table_Name & " ("

'This tool assumes text..... will improve later
'Place the titles into an array

c = Data_Range.Column

For i = 0 To column_count - 1
    Column_Title = Data_Range.Cells(1, i + 1).Value
    'Check to see column titles meet criteria : No spaces and required text
    If Trim(Column_Title) = "" Then
        MsgBox ("Data Range has blank column titles.  Fix to execute the code.")
        GoTo The_End
    End If
    If Not InStr(1, Column_Title, " ") = 0 Then
        MsgBox ("One of the column titles has a space.  Fix to execute the code.")
        'GoTo The_End
    End If
    SQL_Text = SQL_Text & "[" & Column_Title & "] Text(150) WITH Compression, "
Next

SQL_Text = Left(SQL_Text, Len(SQL_Text) - 2) & ")"
MsgBox (SQL_Text)
ADO_Connection.Execute SQL_Text

'Data upload into table
SQL_Text = "INSERT INTO " & New_Table_Name & " VALUES("
r = Data_Range.Row
Colan = """"
'For loop starts at the top left, crosses over to the right, drops a row and repeats
For Each Box In Data_Range
    If r < Box.Row Then
        'prevents
        If r > 1 Then
            SQL_Text = Left(SQL_Text, Len(SQL_Text) - 1)
            SQL_Text = SQL_Text & ")"
            ADO_Connection.Execute SQL_Text
        End If
        r = Box.Row
        SQL_Text = "INSERT INTO " & New_Table_Name & " VALUES("
    End If
    SQL_Text = SQL_Text & Colan & Box.Value & Colan & ","
Next

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

End Sub

Calling the tool

'CREATE A TABLE IN THE DB FROM RANGE
' /////////////////////////////////////////////////////////////////////////////////////
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Dim Data_Range As Range ' The range to be copied into the database
ReDim Column_Titles(1 To 100) As Variant ' The array to pass column titles, critical for SQL code
Dim i As Integer ' General counter
Dim New_Table_Name As String ' Name of table in database, no spaces
Dim DB_location_name As String ' Full path and file name of database

'Determine the range that will be pasted into the database table
'The first row MUST be the column names with no spaces, instead use "_"
Set Data_Range = ThisWorkbook.Worksheets("Input").Range("B2:K300")
'You must create an array that contains a name for every column in the range above
'For i = 1 To Data_Range.Columns.Count
'    Column_Titles(i) = ThisWorkbook.Worksheets("Input").Cells(1, i) '<------ INPUT
'Next
'ReDim Preserve Column_Titles(1 To Data_Range.Columns.Count)

DB_location_name = "C:\test.mdb" '<----------------------------------------- INPUT
New_Table_Name = "New_Table" '<--------------------------------------------- INPUT
'RUN THE SQL TOOL                                                            =)
Call Range_to_DB_Table(DB_location_name, New_Table_Name, Data_Range)
Set Data_Range = Nothing
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'/////////////////////////////////////////////////////////////////////////////////////

 

[Irish_Griffin]

The tool

Sub Delete_DB_Table(DB_location_name As String, Table_Name As String)

'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 SQL_Text As String
SQL_Text = "DROP TABLE " & Table_Name

On Error Resume Next
    ADO_Connection.Execute SQL_Text
On Error GoTo 0

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

End Sub

Calling the tool

'DELETE A TABLE IN THE DB
' /////////////////////////////////////////////////////////////////////////////////////
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Dim DB_location_name As String ' Full path and file name of database
Dim Table_Name As String ' Name of db table to delete

DB_location_name = "C:\test.mdb" '<----------------------------------------- INPUT
Table_Name = "New_Table" '<------------------------------------------------- INPUT
'RUN THE SQL TOOL                                                            =)
Call Delete_DB_Table(DB_location_name, Table_Name)
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'/////////////////////////////////////////////////////////////////////////////////////

 

[Irish_Griffin]

The tool

Function SQL_to_Array_wo_Titles(DB_location_name As String, SQL_Text As String) As Variant
' 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
Dim RS As ADODB.Recordset

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

'Create the Array
SQL_to_Array = RS.GetRows()

'Clean up recordset object
RS.Close
Set RS = Nothing
'Close the connection so that computer resources are not used
ADO_Connection.Close
Set ADO_Connection = Nothing

End Function

Calling the tool

'PREFORM SQL CODE TO GENERATE ARRAY WITH OUT TITLES
' /////////////////////////////////////////////////////////////////////////////////////
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
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
Dim My_Array As Variant

Table_Name = "New_Table" '<------------------------------------------------- INPUT
DB_location_name = "C:\test.mdb" '<----------------------------------------- INPUT
SQL_Text = "SELECT * FROM " & Table_Name & " WHERE [Part_type]='Seal'"  '<-- INPUT
'RUN THE SQL TOOL                                                            =)
My_Array = SQL_to_Array_wo_Titles(DB_location_name, SQL_Text) 'FORMAT: Array(Column,Row) top Left starting at (0,0)
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'/////////////////////////////////////////////////////////////////////////////////////

 

[Irish_Griffin]

The tool

Function SQL_to_Array_w_Titles(DB_location_name As String, SQL_Text As String) As Variant
' 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
Dim RS As ADODB.Recordset
Dim Row_Count As Long
Dim Col_Count As Long

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

Col_Count = RS.Fields.Count

Row_Count = 0
RS.MoveFirst
Do While Not RS.EOF
    Row_Count = Row_Count + 1
    RS.MoveNext
Loop


ReDim My_Array(0 To Col_Count - 1, 0 To Row_Count) As Variant '1 not subtracted from row_count to allow room for titles
Dim r As Integer
Dim c As Integer

'Place titles on array
For c = 0 To Col_Count - 1
    On Error Resume Next
    My_Array(c, 0) = RS.Fields(c).Name
    On Error GoTo 0
Next

'copys data from recordset into array
r = 0
RS.MoveFirst
Do While Not RS.EOF
    r = r + 1
    For c = 0 To Col_Count - 1
        My_Array(c, r) = RS.Fields(c).Value
    Next
    RS.MoveNext
Loop

SQL_to_Array_w_Titles = My_Array

'Clean up recordset object
RS.Close
Set RS = Nothing
'Close the connection so that computer resources are not used
ADO_Connection.Close
Set ADO_Connection = Nothing

End Function

Calling the tool

'PREFORM SQL CODE TO GENERATE ARRAY WITH TITLES
' /////////////////////////////////////////////////////////////////////////////////////
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
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
Dim My_Array As Variant

Table_Name = "New_Table" '<------------------------------------------------- INPUT
DB_location_name = "C:\test.mdb" '<----------------------------------------- INPUT
SQL_Text = "SELECT * FROM " & Table_Name & " WHERE [Part_type]='Seal'"  '<-- INPUT
'RUN THE SQL TOOL                                                            =)
My_Array = SQL_to_Array_w_Titles(DB_location_name, SQL_Text) 'FORMAT: Array(Column,Row) starting at (0,0)
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'/////////////////////////////////////////////////////////////////////////////////////