MEET FEATURE REQUIREMENTS, SCHEDULE, and BUDGET! Consulting available to teach your organization to apply this methodology and tools for powerful quantitative management of your business processes.
- Instruction of your staff at your site, using course-ware, application example, and a functional template.
- Mentoring your key staff on an ongoing or periodic basis, on these tools or on Quantitative Program Management.
- Contracting or Employment in your organization on specific topics.
Check out my YouTube Channel: Power Operational Intelligence
Now Live! Overview, Structure, Task Data, Table Design, SQL Server, and Re-Linking now showing.
Video courses covering material on this website, and more, are presented in the playlists.
Code snippet links at YouTube Code Snippets. Twitter at @poweroperation1, #poweropi, #poweroperationalintelligence.
Subscribe on YouTube, and click the "Notification" Bell icon to be notified as content is published.
Linking Excel to Database Environments using Data Connection Wizards (Legacy)
Description. Prior to introduction of PowerQuery, Excel Data Connection Wizards were used to connect from Excel into a Database. Data connections created using this method are editable both manually via the Connection Properties dialogue box, and by VBA. This capability is used to insert SQL into a Connection, to manage its Refresh behavior, etc.
This method still works, but is hidden and its title now includes "(Legacy)" in its title, indicating deprecation by Microsoft. It is probably better to use a method that is not deprecated at least for new applications. But if you already have connections based on a Data Connection Wizard, here's how to get to it and use it.
Enable Data Connection Wizard (Legacy):
In Excel File > Options > Data
Then, to use a Data Connection Wizard:
Select Data > Get Data > Legacy Wizards > ...
Then find the section below for SQL Server or for MS Access:
Connecting Excel to SQL Server using Data Connection Wizard (Legacy)
Select Data > Get Data > Legacy Wizards > From SQL Server (Legacy)
Copy the Master Connection and customize it by adding SQL to create a JOINed QUERY using a Linked Server.
From "MasterConnect", create and save a Connection containing a useful SQL QUERY or action. A manual method is shown but could be automated in VBA.
Turn OFF Background Refresh, Periodic Refresh, and Refresh on File Open. Refresh data on Refresh All should be ON for Data QUERYs and OFF for Command QUERYs.
Example of a useful QUERY, to be embedded in a Data Connection. QUERY "ADBDE" called from Excel via this Connection "ADBDE" JOINs Tables for Allocation, BOM, Dates, Builds, and Elements. The Tables are read from Excel. The Excel Data Connection containing this QUERY is used to drive a Pivot Table or other report, in Excel. The QUERY is executed when the Pivot Table is refreshed, and returns the QUERY result through the Connection to drive the Pivot Table.
SELECT xlAlloc.*, xlBOM.*, xlDates.*, xlBuilds.*, xlElements.*,
xlAlloc.akQty * xlBOM.bmSubQty AS QtyExt,
xlAlloc.akQty * xlBOM.bmSubQty * xlBOM.bmSubCost AS CostExt
FROM BI_linked_tablesSQLServer...rngAlloc AS xlAlloc
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBOM AS xlBOM
ON xlAlloc.akAllocRevKey = xlBOM.bmBOMItemKeyIn
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngDates as xlDates
ON xlBOM.bmBOMItemKeyOut = xlDates.daSchedItemRevKey
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBuilds as xlBuilds
ON xlDates.daSchedItemRevKey = xlBuilds.bdBuildRevKey
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngElements as xlElements
ON xlBOM.bmBOMItemKey = xlElements.emElementItemKey;
Inserting the SQL QUERY into a Connection results in:
An Excel Data Connection must create a Table, Pivot Table, or Pivot Chart in the Excel client in order for the Connection to be "contained" in the Excel workbook.
A Data Connection that Only Creates Connection can be saved as an .odc file for later use but will not be "contained" in the Excel workbook.
Additional tables (presumably designed to show varying pivots and filtering) may be driven by the same SQL QUERY using same saved Connection.
Insert > PivotTable > Use an external data source:
Choose the Data Connection:
Create PivotTable dialogue box now lists the Connection name:
Finally, wizard to design the Pivot Table:
Column addition, deletion, or modification in Excel tables used in the QUERY, propagate as desired through the JOINs to drive the Pivot reports in Excel, incorporating the column updates as expected.
Connecting Excel to MS Access using Data Connection Wizard (Legacy)
Select Data > Get Data > Legacy Wizards > From Access (Legacy)
Choose the .accdb file for the intended MS Access database:
Choose the report type to create (Table, PivotTable, PivotChart) and its location in the workbook, or create a connection for later use:
From the Properties... button you can insert SQL into the Connection, set its Refresh behavior, Name it, or add a Description:
From Connection Properties you can also Export Connection File... for subsequent use.
On OK out of Connection Properties and from Import Data, set up the PivotTable (or PivotChart, or Table):
And if you Only Created a Connection, the connection can be selected via the Insert PivotTable dialog as the External Source to drive that PivotTable.