Databases Reference
In-Depth Information
FIGURE 8.11
SQL-based data extractions to Excel.
SQLExtract = "SELECT " & frm8Extract.ComboBox_Fields & " FROM
" & frm8Extract.ComboBox_Tables & _
" WHERE " & frm8Extract.ComboBox_Fields2 & " LIKE '%"
& frm8Extract.TextBox_Query & "%';"
'Debug.Print SQLExtract
If SQLExtract = "" Or frm8Extract.TextBox_File = "" Then Exit Sub
Workbooks.Add
Select Case frm8Extract.OB_ADO
Case True 'USE ADO
Call
QueryToWorksheet
(frm8Extract.TextBox_File, SQLExtract)
Case False 'USE XLODBC
Call
RetrieveDataXLODBC
(frm8Extract.TextBox_File,
SQLExtract, Range("A1"))
End Select
End Sub
Two subroutines are utilized, each of which will execute the SQL query and return its results
in a different manner. The
QueryToWorksheet
subroutine utilizes ADO.
Sub QueryToWorksheet(mdbfile As String, SQLString)
'Query Results, Return Results to Worksheet Using ADO
Dim wksrow As Integer, col As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Fields()
On Error Resume Next
'Set ADO Connection
Set cn = New ADODB.Connection
'Establish a DSN-less connection to the MCS Sample Data Base
