By creating QueryTables we get so called dynamic database queries that can be updated on a regular basis.
In order to create QueryTables manullay we need MS Query but by doing it in VBA it can be done without the add-in.
However, the QueryTable rely heavily on ODBC which may be considered as an old fashion technique which I tend to agree on.
So when applying an ADO-approach we need to be aware of that we can't update it in the same way as we can do when using the ODBC-approach.
For more details about the ODBC-approach please see the following article:
What we need:
* Microsoft Windows 2000 or above
* Microsoft Excel 2000 or above
* MDAC 2.5 or above
* A database and in the example below a local SQL Server 2000 is used.
Add a reference to the Microsoft ActiveX Data Object 2.x library via Tools | References... in the VB-editor.
Add a standardmodule to the workbook and then add the following procedure.
- Option Explicit
- Sub Add_QueryTable_ADO_Recordset()
- Dim cnt As ADODB.Connection
- Dim rst As ADODB.Recordset
- Dim stSQL As String
- Dim qtData As QueryTable
- Dim wbBook As Workbook
- Dim wsSheet As Worksheet
- Dim rnStart As Range
- Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
- "Persist Security Info=False;" & _
- "Initial Catalog=Northwind;" & _
- "Data Source=IBM"
- Set wbBook = ActiveWorkbook
- Set wsSheet = wbBook.Worksheets(1)
- With wsSheet
- Set rnStart = .Range("A1")
- End With
- stSQL = "SELECT * FROM Shippers"
- Set cnt = New ADODB.Connection
- With cnt
- .CursorLocation = adUseClient
- .Open stADO
- .CommandTimeout = 0
- Set rst = .Execute(stSQL)
- End With
- 'Here we add the Recordset to the created QueryTable.
- Set qtData = wsSheet.QueryTables.Add(rst, rnStart)
- 'In order to view any data the QueryTable need to be refreshed.
- 'Cleaning up.
- Set rst = Nothing
- Set cnt = Nothing
- End Sub
Personally I would like to see some major improvements of the QueryTable enabling us to manipulate its contents and properties with ADO.