dbnet.home Bookmark and Share

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 courseware, application example, and a functional Framework.
  • 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.

Brochure  ...  Contact for Details

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.

VBA Creation and Control of Data Connections to SQL Server and MSAccess

This page describes a "Working Path": documenting a PowerOpI logistics tool design that has been prototyped and used, is known to work, and provides a working instance to use or from which to adapt with incremental changes. It demonstrates many design techniques.

As to incremental changes, this is Excel, and there are many paths that could work, and many more that lead to dead ends for a project as broad as this one. This is not tips and tricks, this is an architectural solution. As it is used, there will be many incremental changes possible. But you can start from an initial Working Path, using these examples.

It will always be possible that Excel will change too. That is in fact the whole genesis of this page... the low-code designs shown for now on some other pages on this site, are now more limited by changes made to Excel in just the past year. Nevertheless, the premise of the designs shown here has proven to be very powerful for the Use Case documented here and for related projects, so this new PowerOpI Framework implementation has been developed to keep solutions viable for that Use Case. And this new methodology is more powerful than ever.

Use Case Sets Operational Constraints

A primary use case involves management of development and New Product Introduction (NPI) of fairly complex system elements and systems including silicon, board assemblies, subsystems, EE, ME; Firmware, Diagnostics, OS Driver, element and system management, client and server function such as transport, streaming, encoding, storage, rendering etc. Prototype and productized code and physical items are locally developed or come from a library or OEM, open-source, third-party or JDM with branch-driven DevOps. Organizational management entities include captive and contract developers, Manufacturing Supply Chain and Operations, with offshore JDM/CM.

Product, organizational, and project operational structure are described quantitatively by tools and tables. A core set typically includes task-sequence GANTT/"schedule" including tasks/integrations/releases for all elements/assemblies for HW, FW, ME, SW, DevOps and from which operational dates can be derived; element allocation, configuration and use; element Bill-of-Materials (BOM) product hierarchical structure; Element task dates from GANTT; Element operational data (PN, Supplier/Distributor), technical data links/power/physical, buyer. A focused set of project leads manage Table content.

A product is developed and validated within the effort which is obvious to all, but less obvious to many, concurrently developed and proven are sourcing, production, certification, compliance, and product and customer support.

Project Leads include cross-functional Development (EE, ME, SW, SQA, DevOps et al), Compliance, Supply Chain, Operations, Support, Marketing and are physically distributed. Project Leads have some depth with Excel but no exposure to use of Database.

Effort to be managed is complex enough that Excel standalone is inadequate: even well-developed local tools miss interactions, integrations, and project scope. Formal production tools focused on management of product development, NPI, finance and headcount are oriented toward revenue-production and minimally support product development. Project structure is ad-hoc meaning dependent on product structure and current organization status. Structure is temporary, used for the life of the development effort but handed off to production systems over the course of NPI. There is no IT support for management of product development so Program Manager and Leads develop their own structures and tools.

The effort described here may look constructed as a worst-case management problem, but is common practice among well-known suppliers of electronic systems. Complexity varies with project scope. Much of this work has been offshored over time to CM, JDM, ODM and OEM partners. This has the effect of hiding the complexity from the company owning the effort, but the complexity exists nevertheless, managed by partner processes - the owner just takes the result without detailed knowledge or control of shortcuts taken or constraints built in to the product, or even of actual defects in product function, compliance, or environmental operation. Issues discovered during or after the development effort are routinely accepted as schedule issues or product defects to be corrected at some cost and delay and which may be difficult on units already in customer use; or they may even be ignored. This can be a cause of effort or even product failure.

The same issues may exist even if development is local/captive within the owning company albeit with earlier and better visibility and perhaps mitigation. But the methodology and tools described on this site are aimed to avoid such issues in the first place, resulting in a better-managed effort, a more-robust product, and a more cost-effective product effort.

Through experience applying tools to several (actually, many!) projects, I've come to the following principles:

  • Logistics problems, and other problems of similar structure, can be broken down to sub-problems that can be quantitatively represented in two-dimensional tables. Many can be represented specifically by tables representing ALLOCATION: configuration, and use of physical and logical project elements; BOM: describing structures of product elements; DATES: from project schedule that are relevant to project elements; and ELEMENT: operational and technical data and references.

  • These tables can be connected among themselves using structures such as INDEX/MATCH/MATCH, XLOOKUP, and VLOOKUP to cover row-by-row dependencies among the sub-problems. They can also be expanded using JOIN operations, to facilitate hierarchical aggregation of elements into assemblies that can be scheduled, allocated, build or and bought. All of these operations require some skill with Excel and perhaps also with database tools - SQL Server or MSAccess.

  • Efforts quantified and structured in this way easily represent multi-dimensional problems probably beyond personal predictive imagination, and certainly beyond ability to implement using the manually-created spreadsheets in common use to manage efforts.

  • The Program Manager cannot count on deep tool skills among program participants. They usually have deep skills in product technology or organizational processes. Many would consider themselves strong users of Excel. But extension of those skills to include any of Structured Tables (normalized!), INDEX/MATCH, VBA, database Connections, SQL or Power Query M is very rare.

  • Most people know their skill area or organization quite well. Very few know enough aspects of the full product or of the cross-functional task path required to develop it. So locally-developed solutions to pieces of the problem almost always fail to integrate with other sub-problems, do not agree on key points, are incomplete, may address individual elements well but do not address element or full-product integration at all, fail to handle requirements of other groups… and all the related sins. The coordinated effort among organizational participants that is required to achieve these is unlikely to really occur since staffing is focused on local organizational aspects of the product development.

  • People solving these problems, and projects needing those solutions rarely if ever have IT support, and must develop project tools on their own. Formal tools addressing pieces of the problem are generally captive to production processes, and are not responsive to the time-bound nature or informal considerations of development projects.

  • So the task of creating such a solution generally falls to Program Management. That is usually in the form of a starting-point skeleton that must be validated with program participants and tweaked extensively with them to represent the local complexities. As a by-product though, this builds a great deal of knowledge in the program management staff and tools developed and turns into a key and tangible way by which the value of Program Management can be realized throughout an organization.

  • Project personnel are distributed. So tool configuration and access paths vary among participants. Distance, time zone, and languages complicate detailed support of the distribution.

  • Tools described here use Excel; with MS Access, SQL Server, or Power Query to assemble Tables describing logistical components of the problem into operational summaries for planning and operational direction. Each of these has its own strengths and constraints. Excel runs on Windows and Apple macOS, and functionality may vary a little depending on which OS.

    • MS Access is simple to set up and use. It can be used on a desktop or in semi-centralized fashion from a shared directory on a common server, but Queries are executed on the Client, requiring MS Access to be installed on client computers in either a native Windows installation or in a Windows Virtual Machine; or available to users via RDP. Querys can be held within the centralized .accdb file to centralize sustaining and functional access and to "hide" visibility of file structure of source Tables. Client connections can embed SQL. MS Access can host Tables, or link to Excel Tables as source data. Excel Pivot Tables, Pivot Charts and Structured Tables can be driven from an Access Query.

    • SQL Server provides a common database structure residing on a common server, or can reside on client computers. Substantial programmabililty is provided via T-SQL. SQL Server can run on several OS environments, and clients also are not restricted to Windows. Querys execute in SQL Server on the Server computer. Client connections can embed SQL, or cause execution of Stored Procedures. SPs provide centralized function release and maintenance and security of source data file structure. SQL Server hosts tables, and supports Linked Servers and OPENROWSET to connect to Tables from Excel and other data sources. SQL Server requires substantially more effort to set up and manage, offset by scalability and functionality.

    • Power Query provides connectivity to dozens of data sources, including Excel, MS Access, and SQL Server. Querys execute on the Client, but Power Query delegates as much as possible to data source providers. Querys can embed SQL for execution on the data source provider or cause execution of Stored Procedures or Stored Querys; in fact, any functionality provided by the data source provider. Power Query directly supports DAX and M, a powerful functional language supporting data source connection, Table extraction and transformation, JOINs and more. All of these configurations can drive Pivot Tables, Pivot Charts and Structured Tables in Excel. M language is not broadly known, and documentation is scattered at best.

    • Power BI Desktop is a standalone Windows desktop client supporting Power Query, and provides numerous summary charts for data analysis, dashboards, management summary and decision support. Power BI Desktop does not support Pivot Tables. So Power BI Desktop would be used for for summary analysis and reporting, formal Project Documentation and Review, management status, and decision support. An Excel client would be used for operational execution, typically using Pivot Tables to direct detailed operation throughout the breadth and depth of an organization.

  • So, characteristics of the tools described on this site:

    • Can be conveniently developed by a Program Manager who acquires the skills described here. The skills may be developed incrementally, and code is provided on this site to provide a basis.

    • Key users may develop incremental skills to manage their portions of the dataset.

    • The tool, source data, and reporting may be aggregated centrally providing integration among its several users, plus distributed visibility with security. Data structures themselves and paths to them may be hidden within the tools developed.

    • Wherever possible, the tool is self-locating: paths relative to the tool can be evaluated so relative paths can be used, and common variables are provided that evaluate to tool name and location, and database server computer and SQL Server instance, databases, and linked servers.

    • Distribution, update and reconfiguration of the tool and source data are simplified by pushbutton automation to build, update, and re-configure Connections. Self-locating values are also pre-populated in Framework Connection definitions for about a dozen types of connections.

    • Both management and use of detailed data in its integrated context, and management summaries derived consistently from that data, are supported.

    • Functionalities of Excel, PowerQuery, PowerBI, and SQL integrate cleanly within solution structures described here, available for users with skills and security access.

    • Real product development projects within large and small companies have used these tools and methodologies. Use and integrations among the product capabilities was developed by me specifically to address real usage needs encountered among a number of real projects.

Using VBA in Excel to Build and Manage Connections

This site has for some time shown how to build and manage Excel Data Connections using the Excel User Interface. This is a low-code approach, available to a broad set of people and requiring only a low-level of training in VBA, Data Connections, or SQL. In this way, the approach has made Excel Structured tables used with SQL JOINs available in more organizations to solve hard problems with much greater power than otherwise available.

However, some changes have been made to Excel that necessitate a new approach. This is a list of changes important to use of the Power OpI methodology and tools; it is not necessarily a complete list of changes; and there could be workarounds that haven't yet been determined. Nevertheless, a way of dealing with these problems is addressed below. As usual this description focuses on the PowerOpI tools; but the problems and solutions undoubtedly apply to any solution using Excel with a SQL database that works directly with the database using SQL.

  • Actions in the SQL database that change its structure can no longer be driven by QUERYs driven from Excel Data Connections. These include SQL CREATE and DROP of STORED PROCEDUREs, LINKED SERVERs and TABLEs within the SQL database. However, it is still possible to execute a SQL QUERY embedded in a Data Connection, or EXEC a STORED PROCEDURE from a Data Connection.
  • An Excel Data Connection created by the procedure Menu "Data" > "Existing Connections" > "Connections" tab > "Browse for More…" > "New Source…" used to create new connections to SQL Server and MS Access will ultimately drive to the PowerQUERY editor to define the connection, which then forces a "Load To…" operation. This provides minimal support for SQL and furthermore results in several Data Connection parameters being Read-Only. This does not support the distributed operation of the Use Case described above.
  • Updates to the connections created using the PowerQUERY editor must be managed and modified by the PowerQUERY editor, a manual operation that weights down maintenance of distributed PowerOpI clients.

However, Excel Data Connections can still be created and managed using VBA in Excel, and maintain the described Use Case..

So a tool is described on this website that provides automation for building, distributing and configuring connection-based database access from within Excel. Connection-based QUERY and JOIN operations on Excel Structured Tables from within Excel can be performed using SQL and configuration tools for Linked Servers, OPENROWSET etc. with:

  • Microsoft Access
  • SQL Server
  • PowerQuery

A QUERY may bring JOINed Source Data into Excel, or into the database tool and then into Excel, from:

  • Tables within the Database Tool
  • Excel files
  • Text files

How is this different from PowerQUERY in Excel? This tooling provides automation to conveniently use Excel with SQL, creating QUERY and JOIN functionality, with configuration for Linked Server and OPENROWSET to bring in external data types. This tooling then supports use of Excel with configured SQL and M among multiple distributed users with automation to assist configuration of location-sensitive paths. The tool integrates seamlessly with PowerQUERY, providing that with enhanced support for use with SQL QUERY and JOIN. The functional result combines the power, connectivity and convenience of Excel with the power of SQL, together with the capabilities of PowerQUERY and M.

Main Components of PowerOpI SQL Tooling Framework

First, you should have SQL Server, SQL Server Management Studio (SSMS), MSOLEDBSQL, and Microsoft.ACE.OLEDB.XX.0 (ACE) installed and configured as described on page Excel-SQL Server on this site. Links are provided there for components to install. If possible, also set up Visual Studio and SQL Server Development Tools (VS-SSDT), also linked on that page.

A single PowerOpI SQL Tooling Framework file contains several functionalities:

  • Source Data Tables. These are Structured Tables, or Named Ranges, or Worksheets, in Excel containing primary data defining the Project/Program/Product/Process represented. Data may also be imported from MS Project, using an exported Excel .xlsx file, for dates, or from SQL Server, or from a Text file or an MS Access database. Tables can be imported from another source by using database QUERY in order to add functionality to that table either by the QUERY or locally in Excel, and thence cycled again through subsequent Report QUERYs. Use of Excel Structured Tables is preferred because of characteristics of such tables, but they must be provided with a named Shadow Range. It may be necessary to use a full worksheet as a Source Data Table, if that is the only choice for export format (as, for example, from MS Project).
  • Reports and Tables. Reports are typically Pivot Tables or Pivot Charts, driven from SQL QUERYs contained in the workbook containing the Reports. Pivot Table shown at left, and an imported Table is shown at right. An imported Table is represented in Excel as a Structured Table and may itself be provided with a named Shadow Range so that it can be used as Source Data for a subsequent QUERY.
    images/vbaConnectionImages/ReportSm.jpg images/vbaConnectionImages/ReportTableSm.jpg
  • Connection Management, consisting of
    • Data Connection Configuration table: Connection definition, with Build/Update/Refresh automation.
    • Common Configuration Variables. These can be used in Excel to build location-sensitive values to build QUERYs to be used among multiple distributed users of Report workbooks.
    • Editor for Configured SQL. Build SQL statements using configuration variables for location, named Linked Servers, file paths for OPENROWSET or named Linked Servers. SQL lines in the figure below in blue typeface are Excel expressions incorporating Common Configuration Variables to provide filenames and paths, Linked Server name, names of related SQL Stored Procedures etc. Using these common variables is helpful to maintain functional integrity among related SQL QUERYs.
    • QUERY Export. Exports Configured SQL QUERYs to a designated directory, for use with SQL Server Management Studio (SSMS) and Visual Studio (VS-SSDT). Create configured SQL for CREATE/DROP of Databases, Tables, Procedures, Permissions etc. CREATE/DROP no longer be initiated from Excel, and must be done from SSMS or VS-SSDT. Configured definition of these exported QUERYs creates entities to be used with Excel-initiated configured QUERYs in Report workbooks using the same Common Configuration Variables. The string #VBTEXTJOIN: EXPORT; within a SQL comment indicates which SQL procedures to export, using button Execute tblSQL #Directives (shown in above figure).

All components described here are contained within a single Excel Framework File. As noted above, functionality generally consists of Source Data, Reporting, and SQL Management; and these in practice are commonly split from the Framework into separate workbook files for the three separate purposes listed above.

  • Source Data Workbook(s): Source Data may be contained in one or more workbooks visible to SQL Server or MS Access, each containing data tables defining process and product structure (e.g., Allocation, Dates, Builds, Element Data, Supply Chain), maintaned by owning organizations. Each Source Data Workbook may contain connections to import data into tables (e.g., Date import from MS Project). Each Source Data workbook would contain a Data Connection Configuration tab if it needs to manage any Connections and QUERYs contained in that workbook.
  • For use with SQL Server, Reports (Pivot tables) driven by SQL Server Connections cannot be refreshed from OPEN Source Data files, so Source Data must be in files SEPARATE from any files containing Reports driven from that Source Data. The Source Data files must be CLOSED at the time of Report refresh.
  • A Source Data workbook may simply be a workbook containing data in Structured Tables, or it may itself be a copy of the Framework workbook if the Source Data requires Connection functionality - for example, pulling Date data into a Table in the workbook, to which computation might be added (such as key creation for subsequent JOINs) or that may provide data fields used in computation in other Tables in the Source Data workbook. Just make sure, at each level in such a hierarchy of workbooks, that Source Data files are closed for any workbook connections to be Refreshed.
  • For use with MS Access, Reports and Source Data may reside within a common file.
  • Report Workbook(s): Files provided as Report files to distributed users typically contain Report Pivot Tables etc. driven from contained Connections that use EXEC QUERYs calling Stored Procedures in the Database system, thereby protecting construction of the QUERYs and the file structure and database structure underlying them, and also centralizing and simplifying maintenance of QUERY construction. Some Report Workbook QUERYs could nevertheless contain embedded SQL. Report Workbooks contain Reports/Pivot tables prototyped in the Management Workbook, and may also contain locally-created Reports based on Stored Predures available to Users. Each Report workbook would contain a Data Connection Configuration tab, managing its Connections and QUERYs contained in that workbook.
  • SQL Management Workbook: SQL Management capapability from the PowerOpI Framework is typically held in a workbook specific to management, and containing prototypes of Reports to be distributed (Pivot Tables, Pivot Charts, Tables), and SQL development for the configured SQL Stored Procedures to drive those Reports. The workbook contains QUERY definitions for CREATE/DROP to be exported to perform those actions in SSMS or VS-SSDT to create the Stored Procedures, Tables and Databases. The SQL Management workbook would contain a Data Connection Configuration tab, managing Connections and QUERYs contained in that workbook, and these connection configurations provide prototype Connection configuration to be replicated within distributed Report workbooks.

The PowerOpI Framework is not distributed here, but the mission of this website is to show how to use a Database Tool with Excel to address hard configuration problems. Because of changes made to Excel this is no longer a Low-Code proposition... So, critical sections of the VBA code required are shown in the following links, to be described below.

  • vbaConnect.txt contains code for a common module to manage connections. (Requires distribution from Quantitative Management Consulting).
  • vbaDataSources.txt contains code for the Data Sources tab, and for each tab containing a Source Data table. (Requires distribution from Quantitative Management Consulting).

Overall Operation

Notice the command buttons at lower left of the Data Connection Configuration Table:

The top four buttons initiate Build or Update of connections to Source Data. Build means "create if no connection by that name exists", and set all parameters as described by the Table. Update also means "create if no connection exists by that name", but "Update" the existing database Connection String if one existed, and for new connections set the Connection String to the Template from the Table. All other parameters are set as described in the Table.

DATE tables get their own Build/Update buttons. They function the same as for DATA tables but are generally updated separately so that you can work on dates without affecting logistics until you're ready to; and then you should update DATES before updating DATA, so that the DATA will pick up the newest set of dates. DATA tables are defined by LinkType "PivotSource" (for Data Connection) or "webQuery" (for PowerQUERY). DATE tables are defined by LinkType "mppImport" (for Data Connection) or "mppQuery" (for PowerQUERY).

The same sequence applies to Build and Update, for DATA and for DATES; and also for both Connection and PowerQUERY QUERY definition records. The exact coding for QUERYs is a little different than for Connections but the overall sequence is the same:

  • tblConnectionConfig is scanned line by line and each line is processed.
  • All parameter values are taken from tblConnectionConfig. If blank, a default value of "" (null string) or "FALSE" is provided for Text and Boolean parameters respectively. Some pre-processing is provided to evaluate validity if a file path is provided; and to push parameter values into the Database Connection String (see below).
  • If no Connection or QUERY exists by the name provided, a new one is created using provided or pre-processed parameters.
  • Creation doesn't set all parameters listed, so the remaining parameters are set from values from tblConnectionConfig.
  • With parameters all set, the Connection or QUERY is refreshed.

Once all entries in tblConnectionConfig have been processed (that is, created and/or updated), all connections contained in the Workbook are examined. Only Connections/QUERYs that have a definition record in tblConnectionConfig are kept; any Connection/QUERY not defined in the Table is deleted. Un-used connections/QUERYs not actively kept up-to-date only cause problems over time when refreshed with parameters that no longer make sense, so this is garbage collection.

The fifth command button initiates #Directive operation from tblSQL. Currently only Export is supported, and is described below.

If Not boolExists(strConnectionName, thisWorkbook.Connections) Then
    ' If connection(strConnectionName) NOT PRE-EXISTING,
    '   create it with parameters required for creation

    Set thisConnection = thisWorkbook.Connections.Add2( _
        strConnectionName, strDescription, _
        strConnectionString, strCommandText, _
    'If connection(strConnectionName) IS PRE-EXISTING, update its params required 
    '   for creation so that it's in state equivalent to a newly-created 
    '   connection

    Set thisConnection = thisWorkbook.Connections(strConnectionName)

    If thisConnection.OLEDBConnection.Refreshing Then _
        ' Turn off ongoing activity if any - it will refresh later

    thisConnection.Description = strDescription
    thisConnection.OLEDBConnection.Connection = strConnectionString
    thisConnection.OLEDBConnection.CommandText = strCommandText
    thisConnection.OLEDBConnection.CommandType = lCmdType
End If

Below we'll describe where the str and bool parameters come from; Connection String Templates; Error Handling; string replacement function strReplaceString2; further functionality to complete parameter updates, to refresh connections, and cleanup of un-used connections; and handling of SQL CREATE and DROP.

Connection Parameters

In the Prototype Framework to be described on this site, Connections in an Excel workbook are described in the Excel UI within a Structured Table, one Connection described in each table record. The Connection parameter values are contained in fields (columns) in each record, each named for the parameter specified. The table currently lists 26 values for each connection. Fortunately, templates are provided in the table that describe many styles of connection (ACE, MSOLEDBSQL; Data Connection PowerQuery; embedded SQL, Stored Procedure call; etc.) each showing template parameter settings for its connection type.

The table is named "tblConnectionConfig" and is visible to VBA as a ListObject by that name as is usual for a Structured Tables. (Clicking the image below will bring up an expanded image.)


When Connections are built or updated, as commanded by a button object shown at bottom left in the preceding figure, the table is processed record-by-record. As each record is chosen, its parameter values are read from the table and set into VBA variables. The values are either string or boolean type, as determined by the use of the parameter in VBA procedure calls to build or configure each Connection, as shown in the VBA code above as needed for the Microsoft procedures called (e.g., "Add2" or the Workbook.Connections.Connection object itself.).

Here's VBA code to take values from the table, or to set a default value if none is specified, and put the the value into a VBA variable. First, self-contained statements to take values directly from the table: an example of a parameter with a string value and another with a boolean value:

Set connectionSheet = thisWorkbook.Worksheets("DataSources")
Set tblConnectionConfig = connectionSheet.ListObjects("tblConnectionConfig")
Set rngHeaderRow = tblConnectionConfig.HeaderRowRange

If WorksheetFunction.Match("Alternate Path", rngHeaderRow, iMatchTypeExact) > 0 Then _
    strAlternatePath = thisListRow.Range.Cells(WorksheetFunction.Match("Alternate Path", _
    rngHeaderRow, iMatchTypeExact)) _
    Else strAlternatePath = ""

If WorksheetFunction.Match("BackgroundQuery", rngHeaderRow, iMatchTypeExact) > 0 Then _
    boolBackgroundQuery = CBool( _
        thisListRow.Range.Cells(WorksheetFunction.Match("BackgroundQuery", _
        rngHeaderRow, iMatchTypeExact))) _
    Else nameIndex = False

'   ...and repeat one of these two statements for each of 26 parameters
'       to be extracted from tblConnectionConfig as each Connection record is processed.

Each of those is a mouthful that will be repeated 26 times. So they can be simplified a bit by first associating a table column number with each column name, instead of using a MATCH function twice to do that job in each case:

' First build a header collection associating each header name with a column number:
' Clear out collection of header names prior to populating
For iCellIndex = cHeaderCollection.Count To 1 Step -1
    cHeaderCollection.Remove iCellIndex
Next iCellIndex
' Then populate the collection of header names
For iCellIndex = 1 To tblConnectionConfig.HeaderRowRange.Count
    cHeaderCollection.Add   Item:=iCellIndex, _
Next iCellIndex

' Then use that header collection to fetch each parameter value into a VBA variable:
If boolExists("Alternate Path", cHeaderCollection) Then _
    strAlternatePath = thisListRow.Range.Cells(cHeaderCollection.Item("Alternate Path")) _
    Else strAlternatePath = ""

If boolExists("BackgroundQuery", cHeaderCollection) Then _
    boolBackgroundQuery = CBool(thisListRow.Range.Cells(cHeaderCollection.Item("BackgroundQuery"))) _
    Else boolBackgroundQuery = False

'   ...and repeat one of the last two statements for each of 26 parameters 
'       to be extracted from tblConnectionConfig as each Connection record is processed.

Connection Parameter List, and Config Variables

Following table describes parameters set on each Connection. Some of the listed parameters control use of others.

- Many of these parameters are defined in Microsoft OLEDB Connection Object (Excel)
- Worksheet Template parameter in Table field value dropdown: choices are provided from choice-list below Table.
- New choices can be added to choice-list.
- Default choices are pre-loaded in each Connection template. Some values are self-localizing expressions.
Sets the name by which Excel/Windows knows this Connection.
Verbal description, shows in Description field of Connection.
Worksheet Build/Update button action:
Build = ConnectionString always from Provider template.
Update = Updates existing ConnectionString if available.
Skip = No Build, Update or Refresh action taken.
Connection Driver from Excel
ACE: Connection to .xls_, .accdb or .txt file.
MSOLEDBSQL (new), SQLOLEDB (legacy): Connection to SQL Server.
PowerQuerySQL, PowerQueryExcel: PowerQuery to SQLServer/MS Access, or to .xls_ file.
Template ConnectonString chosen by Provider field.
Chosen by Provider fleld.
OLEDB for SQL Server, and files for Excel and other.
ODBC for SQL Server, MySQL and other database environments.
PivotSource, mppImport: Data Connections
webQuery,mppQuery: PowerQUERY
PivotSource, webQuery: DATA buttons
mppImport, mppQuery: DATE buttons
xlCmdTable: import Table Name
xlCmdSQL: execute SQL CommandText
default or - execute "SELECT * FROM Table Name" table or Access stored QUERY
Read / Write permissions for ACE provider.
Name of SQL Text to choose from Worksheet tabletblSQL.
SQL String selected from SQL Configuration and Editor
Primary Path
Location of file to QUERY using ACE Provider.
Alternate Path
If Primary Path fails, location of file to QUERY using ACE provider.
Table Name
Depending on Command Type, name of database Table to import or MS Access stored QUERY to execute.
Name of workstation executing Data Connection.
Name of computer running SQL Server.
Name of SQL Server instance to be used from ServerComputerName.
Database in SQL Server SQLInstance to be QUERYed by this Connection.
Concatenated <ServerComputerName>\<SQLInstance> to connect.
Refresh on Excel Refresh All operation. Best to set FALSE: Refreshing un-used or out-of-date Connections causes problems or hangs. Use Refresh buttons provided instead.
Eponymous. Recommend default FALSE.
Timed refresh, like BackgroundQuery. Recommend default 0 minutes.
Eponymous. Maintain connection beyond execution of SQL in this Connection.
Worksheet field. Notes on use or design of this connection.

Connection Parameter Completion and Refresh

Creation of a new Connection listed in the Table sets a subset of parameters, and the Update operation sets the same subset in an exisitng Connection. Following Creation or Update of each listed Connection, the remaining parameters are set in the Connection and it is Refreshed by the following code:

' When Connection defined in tblConnectionConfig exists, either pre-existing or just created, 
' update its params beyond creation-required params

' strRefreshEnable
thisConnection.RefreshWithRefreshAll = boolRefreshAll
If boolExists(thisConnection.Name, colPivotCache) Then  
' Checks if connection is used to drive a pivot. 

    If thisConnection.OLEDBConnection.Refreshing Then _
        ' Turn off ongoing activity if any - it will refresh later
    If thisConnection.OLEDBConnection.EnableRefresh <> boolEnableRefresh Then _
        thisConnection.OLEDBConnection.EnableRefresh = boolEnableRefresh   
        ' May cause error, reason unknown
    thisConnection.OLEDBConnection.BackgroundQuery = boolBackgroundQuery
    thisConnection.OLEDBConnection.MaintainConnection = boolMaintainConnection
    thisConnection.OLEDBConnection.RefreshOnFileOpen = boolRefreshOnOpen
    thisConnection.OLEDBConnection.RefreshPeriod = intRefreshPeriod
End If  ' Connection Exists
' Refresh Connection after build / update. strSkipBuildUpdate does NOT affect Refresh.
' For button RebuildRefreshPivotSourceWebQuery or RefreshPivotSourceWebQuery, refresh PivotSource
' For button RebuildRefreshMPPImportMPPQuery, refresh mppImport
If boolExists(strConnectionName, thisWorkbook.Connections) Then
    Set thisConnection = thisWorkbook.Connections(strConnectionName)
    If Not thisConnection.OLEDBConnection.Refreshing Then 
        ' If it's already refreshing then skip it, else refresh depending on buttons

    End If
End If  ' connection exists

Parameter Replacement in Connection String Templates

There are still some parameters remaining to configure Connections and PowerQuerys - those that must be included within the ConnectionString parameter.

Those Connection Strings are defined by templates of the form:
For Data Connections:
OLEDB; Provider=MSOLEDBSQL; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE; for MSOLEDBSQL to a SQL database, or

OLEDB; Provider=SQLOLEDB; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE; for SQLOLEDB to a SQL database, or

OLEDB; Provider=Microsoft.ACE.OLEDB.12.0; data source=FFFFFF; Mode=MMMMMM; for ACE connection to a source File

For PowerQuerys:
let Source = OleDb.DataSource("provider=PPPPPP;initial catalog=DBDBDB;data source=SSSSSS", [Query="QQQQQQ"]) in Source for connection via OLEDB, or

let Source = Excel.Workbook(File.Contents("FFFFFF"),null,true), SheetImport=Source{[Name="TTTTTT"]}[Data], SheetImportPromoted=Table.PromoteHeaders(SheetImport, [PromoteAllScalars=true]) in SheetImportPromoted for connection to an Excel File worksheet, or

let Source = Sql.Database("SSSSSS", "DBDBDB", [Query="QQQQQQ"]) in Source for connection to a SQL Server database, or

let Source = Access.Database(File.Contents("FFFFFF"), [CreateNavigationProperties=true]), ImportQuery = Source{[Schema="",Item="TQTQTQ"]}[Data] in ImportQuery for connection to an MS Access database.

PowerQuery uses the Power Query M formula language to describe connection and data import.

Instead of fully parsing each string, we are going to find and replace sub-strings. For that, function strReplaceString2 (found in vbaConnect.txt) performs similarly to the VBA Replace function. The parameter values to be updated/replaced may occur within a sub-string terminated by a ";" character, or at the end of a string terminated by a Quote character in which case there is no terminating ";" character. So strReplaceString2 looks for termination by the earlier occurence of one of two possible termination characters.

A collection of core patterns for Connection-Strings and M-Strings is provided at this link.

Be aware that in any #Template, substitution will occur for the first match for a pattern subject to that #Template. This is great for Connection Strings, but a little more complicated for #Templates for M, including SQL inserted into an M string. Substitution does not parse syntax of Connections Strings, but will replace the first pattern match; so /* comments */ or //comments should occur AFTER any code in a SQL or M string that could be replaced.

And again, for this web page, all infrastructure and control logic implementing support functions, buttons, error handling, status updates etc. is NOT SHOWN. The full context for these constructions is contained in vbaConnect.txt which can, as a whole and as available, be placed into a functional Framework like the one described on this website. Please Contact for Details if interested.

Function calls to strReplaceString2 are chained to replace target sub-strings in Connections, like this:

' strConnectionString: Make replacements for Connection String
strConnectionString = strReplaceString2("Data Source=", ";", "*", strConnectionString, _
        ' Replace Data Source
strConnectionString = strReplaceString2("Workstation ID=", ";", "*", strConnectionString, _
        ' Replace Workstation ID
strConnectionString = strReplaceString2("Initial Catalog=", ";", "*", strConnectionString, _
        ' Replace Initial Catalog
strConnectionString = strReplaceString2("Mode=", ";", "*", strConnectionString, strMode)
    'Replace ACE Read Write Mode

...and like this for PowerQuery:

Select Case LCase(strProvider)
    Case "powerqueryoledb"
        strConnectionString = strReplaceString2("provider=", ";", """", strConnectionString, _
                "MSOLEDBSQL")  ' Replace strProvider
        strConnectionString = strReplaceString2("initial catalog=", ";", """", strConnectionString,_
                strDatabase) ' Replace strDatabase
        strConnectionString = strReplaceString2("data source=", ";", """", strConnectionString, _
                strServerName)  ' Replace strServerName
        strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                strCommandText) ' Replace strCommandText
        strConnectionString = updateQueryTableName(strConnectionString, "_Table =", strTableName)
            ' If there's a Table name within strConnectionString, replace it 
            '   by the strTableName parameter.
    Case "powerqueryexcel"
        strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                strConnectionString, strSourceTarget)
        strConnectionString = updateQueryTableName(strConnectionString, "=Source{", strTableName)
            ' If there's a Table name within strConnectionString, replace it 
            '   by the strTableName parameter.
    Case "powerquerysqlserver"
        strConnectionString = strReplaceString2("Sql.Database(""", """,", "*", strConnectionString,_
                strServerName) ' Replace strServerName
        strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                strCommandText) ' Replace strCommandText
        iCurLeft = InStr(1, strConnectionString, ",", vbTextCompare)    
            ' Replacing the Database name is harder because it has no unique delimiter
        iCurLeft = InStr(iCurLeft, strConnectionString, """", vbTextCompare) + 1
        iCurRight = InStr(iCurLeft, strConnectionString, """", vbTextCompare) - 1
        strStringToReplace = Mid(strConnectionString, iCurLeft, iCurRight - iCurLeft + 1)
            ' Capture current-string to replaced within strConnectionString
        If InStr(1, strConnectionString, strStringToReplace, vbTextCompare) > 0 Then 
            ' Check if we found the string to replace
                strConnectionString = Replace(strConnectionString, strStringToReplace, _
                strDatabase, 1, -1, vbTextCompare) 
            ' Match current-string within strConnectionString and replace by make-string
        End If
    Case "powerquerymsaccess"
        strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                strConnectionString, strSourceTarget)
        strConnectionString = updateQueryName(strConnectionString, "Item=""", """", strTableName)   
            ' If there's a Table name within strConnectionString, 
            ' replace it by the strTableName parameter.
End Select

PowerQuery Build, Update, Parameter Completion, and Refresh

The same sequence is applied to PowerQuery: Get Parameters and Pre-Process; Build or Update; Complete Param update; Refresh. Getting and pre-processing Parameters, and update of the Connection String is described above. The coding for Build/Upddate, Param update, and Refresh is a little different. A representation of all elements of the core sequence (Build/Update, Param Completion and Update, Refresh; and without displaying infrastructure) is shown.

Note that when a PowerQUERY QUERY is created and assigned to object thisWBQuery, Excel automatically creates a Connection, named "Query - " & thisWBQuery.Name, and Parameter updates apply to that Connection. Also note, the function boolExists(ByVal thisname As String, ByVal thisCollection As Variant) As Boolean determines if an item named thisName exists in thisCollection, returning boolean TRUE or FALSE.

PowerQuery QUERY Build/Update - Parameter Completion - Parameter Update - Refresh:

If Not boolExists(strConnectionName, thisWorkbook.Queries) Then
    ' Here's the Build action
    Set thisWBQuery = thisWorkbook.Queries.Add(strConnectionName, strConnectionString, _
' If it pre-exists, update it
    ' Update pre-existing QUERY Connection String and Command Text
    Set thisWBQuery = thisWorkbook.Queries(strConnectionName)
    If strSkipBuildUpdate = "Update" Then
        strConnectionString = thisWBQuery.Formula
        ' If Update, start with pre-existing string in QUERY, else re-start from template
    End If

    ' Build the Connection String
    Select Case LCase(strProvider)
        Case "powerqueryoledb"
            strConnectionString = strReplaceString2("provider=", ";", """", strConnectionString, _
                    "MSOLEDBSQL")  ' Replace strProvider
            strConnectionString = strReplaceString2("initial catalog=", ";", """", _
                    strConnectionString, strDatabase) ' Replace strDatabase
            strConnectionString = strReplaceString2("data source=", ";", """", _
                    strConnectionString, strServerName)  ' Replace strServerName
            strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                    strCommandText) ' Replace strCommandText
            strConnectionString = updateQueryTableName(strConnectionString, "_Table =", _
                ' If there's a Table name within strConnectionString, 
                '   replace it by the strTableName parameter.
        Case "powerqueryexcel"
            strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                    strConnectionString, strSourceTarget)
            strConnectionString = updateQueryTableName(strConnectionString, "=Source{", _
                ' If there's a Table name within strConnectionString, 
                '   replace it by the strTableName parameter.
        Case "powerquerysqlserver"
            strConnectionString = strReplaceString2("Sql.Database(""", """,", "*", _
                    strConnectionString, strServerName) ' Replace strServerName
            strConnectionString = strReplaceString2("Query=""", """", "*", strConnectionString, _
                ' Replace strCommandText
            iCurLeft = InStr(1, strConnectionString, ",", vbTextCompare)    
                ' Replacing the Database name is harder because it has no unique delimiter
            iCurLeft = InStr(iCurLeft, strConnectionString, """", vbTextCompare) + 1
            iCurRight = InStr(iCurLeft, strConnectionString, """", vbTextCompare) - 1
            strStringToReplace = Mid(strConnectionString, iCurLeft, iCurRight - iCurLeft + 1) 
                ' Capture current-string to replaced within strConnectionString
            If InStr(1, strConnectionString, strStringToReplace, vbTextCompare) > 0 Then 
                ' Check if we found the string to replace
                strConnectionString = Replace(strConnectionString, strStringToReplace, _
                    strDatabase, 1, -1, vbTextCompare) 
                ' Match current-string within strConnectionString and replace by make-string
            End If
        Case "powerquerymsaccess"
            strConnectionString = strReplaceString2("File.Contents(""", """)", "*", _
                    strConnectionString, strSourceTarget)
            strConnectionString = updateQueryName(strConnectionString, "Item=""", """", _
                ' If there's a Table name within strConnectionString, 
                '   replace it by the strTableName parameter.
    End Select
    thisWBQuery.Formula = strConnectionString
    thisWBQuery.Description = strDescription
End If
' Update common parameters of QUERY Connection
If boolExists("Query - " & thisWBQuery.Name, thisWorkbook.Connections) Then
    Set thisConnection = thisWorkbook.Connections("Query - " & thisWBQuery.Name)
    If thisConnection.OLEDBConnection.Refreshing Then _
    ' Cancel refresh if active
    thisConnection.RefreshWithRefreshAll = boolRefreshAll
    If (thisConnection.Type = xlConnectionTypeOLEDB And _
            strConnectionType = "xlConnectionTypeOLEDB") Then

        thisConnection.OLEDBConnection.EnableRefresh = boolEnableRefresh
        thisConnection.OLEDBConnection.BackgroundQuery = boolBackgroundQuery
        thisConnection.OLEDBConnection.MaintainConnection = boolMaintainConnection
        thisConnection.OLEDBConnection.RefreshOnFileOpen = boolRefreshOnOpen
        thisConnection.OLEDBConnection.RefreshPeriod = intRefreshPeriod
    End If

End If

If boolExists(strConnectionName, thisWorkbook.Queries) Then
' Check if QUERY exists - created or pre-existing
    Set thisWBQuery = thisWorkbook.Queries(strConnectionName)
        If boolExists("Query - " & thisWBQuery.Name, thisWorkbook.Connections) Then
            Set thisConnection = thisWorkbook.Connections("Query - " & thisWBQuery.Name)
            If Not thisConnection.OLEDBConnection.Refreshing Then
            ' If it's already refreshing let it go
                thisConnection.OLEDBConnection.BackgroundQuery = boolBackgroundQuery
            End If
        End If  ' Refresh Operation
    End If  ' REFRESH Button check
End If  ' QUERY existence check

Cleanup of Un-Used Connections and PowerQuerys

When all line items in tblConnectionConifg defining Connections have been processed, connections contained within the Workbook that ARE NOT defined in that table, are deleted to avoid accumulation of dysfunctional Connections. For PowerQuery QUERYs, both QUERY and its assigned Connection must be deleted. Presumably deleted Connections and QUERYs could be re-constructed by re-adding their definitions to the table, or changing their Skip parameter to Build.

In the code below, colConnectionList and colWBQueryList were constructed, a collection of named Connectons and QUERYs intended to exist, as the Build/Update/Parameter/Refresh cycle progressed, in code not shown here.

Connection and PowerQuery QUERY Cleanup:

' Once defined connections are handled,
'  delete existing Connections not defined in tblConnectionConfig
For Each thisConnection In thisWorkbook.Connections
    ' Connections defined in tblConnectionConfig were saved into colConnectionList
    ' Delete connection if it's not in colConnectionList,
    '   as long as its name doesn't begin with "Query -"

    If Not boolExists(thisConnection.Name, colConnectionList) Then
        If Not (Left(thisConnection.Name, 8) = "Query - ") Then
            If thisConnection.OLEDBConnection.Refreshing Then _
            ' Stop refresh before deleting it

        End If
    End If
Next thisConnection
' Once defined QUERYs are handled, delete existing QUERYs not defined in tblConnectionConfig
For Each thisWBQuery In thisWorkbook.Queries

    ' Queries defined in tblConnectionConfig were saved into colWBQueryList
    If Not boolExists(thisWBQuery.Name, colWBQueryList) Then
        ' Delete the Connection
        thisWorkbook.Connections("Query - " & thisWBQuery.Name).OLEDBConnection.CancelRefresh
        ' Stop any ongoing refresh before deleting

        thisWorkbook.Connections("Query - " & thisWBQuery.Name).Delete
        ' Then delete the associated QUERY
        If boolExists("Query - " & thisWBQuery.Name, thisWorkbook.Connections) Then
        End If
    End If
Next thisWBQuery

Error Handling

  • Error Prevention
    • Explicit Typing, Variable and Function name Type label
    • Structured Code
    • "Please" Construction: Before any operation, check object existence, checks coherence of button command to object type.
  • Error Catching and Reporting
    • Each operation structured as
    • err.clear
          ' Captures intent, displays intent in Status Bar
      on error resume next
      if err <> 0 the 
          ' Creates error message with captured state and error detail
          ' Aggregates error message into log
          ' Sets "Skip" state to avoid furter Operations on this object.
    • On error, creates and accumulates error log.
    • On completion of button command, displays dialogue box listing all errors and details.

Configured-SQL Editor, and Configuration Variables

Most Connections will have SQL embedded, so it's convenient to build SQL strings within Excel. Even more than convenient, in Excel you can use the Common Configuration Variables specifying file names and paths, localized as needed; you can reference other Stored Procedures and Linked Servers by name, whose SQL strings for CREATion are themselves created within Excel (referencing local variables etc.), to EXEC those SPs from within SQL strings created subsequently. In short, Excel provides a computational infrastructure useful especially for creating SQL strings that are related to each other and which share Source Data entities and paths to their locations.

The Framework described on this website provides Common Configuration Variables that can provide some leverage, consistency, and robustness to solutions built on it. Those variables include Source Data filenames and paths; database location and instance names; Linked Server names Export location path; name of the Framework workbook, host processor executing the Framework file. Where useful, those variables provide a localized value which may be a function providing such information; and are provided as Named Ranges to be referenced by name. They could be overwritten as appropriate, although that complicates distribution of updates.

tblLocalizationConfig Common Configuration Variables are used with PowerQuery M primarily in setup of Connection Strings to data sources. For SQL with MS Access, SQL Server and PowerQuery, they are more extensively used, in Connection Strings and also to set up Linked Servers and OPENROWSET QUERYs.

The Configured SQL Editor part of the Framework is conceptually straightforward. Each SQL string is represented in a column within Structured Table tblSQL. The name of the column is most useful if it indicates the functionality of the SQL contained in that column. (The section on DROP and CREATE just below describes how SQL strings can be exported .sql files named for their defining column, for use in SSMS and VS-SSDT.) Each cell in the Table takes normal Excel content which can include expressions of any kind, or calls to VBA etc., and which frequently are used to concatenate, parse or lookup other variables to create functional SQL.

Common Configuration Variables are provided in templates including the PowerOpI Framework Environment, local Workstation and Workbook. Templates are provided for Data Sources as Servers, Files, and Linked Servers. A note on OPENROWSET: Although OPENROWSET pulls information from a File data source, it requires all the same information as is needed for a Linked Server even though it differs syntactically and logistically from a Linked Server. When configuring Common Configuration Variables for OPENROWSET, use the Linked Server template which besides the file name and path, will provide named Variables for the Excel Version, ACE Provider and Version for use in OPENROWSET QUERYs just as for Linked Server CREATE QUERYs. There are examples of this in the PowerOpI Template.

When updates are made in tblConnectionConfig that change locations of files using SQL from tblSQL, then Stored Procedures in affected databases and Linked Servers, all of which are saved within SQL Server, must be updated by exporting and running configured QUERYs for SMS/VS-SSIS/VS Code; and connections into MS Access and MS Visio, stored in .accdb and .vsdm files for those application, must be updated as well from within those applications. A sequence for these updates is described below.

Using Common Configuration Variables in tblSQL and tblConnection

As shown in the image below, cells containing simple "simple text" are left un-formatted, and cells containing Excel expressions to concatenate etc. are formatted as bold-blue-italic. SQL Keywords are capitalized.

The Connection Configuration table tblConnectionConfig contains a field specifying SQLName, identifying the name of a column in tblSQL containing SQL to be embedded for each Connection. Using that, field CommandText fetches the SQL String which will be embedded into the Connection on a Build or Update command from one of the Buttons.

Field CommandText uses Excel function XLOOKUP to locate the column in tblSQL named by field SQLName, and uses Excel function TextJoin to concatenate all non-blank cells in that column, delimiting each by a space and line-feed (ignored by SQL) to create a string containing the SQL to be embedded into each Connection or to be exported to a .sql file.

Thus these SQL-Strings are invoked by name (at the top of each column in tblSQL), selected by a pull-down menu in tblConnectionConfig field named SQLName, for each Connection configured in tblConnectionConfig. The value of the SQL-String is then shown for each Connection, in tblConnectionConfig field CommandText.

Configured-String Editor for Connection Strings and PowerQuery "M" Strings

It would be useful to have an editor to create, configure, and update Connection Strings, and PowerQuery "M" strings. The PowerOpI Framework workbook provides a Configured-String Editor for this. This Editor is fairly basic, but provides more functionality than would be provided by simply stating fixed string values as Connection-Templates and M-Templates.

For M, the PowerQuery Editor in Excel is much more funtional. However, it produces values in its Queries and Connections that are read-only in the User Interface except within the PowerQuery Editor; and that is a manual proposition. The intent of the PowerOpI Framework is to provide a way to create and manage access and use of Excel with database environments, in a way that handles numerous connections with automation so that it can be provided to a distributed set of users.

It is not fully developed and tested, but it is nevertheless intended that a user could go back and forth using the PowerQuery Editor, and the Configured-String Editor, together with the automation of the PowerOpI Framework - the PowerOpI Framework "Update" capability will ultimately provide this capability.

  • Limitations to be aware of:
  • Only one source-function statement (e.g., M functions Excel.Workbook, Sql.Database, Access.Database, OleDb.DataSource, Excel.CurrentWorkbook), or Connection String, is supported FOR SUBSTITUTION IN TEMPLATES in each M QUERY. String-substitution on connection Build makes a single pass through Provider strings in tblConnectionConfig, replacing QUERY variables in each as invoked by the Provider Template class, by corresponding values for each Provider from tblConnectionConfig. So only the first Source statement will get the replacements. You can still use Common Configutation Variables embedded in the QUERY definitions in tblSQL and tblConnection, to configure as many Source and other statements in each QUERY.
  • Replacement is based on string-search and string-substitution, and the QUERY is not parsed, so //comments, /* comments */ and other statements can cause incorrect substitution. Therefore, for correct substitution, the target statement for replacement must be the first encountered in the string that matches the Template pattern. All other statement in the QUERY, including comments, must occur later in the QUERY. Substitutions will be correct or incorrect, depending on order of #template key words found in QUERY.
  • Substitution in #Templates should be used for simple QUERYs and Connection Strings, typically a single statement invoking a Connection String or simple M statement. More complex M QUERYs, and M QUERYs with inserted SQL, should be sourced as M statements (and SQL statements) constructed using Common Configuration Variables for localization. These are then subject to M syntax (and SQL syntax) rules. The substitution engine does not parse Connection Strings for /* comments */ or //comments so if they are present in a connection string using a #Template, the comments should appear later in the string than any substring that may be substituted.

The Templates for both Connection-Strings and PowerQuery M-Strings discussed above, are provided via the Configured-String Editor. Connection strings could be added to the Template (ODBC comes to mind), and terms can be updated. Certainly M has more capability than the basic connectivity provided by the M-Template strings provided so there is lots of opportunity there.



First are shown Configuration-Templates in the Configured-String Editor each named as its Provider; followed by M-Templates named to indicate Data Source type.

As in the Configured-SQL Editor, each column in Configured-String Editor source tblConnection represents a String whose Name is the Title of that column. Within each column, each cell represents a term in the Connection- or M-String. Each cell can be created by simple text, or by concatenating values, such as references to Common Configuration Variables; or evaluating any other Excel expression that results in a string value; to specify files, paths, providers etc. Whole terms can be shown for inclusion or hidden for exclusion within the resulting string.

Construction of a data-access clause in an M statement, fetching data for a QUERY from a file specified using a Common Configuration Variable:


These Connection-Strings / M-Strings are invoked by name (at the top of each column in tblConnection), selected by a pull-down menu in the field named Provider, for each Connection configured in tblConnectionConfig. The value of the String selected is then shown for each Connection in tblConnectionConfig field ConnectionString.

Connections specified in tblConnectionConfig specify their Provider connection-string by a UniqueName or a #TemplateName; Provider connection-strings themselves are detailed in tblConnection. Provider #Templates are used to modify a Provider connection-string to use values from tblConnectionConfig when a Connection is Built or Updated. The connection-string for a Provider identified by a UniqueName alone will be passed directly into the Connection as it is Built or Updated. A UniqueName may invoke Template field-substitution by post-pending a #TemplateName, as UniqueName#TemplateName. Connection-Strings that can be used as Templates are named with a "#" as the first character in the name, as #TemplateName. Each #TemplateName determines a set of fields within the connection string that will be updated and overridden when a connection based on it is Built or Updated. Templates exist for the following for Excel Data Connections: #Microsoft.ACE.OLEDB.12.0, #Microsoft.ACE.OLEDB.16.0, #MSOLEDBSQL, and #SQLOLEDB; and for PowerQuery M strings: #OleDb_DataSource, #Excel_Workbook, #Sql_Database, #Access_Database, and #Table_Join following the naming convention for the functions used in M for these operations: OleDb.DataSource, Excel.Workbook, Sql.Database, Access.Database, and Table.Join.

In the figures above illustrating some of the templates, note the values highlighted in red in the Template strings. These values of these parameters will be overridden by values from fields in tblConnectionConfig at the time each Connection or M-String is built. The Connection builder uses the #TemplateName part of the Provider connection-string name, to determine to determine which parameters to update as determined by the Template. When Updated from pre-exisitng connection-strings, fields not found in an invoked #Templates would not be overidden, and would retain values placed into the M-String in the Configured-String Editor or by the PowerQuery Editor.

The Configured-String Editor concatenates each non-blank cell in a column, delimiting each cell entry by a space and linefeed. The result is a readable string in SSMS VS-SSDT or WordPad etc. You must stay within rules of "M" regarding line breaks . Also, note that indentation of text in a cell is a visual effect only, not affecting the cell value: if a space is needed syntactically, an actual space character must be inserted properly in the string value.

When updates are made in tblConnectionConfig that change locations of files using SQL from tblSQL, then Stored Procedures in affected databases and Linked Servers, all of which are saved within SQL Server, must be updated by exporting and running configured QUERYs for SMS/VS-SSIS/VS Code; and connections into MS Access and MS Visio, stored in .accdb and .vsdm files for those application, must be updated as well from within those applications. A sequence for these updates is described below.

Again, the Configured-String Editor is pretty basic right now, but it provides infrastructure that may become more valuable as more functional use of M is explored with the PowerOpI Framework and its automation.


One of the recent changes to Excel eliminated capability to CREATE or DROP database entities such as Linked Servers, Stored Procedures, or Tables. That was useful for creating those entities for use with QUERYs interacting with Source Data and Reports in Excel, especially where those QUERYs are related to each other and can use Common Configuration Variables; obviously that function is needed somewhere.

That function is available in SQL Server Management Studio (SSMS) and in Visual Studio (VS-SSDT). Both of those can execute SQL stored in .sql files, so functionality is included in the Framework described on this website, to export SQL created in its Configured SQL Editor to such .sql files.

A Button is provided, Execute tblSQL #Directives, to do the export. The location to be exported to is defined in Common Configuration Variable ExportTo, and is commonly set to the directory from which SSMS and VS-SSDT can be directed to open .sql files in their Open File... dialogues.

The Framework creates a directory structure, if needed, in the SSMS/VS-SSDT/VSCode file directory specified as the ExportTo Variable, and exports .sql files there. The default ExportTo value is C:\Users\WorkstationLocalLogin\Documents\SQL Server Management Studio[\ExportToSubstring]\WorkbookCurrentDirectory\WorkbookName (without extension)\Variables Tab Profile. (optional fields in brackets). The result is a path specification like C:\Users\Owner\Documents\SQL Server Management Studio\Merge\TestOLEDB\ProfileLocal. The default structure resembles the directory structure to the PowerOpI Framework file exporting the SQL, appending the Variable profile specifying file locations. The default can of course be overridden in Excel tblLocalizationConfig. The exported files can then be used in SSMS/VS-SSDT/VSCode to manage LSs, SPs, and Tables. This structure supports multiple Framework instances on a computer, and multiple databases and projects within a SQL Server instance.

A separate .sql file is exported, overwritten if needed, to that directory, for each column in tblSQL containing a Directive string formatted as #VBTEXTJOIN: EXPORT; or #VBTEXTJOIN: EXPORTWITH domainName;. Note the hashtag and colon character , and closing semi-colon. It is recommended that a Directive String be placed within a SQL Comment so that SQL will ignore it, so usually a cell in the SQL string definition column contains /* #VBTEXTJOIN: EXPORT; */ or /* #VBTEXTJOIN: EXPORTWITH dbo; */ (or other domain name you specify) .

So files are exported that cannot be executed from Excel, and must be executed via SSMS or VS-SSDT - certainly DROP and CREATE. Those files when opened in SSMS/VS-SSDT must be linked to the intended database within the SQL Server instance. The SQL itself must conform to the GO batch construction of SSMS. The Framework shows an example in which a Stored Procedure configuring Microsoft.ACE.OLEDB.12.0 is EXECed prior to CREATEing a Linked Server; that EXEC must be separated within a batch step by GO. Note that a SQL string intended to be embedded into an Excel Connection rather than be executed by SSMS/VS-SSDT (e.g., to EXEC a Stored Procedure from Excel, that was created by SQL executed from a file exported for SSMS/VS-SSDT), would not contain the GO syntax.

When updates are made in tblConnectionConfig that change locations of files using SQL from tblSQL, then Stored Procedures in affected databases and Linked Servers, all of which are saved within SQL Server, must be updated by exporting and running configured QUERYs for SMS/VS-SSIS/VS Code; and connections into MS Access and MS Visio, stored in .accdb and .vsdm files for those application, must be updated as well from within those applications. A sequence for these updates is described below.

Organizing Storage and Permissions by Project and Domain

  • A server instance may include several domains each with different permissions among different databases in a SQL Server Instance, particularly in a development environment, and this affects naming and storage organization for Stored Procedures and SSMS procedures. This is supported using Common Configuration Variables domain and project. Domains, Roles, and Permissions in SQL Server are complex, and some good references and code are shown at Permissions and Membership on this website.

  • Domain examples could include dbo (database owner) or guest; and others can be created to organize code and manage permissions within a database you might set up for a project. There is a Common Configuration Variable named domain that sets a domain value to be used when CREATING or EXECing a Stored Procedure in tblSQL to define a stored procedure specific to a domain, for example named dbo.spBuildPlanLS_Proto. Other procedures can also append the domain variable in SQL statements to refer to domain-specific procedures.

  • A Domain would be specified within Excel tblSQL by concatenating a variable from tblLocalizationConfig defining domains used for each profile (domain, domain2,... or any you might add to the table). Such a domain specification is optional (just leave the variable value blank if unused). However, you don't want a superfluous "dot" connector added within a QUERY or file name if the domain is left blank. For this reason, a function is provided by the PowerOpI tool framework called strAddADotIfNotNull(string,connector) to be used to insert such an optional value. Here is an example showing how to use this to add an optional domain specification: ="CREATE PROCEDURE " & strAddADotIfNotNull(domain,".") & "spUnionGroup" & LinkedServer1DataSourceFIleRaw & " AS". There are numerous such examples provided in tblSQL. And you can avoid any complexity by just writing SQL in that table, without using the Common Configuration Variables, although those wouldn't re-configure by the automation provided by the Variables.

  • A Linked Server is a Server Entity, but in a development environment a named LS may need to point to different files in different projects, so that could be reflected by creating linked servers with multi-part names such as LS_ProjectName_Proto, to differentiate and organize Linked Servers among several projects within a SQL Server Instance.

  • We can export SQL files be executed on SSMS that are specific to a domain and project. A second directive is supported, called in an SP definition in tblSQL as /* #VBTEXTJOIN: EXPORTWITH domainName; */. For this Directive, the command is EXPORTWITH; and the string domainName specifies the domain intended. The result of using this directive is that an exported file will include the domain name in the active profile in Common Configuration Variables within the name of the exported file, such as EXEC_dbo.spBuildPlanLS_ToolSource.sql. Furthermore, files are exported to a sub-directory named for the project to which they apply.

  • So an SP defined in a tblSQL column named EXEC_spUnionGroupToolSource and including directive /* #VBTEXTJOIN: EXPORTWITH dbo; */ would be exported to a file EXEC_dbo.spUnionGoupToolSource, including the domain name as shown. So that multiple domains can export and use SPs specific to each domain. The rule for using the EXPORTWITH domainName keyword is to include that keywork in any procedure (named as DROP_, EXEC_, CONFIG_, CREATE_ with included underscore character) if the name of the entity CREATEd includes the domain name variable in its name. Then the entity, any SPs referencing it, and SSMS directive files can include the domain name as shown, to point to the entity specific to the intended domain.

Naming, Saving, and Organization of Stored Procedures, Exported SQL, and Linked Servers

  • SQL procedures exported via Directives button, are stored in the directory specified by the ExportTo Common Configuration Variable in each tab Variables profile. Typically that variable points to a directory from which SSMS/VS/VSCode will open files. It specifies a sub-directory, named after the PowerOpI file, in which the Directive will save the SQL procedures for that profile. SQL Procedures can include a domain name (Common Configuration Variable for each Variable profile) in the filename of the saved procedure to indicate its intended scope, by using the /* #VBTEXTJOIN: EXPORTWITHDOMAIN; */ construct. So there is a directory created for each specified project, containing exported SQL files for SSMS, each file named including the name of the domain to which it applies.

  • SPs CREATEd in tblSQL, in procedures exported as above and executed in SSMS/VS/VSCode. SPs when CREATEd are saved within a database, to <databasename>\Programmabililty\Stored Procedures\ where <databasename> is selected manually in SSMS/VS/VSCode, or specified by a statement USE <databasename>; in the CREATion procedure, where <databasename> can be specified by a Common Configuration Variable such as Server1Database1 shown in tab Variables. SQL Procedures can concatenate a domain name (Common Configuration Variable for each Variable profile) at front of the SP name in the Stored Procedures\ directory, to indicate its intended scope.

  • Linked Server organization and naming. Linked Servers (LS) when CREATEd are saved to Server Objects\Linked Servers\. LS are associated with files linked, and not directly associated by SQL Server with any particular database or domain. Within the Linked Servers directory, they can be organized by utilizing multi-part names, constructed using Common Configuration Variables to indicate scope such as filename linked, and possibly indicating function of data contained in the file.

Using PowerQuery M Formula Language

The Power Query M Formula Language seems to be the real power behind Power Query. The Power Query UI basically creates M to perform tasks specified in the UI. Even more capabililty can be accessed by going to M directly, via the Advanced Editor button in the Power Query UI or by creating a Blank Query from the Excel UI or Power BI Desktop UI.

Power Query M can be developed, with grammar coloring and Intellisense, in Excel and in PowerBI Desktop; and Extensions are available for both Microsoft Visual Studio Code (VSCode) and Microsoft Visual Studio.

PowerQuery M is handled by the PowerOpI Framework, with M source code in the Configured-String Editor. M sourcecode in the Power OpI Framework in Excel can be created using the configuration variables available in the Framework, keeping things coordinated with other Queries, with SQL and embedded QUERYs, with Linked Servers, OPENROWSET, and source table names among many examples. M-Strings are chosen for use in Power Query Queries in the tblConnectionConfig Table, for LinkTypes webQuery and mppQuery, and select among Provider strings in the Configured-String Editor source table tblConnection. M-Strings are set into the WorkbookQuery.Formula property. In the Excel PowerQuery User Interface, these strings could be incorporated into QUERYs using the PowerQuery Advanced Editor.

A Query created in the Power Query UI can be updated from the Power OpI Framework, and vice-versa. Such a Query can be set to "Skip" in the Power OpI tblConnectionConfig to avoid updates.

Creating PowerQuery Query to JOIN using M Formula Language:
Excel Menu "Data" > Get Data > From Other Sources > Blank Query > Tab "Home" > Advanced Editor >
and then paste in an M "LET ... IN" statement from
MTemplatesMStrings.txt or
below in this MStrings section of this website
and edit the statment.
Then Save and Load... etc.

Reference and Training on M: Written documentation of M is comparatively scarce. Much available documentaion centers on the Power Query UI and DAX. There is quite a bit of material on YouTube though, although in usual fashion info on M is scattered across many of them.

M executes operations from the local computer Client, including Queries with JOINs. Query sourcecode is visible to the local Client computer which could be a disadvantage for sustaining and security as compared with Stored Procedures in other environments. Although the Query is initiated from the Client, Power Query delegates work to the server using a method called "Query Folding".

Described in the Learning references above, the M Language constructs use a "LET ... IN..." structure. In each example below, a LET clause performs a sequence of table sourcing operations to get access to the five example Tables. Then a series of JOINs of two Tables at a time, followed by adding Columns to calculate aggregated Quantity and Cost. The IN clause states which of the Variables is output by the M-String. The Source methods differ among these examples, but all use the same methods for JOINs, Columns and Output.

M-Strings are shown above in the Configured-String Editor section that push a SQL Query for execution by SQL Server or by MS Access, using either SQL embedded in the QUERY or invoking a Stored Procedure within the database environment itself, placed there by a CREATE operation as discussed just above. This is an easy introduction to using PowerQuery primarily as a source data access vehicle.

M is much more powerful than just EXEC'ing a SQL SP, and can create, traverse, JOIN, and much more, over complex data structures including the Table and Record structures illustrated in the examples below that are useful for structured data problems similar to the Project Management Logistics problem described on this website. Chris Webb's video Power Query Beyond the Basics illustrates broader capabilities.

JOIN Excel Tables from a separate Workbook, could of course be directed to Tables in the local Workbook. This M code replicates function the JOIN shown in several illustrations on this site, JOINing Tables within Excel using PowerQuery without use of a Database environment.


    Source=Excel.Workbook(File.Contents("C:\PowerOpI\Merge\ToolSource.xlsm"), null, true),


    AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter),
    ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter),

    ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]),
    ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost])


JOIN Tables from Excel, picked up from a Linked Server in SQL Server. Linked Servers are a SQL Server concept so SQL is used to navigate them from PowerQuery. The JOIN is executed in PowerQuery, not in SQL Server. For comparison, this example again replicates the Query commonly used as an example on this website. Make sure Excel doesn't auto-correct the "..." construct by a single ellipsis character! Also note, line breaks have been inserted here to make the display fit on the website pagewidth; in practice, linefeeds can occur within SQL statements and Connection String but not within the M content.


    xlAlloc=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngAlloc"]),
    xlBOM=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngBOM"]),
    xlDates=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngDates"]),
    xlBuilds=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngBuilds"]),
    xlElements=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS", 
        [Query="SELECT * FROM LS_Proto...rngElements"]),

    AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter),
    ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter),

    ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]),
    ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost])


JOIN Tables from Excel, picked up using OPENROWSET in SQL Server. Again, OPENROWSET is a SQL Server concept so SQL is used to navigate it from PowerQuery. This example again replicates the Query commonly used as an example on this website, for comparison. Again note, line breaks have been inserted here to make the display fit on the website pagewidth; in practice, linefeeds can occur within SQL statements and Connection String but not within the M content.


    FileSource = "Provider=MSOLEDBSQL; initial catalog=ExcelProto; 
        data source=TRILITHIUM\SQLEXPRESS",
    xlAlloc = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngAlloc)"),
    xlBOM = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngBOM)"),
    xlDates = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngDates)"),
    xlBuilds = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngBuilds)"),
    xlElements = OleDb.Query(FileSource,"SELECT * FROM 
        OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
        Database=C:\PowerOpI\Merge\ToolSource.xlsm;HDR=YES', rngElements)"),
    AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), 
    ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), 
    ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]), 
    ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost]) 

Once a PowerQuery is created, embedding one of these M-Strings, it is assigned to drive a Report such as a PivotTable, using the method for PowerQuery in the following Section covering Driving Pivot Tables.

Notice that each of these QUERYs shows Table/Named Range names, and field names linking them, thereby exposing database structure; and one of the QUERYs shows fully-qualified file paths. Setup of a Linked Server would also expose paths. The text for each of these QUERYs, since it is executed in M at each Client endpoint, resides within each Client and can be made visible there.

For the distributed-user Use Case I generally work within, it is my preference to "hide" File Paths, Table/Range Names, and linking Field names as much as possible from Client visibility. That can be accomplished to varying degree by using Linked Servers within the shared database environment, or even more by putting QUERY logic within Shared Procedures in the database environment, and having each Client make EXEC calls to those rather than executing QUERY logic locally. This is especially true for use of OPENQUERY.

This improves security and system integrity by hiding structure and physical locations, and simultaneously simplifies maintenance - an update to a Shared Procedure in a common database environment is available immediately to all Clients wherever they may be, without making changes to each Client instance. Recall my suggestion that QUERYs, excepting Client EXEC QUERYs driving Client endpoint reports, be authored and managed in a "Control" instance of the PowerOpI Framework, used with one or more Data Source instances available to Source Data table owners, and with multiple distributed Client instances.

In my view, Client-local QUERY execution with full visibliity of QUERY structures within a Client is best-suited to individual-user/Data-Analyst cases, than to a Distributed multi-user Program Management or similar case. Individuals within the Multi-User case may be exceptions, for example Finance management or Dashboard upkeep may make use of specialized Client QUERY configurations.

A Note on Names used in SQL and PowerQuery M Queries

You may need to pull data from an Excel Workbook that you do not control, and that does not provide Named Ranges or Shadow Ranges to expose the data to MS Access or to SQL Server. However, the Database Environment can see each full Worksheet within the Workbook, which it can interpret to provide Tables.

  • For SQL Server or MS Access, using Microsoft.ACE.OLEDB.XX.0 to pick up a Worksheet from Excel, use a name formed as [Name$] for a Worksheet named Name.

  • In most other situations you can directly use the form name to specify a Named Range, Shadow Range, or MS Access stored QUERY, to MS Access, SQL Server or PowerQuery M; and to specify an Excel Structured Table to PowerQuery M.

  • Avoid duplicating any name, among a Structured Range or Structured Table, with a Worksheet. Excel won't let you duplicate a name between a Structured Range and Structured Table, even if it initially appears to work. But nothing prevents you from naming a Worksheet the same as a Structured Range, and Excel takes evasive action by silently assigning sequential names in the order they are created - and this is not visible in any way inside Excel.

    If a Named Range is named Name and after that you name a Worksheet also as Name it will show that as the name in all aspects of the Excel User Interface; but an internal "silent" name will be assigned such as Name1, numbered in the order created, by which the Worksheet is made visible externally to ACE to the database, even though both the Named Range and the Worksheet tab will display Name in the UI.

    Silent names will be assigned in such a situation sequentially as items are created or re-named to duplicate: Name, Name1, Name2, Name3 etc. Items retain these names even if one of them is deleted (such as, Worksheet Name2 is deleted but the other items retain their silent names as assigned when they were created. The net is that in the case of duplicate names you don't directly control the object name made visible to the database. The moral is: Don't duplicate names in Excel even where it lets you.

  • A case of this that you cannot avoid is export to an Excel Workbook of Task Data from MS Project. MS Project creates a Named Range and a Worksheet, naming them with a duplicate name, which Excel modifies sequentially so that the Named Range is named Name, the Worksheet tab shows Name, but the Worksheet name visible to a Database via ACE is probably like Name1 or Name2. When you attempt to connect to the worksheet, you must find the sequential name of the Worksheet that is visible to ACE - see the next paragraph. It seems repeatable as Name2 - so when you've used the code on this website to export from MS Project, to access tab Task_Table1 externally use name Task_Table12.

    Here's the foolproof way to find the "silent name" you can use that contains the data of the full worksheet: These "silent names" don't show on any Excel User Interface elements - not on Tabs, nor in Excel "Formulas" Menu > Name Manager, nor on Tab identifiers in the VBA Development Environment object explorer. But you CAN find the name to use, via Excel "Data" Menu > Get & Transform Data > Get Data > From File > From Workbook > (choose Excel workbook from explorer presented) > Navigator. Available Names pointing to data are shown in the explorer at the left of the panel, and each can be previewed by selecting it. Obviously, you will want to specify the one that shows all the data you're intending to use, plus headers, in the Preview window.

  • This is interpreted from observed behavior. I don't have inside knowledge of Excel's implementation but have encountered and investigated these situations. Don't duplicate names, be aware of the duplicate-naming anomaly on MS Project Task export, and be aware of the forms of that name you need to use in the situations described.

Driving Pivot Tables Pivot Charts, Tables, Load To... from Connections and QUERYs

When a Connection or Query is created by VBA using tblConnectionConfig, it initally isn't connected to drive a PivotTable, PivotChart or Structured Table. The trick is to get it assigned to such a report, and in such a way that it can be subsequently updated by a Build or Update button operation.

Templates for Project Source Data tables and Dates, SQL Structures, and Reporting

The PowerOpI Framework file contains numerous examples that can be used as Templates to be applied to specific projects.

  • Source Data Tables: Each Excel table used as Source Data should have the Shadow Range event handler VBA in the code page for the Worksheet Tab that the table is on, in the Source Data workbook. The Source Data workbook should also contain QUERYs and resulting Imported Date Tables, or other Imported Tables, to be used in preliminary calculations (PurchDate, PayDate, Cap/Expense etc.) and then in JOINs in Structured Procedures called from the Reports workbook to drive resulting Reports.
    • Allocation: Source Data table shows material usage by Organization, Purpose, System Name, Owner, and Configuration. Each item allocated also identifies the Purchase Request by which the item is acquired. Setup of complex keys to connect among these Tables, via database JOINs and Excel Lookups, is also illustrated.
    • Bill of Materials (BOM): Source Data table shows product structure representation including systems, assemblies, sub-assemblies, and elements; through build sequence, Capital/Expense determination, rollup of element Cost and Extended Cost, and setup of database keys for BOM explosion. tblBOM can use element aliasing to determine some item quantities.
    • Dates: Illustrates Table import, TaskMilestone key creation, association and use, Date JOINs and usage in other Source Data tables, calculations for OrderDate, PayDate, multi-level integration, calculations added to imported Table, and imported table SelectionChange event handler. tblDates can use element aliasing to determine some item dependent milestone dates.

      In a Program Management application, the Dates table presented to the database environment for JOINs is derived from a table created by import of Tasks data exported from Microsoft Project. Discussion of that export and import, and VBA Code for the export from MS Project, are provided elsewhere on this site.

      The operational flow consists of, first, creating and maintaining the project task structure within MS Project, with task sequencing determined by links reflecting dependencies and deliverables. Also within Project, a task Key field is populated, identifying tasks and milestones to be tied to logistics and reporting.

      Then, when its owner of the MS Project structure is satisfied to provide an update, the Task data is exported from MS Project to an intermediate .xlsx file. In the Power OpI Framework, the exported Task Data .xlsx file is imported into a Structured Table in the Source Data workbook using the Refresh or Update DATES buttons and associated QUERY(s). From there, Task Date data is extracted from the imported Structured Table, into the Dates Table via INDEX/MATCH, XLOOKUP or FILTER/LET constructs in that Table, using the Keys in the imported Table. This also implements Task Aliasing.

      Finally, once the Dates table has been updated to contain the current Task dates, it is JOINed to BOM items to quantify timing for item Logistics using the Update or Refresh DATA buttons the Report workbooks. These buttons are associated with QUERYs or Tables stored in the database environments or local to the Report workbooks. The Dates table is an Excel Structured Table so it is conveniently available for JOINs by Power Query. That table is also provided with a Shadow Range to make it visible for JOINS in SQL Server or MS Access database environments.

    • Builds: Source Data table shows another re-structured table representing Task dates, created from the imported Tasks table, de-normalized to support a special report structure.
    • Element Data: Source Data table illustrates useful element data: Supplier info, configuration data and references, accounting rule parameters, prototype cost escalation rule.
    • Purchase Requests: Each allocated item is acquired via a Supply Chain process, initiated by a Purchase Request resulting from PM project scheduling and logistics planning, sequenced through RFQ, Quote, Approvals, Purchase Order, ETA, Delivery, and Staging. Those steps are identified by various tracking numbers and dates in the sequence through suppliers and shippers. The Purchase Request Table reflects tracking of this process, for coordination of the purchasing process with realization of build, integration and distribution processes as items are received.
    • Normalization: Illustrates un-normalized Source Data table (familiar for human interaction across an organization) transformation to normalized (best for technical use in a database), for JOIN with other Source Data tables. Shows addition of calculated fields to transformed table, with dynamic fit to normalized table on refresh.
  • SQL, and M, Structures for Connections, QUERYs, and SSMS/VS-SSDT: This SQL will reside in the Configured-SQL Editor table in Control Workbook if there is one or else in the Reports workbooks. SQL used to call Stored Procedures will reside in the Reports Workbook, to be incorporated into connections in that workbook that drive reports.
    • SQL for Connections and QUERYs to MS Access and SQL Server from Excel, SSMS, and VS-SSDT.
    • SQL to create and Use Linked Server from Excel Connection/QUERY and SSMS/VS-SSDT: Database use of Source Data contained in Excel files.
    • OPENROWSET: SQL to use of Source Data from Excel files, alternative to Linked Servers.
    • SQL to create Stored Procedures from Excel Connection/QUERY and SSMS/VS-SSDT. These create QUERYs with JOINs and other SQL, that will invoke Linked Servers or OPENROWSET to access Source Data from Excel tables. These may also call other Stored Procedures.
    • SQL to be embedded in Excel Connections or QUERYs, or stored in the database environment. This could be SQL to execute a QUERY, or to EXEC Stored Procedures.
    • UNION: SQL to aggregate from multiple Source Data tables into Excel using JOIN with UNION ALL, with Linked Servers or OPENROWSET - for example, to collect organizational input for operational use.
  • Reports and Tables driven by Connections and QUERYs: These PivotReports, PivotCharts, and imported Structured Tables reside on Worksheet Tabs in the Reports workbook.
    • Framework shows Result from multiple SQL Structure alternatives.
    • Formatted as PivotTables and as imported Structured Tables.
    • UNION: Illustrating aggregation from several Source Data tables.
    • Complex QUERY Rollup Report: organized by Organization, System, Assembly, SubAssembly, Element, Rev, Expense/Capital, FiscalQtr; organizing Qty and ExtendedCost. This one table can have pivot order changed, could be duplicated and pivot order changed for multiple reports, fields deleted or new fields incorporated etc; for views of Capital and Expense plans by Year, Fiscal Year, by Quarter (which are driven from the mpp GANTT schedule/plan); Purchase and Build plans, Test plans, and many more at many levels of detail from summary to detailed operational instruction. And that's for a small but complex program from just one example QUERY.
    • .mpp / Schedule Date Table: Imported Date Table for joining with other Source Data tables for project use. This Table is typically imported into a Source Data workbook for preliminary calculations involving dates, and for inclusion in subsequent JOINs from the Reports workbook.

Excel Data Sources: Shadow Ranges, Worksheets, ACE, Linked Servers, OPENROWSET

To pull data from Excel into a database environment, you will use a Linked Table from MS Access, or Microsoft.ACE.OLEDB.xx.0 with SQL Server via a Linked Server, or via OPENROWSET. The database environment will have to be able to "see" the data in Excel, and can see Worksheets, and Named Ranges. Unfortunately, Excel Structured Tables, extremely useful for these applications, are not automatically visible.

So here's some VBA that will create a "Shadow" named range that tracks the table and stays up-to-date. When you click in a table, such as when you add/update/delete data in it, the range will update. The range will take the name of the table, and prepend "rng" to the name. If the table is called "tblTableName", the range will be named "rngTableName". If the table is called "TableName" the range will be named "rngTableName".

From MS Access, you can select the Shadow range by name when creating a Linked Table, and then use that Linked table in QUERYs.

From SQL Server via a Linked Server, reference the Shadow range by using a four-part name identifying the Linked Server and range such as in LS_Proto...rngAlloc AS xlAlloc as shown in examples on this site. Then QUERYs can reference fields, such as "Element", as xlAlloc.Element.

From SQL Server via OPENROWSET, reference the Shadow Range as in OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Users\XXXXXX\ToolSource.xlsm;HDR=YES',rngAlloc) AS xlAlloc as shown in examples on this site.

You can reference a full Worksheet in MS Access by referencing its name when creating a Linked Table.

You can reference a Worksheet from SQL Server as OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Users\XXXXXX\ToolSource.xlsm;HDR=YES',[WorksheetName$]) AS xlAlloc. When you reference as a full Worksheet via either MS Access or SQL Server, you are relying on the capability of the database to discern the data; and whatever is on that Worksheet will be treated as a single Table.

Here is VBA to create Shadow ranges in Excel that track Structured Tables. Place this code in Excel VBA Module1:

Public Sub TableUpdate(ByVal Target As Range)
    'Copyright (c) 2015 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice.
    'Keeps a range object in sync with table definition.
    'Range object is updated each time the table is changed/selected.
    Dim tblTarget As ListObject, thisListObject As ListObject
    Dim cellChanged As Range, rngTarget As Range
    Dim strTableName As String, strRangeName As String, strTblRangePrefix As String
    If Target Is Nothing Then Exit Sub
    Set cellChanged = Target
    strTblRangePrefix = "rng"
    For Each thisListObject In cellChanged.Worksheet.ListObjects
        If Not Intersect(cellChanged, thisListObject.Range) Is Nothing Then
            Set tblTarget = thisListObject
            strTableName = tblTarget.Name
            strRangeName = strTableName
            If Left(strRangeName, 3) = "tbl" Then _
                strRangeName = Right(strRangeName, Len(strRangeName) - 3)
            strRangeName = strTblRangePrefix & strRangeName
            Set rngTarget = tblTarget.Range
            rngTarget.Name = strRangeName
            Exit For
        End If
    Next thisListObject
End Sub

Then place the following VBA in the code page behind each Worksheet that contains a Structured Table for which a tracking Shadow range is needed:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Module1.TableUpdate Target:=Target
End Sub

When a click occurs within a Structured Table, the Selection_Change event catcher calls Module1.TableUpdate with the range of the cell clicked. Then Module1.TableUpdate determines which Table was clicked, and updates and names the Shadow Range to match the Table.

Handling Dates: MS Project, mppImport, mppQuery, Date Build/Update/Refresh

The cleanest way to keep Dates and Logistics aligned, is to export dates from Microsoft Project, and to tie specific tasks and their dates, to Logistics data handled through PowerOpI database solutions and reports.

In the Framework described on this website, a field is added to the MS Project Tasks page, called TaskMilestone containing a Key to uniquely identify tasks with their associated dates contained within a task record. The form of the TaskMilestone Key is ElementName:ElementRevision:Task - for example "Europa:P0-A:Fabout" (with the colon characters but without the quotes). Or you could create your own identifier scheme if you prefer. So the dates and other data in that particular task associated with this Key value can be identified. You must ensure that that Key value identifies a unique Task record.

You would create a similar Key within Excel Source Data tables, so you can JOIN the task dates with material or expense logistics items.

Code to export the date file from MS Project is in Code Samples 01. The file exported is in an .xlsx file named for the MS Project file exported from, and contains a worksheet referred to as [Task_Table1$] when it is imported into Excel via SQL Server.

An example of such an import is included in the Framework Excel file. Connection Record templates are shown in the Framework file, for import via a Connection (LinkType "mppImport") and via PowerQuery (LinkType "mppQuery").

Build/Update and Refresh of Connection records of LinkTypes mppImport and mppQuery are initiated by buttons for Build+Update DATE Links and Refresh DATES. Function of those Buttons for DATES is identical to the function of the Build+Update DATA Links and Refresh DATA buttons. The Buttons for DATA and for DATES are separated so that Dates can be updated separately from Data.

First, work on Dates is frequently handled separately from work on Materials and Expenses. They will come together ultimately, but you can work on the schedule and Dates while the logistics files are in use, and update the dates and add new materials when the schedule sequences, durations, dependencies and lead times are understood. Material updates usually occur much more frequently than date updates. Second, you need to refresh Dates before you refresh Data for materials et al, so that the materials logistics unambiguously are based on the dates you intend. For that reason, Dates and Data must be refreshed sequentially, not simultaneously.

Dates are imported into Excel as a Table, to be used before JOIN operations. You import the mpp Worksheet as a whole, e.g. [Task_Table1$], so a Shadow Range for the mpp exported worksheet is not needed. Calculations using these dates may occur in other Source Data tables, such as to calculate PurchDate based on material lead time; PayDate based on purchase or delivery date, material considerations, and accounting rules; and dates are usually a factor in applying Capitalization accounting rules to BOM elements. Source data imported into Excel as a Structured Table, such as this date file, may have calculations added and they will dynamically expand or contract to fit to rows added or deleted on refresh if you place a "proto-record" row at the top of the table containing column formulas and blank or default data field values; of course provide a means to exclude that row from JOINs or Pivots - usually key field values calculated from blank data fields in this "proto-record" take care of this for you automatically. The PowerOpI Framework file shows an example.

You will most likely use the Imported table as the basis for a tblDates to do date aliasing, and then use tblDates in JOINs. If you do wish to directly use the imported table in database JOINs, place the Table Update "SelectionChange" event-handler shown, onto the code page of the Worksheet Tab containing the import Table, and re-name the imported Table appropriately. A Shadow Range will be created for the imported Table and keep itself updated as the Table is refreshed to import the worksheet exported from MS Project, so the imported table will be visible to be used for JOINS in the database. As with all Excel Data Source tables, you use Microsoft.ACE.OLEDB.XX.0 with OPENROWSET or a Linked Server to access the table in SQL Server; and would create a Linked Table to use it in MS Access.

Determining PurchDate, PayDate and Capital/Expense Accounting

Corporate Finance is often not clear enough on accounting rules for project material, and the Program Manager needs to resolve several issues that depend on dates. There's often more clarity for production material than for development material with its more-situational dependencies. Resolution of these items will be handled in the tblDates and tblBOM tables discussed below.

  • Usually a large portion of material needed must be purchased in a quarter prior to its need, to account for lead time, especially if need is near that start of a quarter. For planning, does Finance want the date of the Purchase Order, or of material commit (i.e., un-returnable), or of payment?
  • PurchDate (PO Date) is earlier than fabrication start date by quoted Lead Time for material (plus fab and setup time). PayDate is dictated by purchase terms; for use of standard material (e.g. electronic components) may be ARO, typically 30 days after delivery. However, vendor fab using custom material (e.g. mechanical materials like metal, plastic, ceramic) usually requires payment concurrent with the Purchase Order. Finally, material used in executed fab steps is Committed, ultimately requiring payment even if the order for the item using it is canceled - so the Commit date may be a proper planning date.
  • Which of those dates are needed for PO approval by Finance? How is a the program Budget vs. Actual metric determined and tracked by Finance? The PM must determine the dates, and have agreement with Finance on which date is used for which purpose in project financial planning, execution, and reporting. The tblDates table is where the PM calculates these dates.
  • Similarly, rules for capitalization vs. expense accounting are usually not quite as clear as intended, so the PM needs to identify cases and get agreement with Finance. These also have some date dependencies.
  • First, consider fabricated material for which the program is directly responsible for design and fab planning and execution: Early prototype units are almost certainly Expense items. Later in development, Pre-Prototype or Pilot builds may provide equipment such as SQA systems for Software release testing and later regression test, both prior to release and then well into production. If that equipment is close to production, it could be Capitalized even though it is not yet formally released to production. Even then, though, fab material near release that is used in compliance systems under stress, is probably Expensed.
  • Second, consider purchased items. Such items over a capital threshold, maybe $1,500, and with an expected lifetime, are usually Capitalized. But normally-Cap items used in stressed systems, say servers in an environmental-test system, would be expensed. Are capitalize-able elements bought for early prototype lab use capitalized or expensed? And items below the cap threshold, bought in bulk for system integration, such as cables that may aggregate significant expense above cap limits - is capitalization determined individually, or in bulk? Is the whole cost of an integrated system capitalized, or are bulk items excluded?
  • These Capitalization rules must be clarified with Finance for planning and execution between Program Management and Finance, and represented in the Program Plan, probably using the Tables mentioned below, for Program execution. The result is going to show up as Capital and Expense financial amounts, tied to dates and important to management of the Program and to its participating organizations.
  • An article on how to use element aliasing in Source Data Tables managing dates and quantities in this tool will be provided soon.

Aliasing Dates for Dependent And Purchased Items

Without a doubt, the project GANTT plan in Microsoft Project is planning and tracking all the major project items - elements in development including design work, process steps, integration; and tasks among many groups - Engineering, OS, Management, Diags, Supply Chain, Operations, CM, Marketing, Support... But there are probably many, many more items needed by the project - purchased items. Where a development project may have a dozen or two key items that it is concentrating effort on, there are probably dozens if not hundreds of items needed for use with those items to build, test, integrate, certify, validate... This could include items like cables (!! - nemesis of many efforts), power supplies, racks, power strips, NICs, Storage disks and Flash, power cords, rack brackets... including some items that may be used internally but never become part of the released end-product.

Need-by dates for these items are usually tied to start of tasks like "Build", "Integrate" or "Test" steps. Each item may have many part number variants (cables! length, connector, speed) or capacity, pins, frequency; and all have different lead times... It's inconvenient to put so many of these items into a GANTT, where they add little value. You're going to work backwards from Need Date to determine PurchDate so material availability won't become a gating item in the GANTT. The few items that are, you can put in the GANTT; most of them won't so work backward from Need Date: PurchDate is Need Date minus Lead Time; and plan them in Excel. Also, we want to limit editing of the GANTT to Program Management, but to allow Supply Chain personnel to update Excel Source Data tables to cover purchased material. So a mechanism is described here which allows purchased items to be scheduled in the Excel tool where logistics are managed and where those dates are really useful.

We're already importing the MS Project date export, which provides the need-by task dates. Purchase lead time and payment terms can be captured for each item into the tblElement table containing such info for each item built or used in the project - quotes usually include purchase price, purchase lead time, and payment terms like net 30 ARO, or Payment at time of PO. With that information we can calculate item PurchDates and PayDates. These would be determined for purchased items, and may be applied similarly to key material for items planned in the GANTT (like new silicon parts on limited allocation; crystals, connectors), and also PayDates for both purchased and GANTT-planned items.

The project GANTT plan is imported in the Framework example, into Structured Table tblP0001_Rollup, and includes tasks with their Start_Date and Finish_Dates. Tasks that are going to be JOINed with Logistics data in other tables, have had Keys assigned that are like Europa:P2-B:AtEng and those Keys are included in the imported tblP0001_Rollup.

A new table, tblDates, is created to act as a "shell" to fetch dates from tblP0001_Rollup and to create dates in Excel that are not included in the GANTT. This allows us to create records to contain dates for tasks not in the GANTT, and for items not scheduled in the GANTT at all, tying those created dates to relevant dates that ARE included in the GANTT. This is called "aliasing". Here's an piece of a tblDates:

Aliasing Dates in tblDates.


The red boxes illustrate the fields used to create the Key values. The purple boxes illustrate how date aliasing is accomplished. Each record identifies an item daElement, its Revision daRev and a task daTask which together are associated with dates daStart_Date and daFinish_Date along with fields that can be calculated or looked up from them, determining Fiscal Year daFY, Fiscal Quarter daFQ, Lead Time daLeadTime and parameters determining Expense and Capitalization. From the fields identifying daElement, daRev and daTask, Key fields daSchedItemRevKey and daSchedItemRevTaskKey are created, to allow JOINing of these Date records with Logistics records from other tables tblAlloc, tblBOM, and tblElements. The Keys allow JOINing of all date records element-rev by element-rev, or retrieval of dates for individual tasks.

The dates themselves are looked up from tblP0001_Rollup like this:
MATCH([@daUseItem] & ":" & [@daRev] & ":" & [@daUseMilestone],tblP0001_Rollup[TaskMilestone],0),

Notice that this expression uses fields daUseItem, daRev and daUseMilestone instead of daElement, daRev and daTask upon which the lookup Keys are based. This allows us to alias in dates from a different Element:Rev:Task than the Keys address, and in particular to substitute dates or to create dates for items or tasks that are not available in tblP0001_Rollup. (In this example we assume we are aliasing dates from the same Rev of the element; otherwise we could have created a field "daUseRev" to further specify a aliasing from a different Rev.)

For items and tasks that are available in tblP0001_Rollup and that we want to use as scheduled in that table, the values of the "Use" fields are set to specify date fetch without aliasing:
daUseItem =[@daElement],
=[@daRev] is used without aliasing; otherwise could have created and used =[@daUseRev] and
daUseMilestone =[@daTask]
(or text values could be used duplicating those fields instead of expressions).

But in records for which we want to return PayDate or PurchDate (or any other task we wish to alias, or to create a Task that isn't available in tblP0001_Rollup), the values of the "Use" fields are set to specify the Element, Rev and Task for which dates should be returned. This shown by the purple boxes in the image.

The next image shows an example for which we created tasks and dates for a purchased item, Cable-QSFP28-3M, that is not scheduled in the GANTT at all. The boxes show that the logic is identical, but field values control where the dates are taken from.


In this case, dates of "PurchDate" and "PayDate" are aliased to fields specifying Europa:P2-A:AtEng. The "daStart_Date" field also subtracts the value of field "daLeadTime", picked up from tblElements, to allow for material purchase Lead Time. Note that the value of "daStart_Date" is about a month earlier than the date for "AtEng", consistent with subtracting the 28-day "daLeadTime":

Picking up StartDate, as Need Date for an item, from aliased task and, in red, subtracting LeadTime, from tblElements, to determine PurchDate:
MATCH([@daUseItem] & ":" & [@daRev] & ":" & [@daUseMilestone],tblP0001_Rollup[TaskMilestone],0),


where field daLeadTime is retrieved from tblElements as:

Tips and Troubleshooting

Showstoppers are highlighted.

Symptom Action
Can't log in to SQL Server from SSMS. Check if SQL Server Services has stopped - use PowerOpI Framework button Management Console to open Computer Management Console at C:\Windows\System32\compmgmt.msc > Services and Applications > SQL Server Configuration > SQL Server Services > SQL Server (<serverinstance>). Refresh the window to be sure of the status. Re-start SQL Server Services if it's red:

Right-click SQL Server (SQLEXPRESS) (SQLEXPRESS or whatever your instance is named) and choose Start, or click to select it and use the Start Service button above the frame. Retry the Re-start if it times-out and fails.

Failures when executing #Directives from SSMS. Create the #Directives from ToolManagement, ToolSource, ToolClient, ToolClientRemote. Before executing #Directives in SSMS, make sure those PowerOpI Tool files are CLOSED.
Linked-Server reference using four-Part namimg is not working. Ensure that Excel has not substituted an Elipsis character for the three separate dots "..." required.
Is this damn Linked Server working? In the SQL Server instance, find the Linked Server in Server Objects > Linked Servers. Expand it through Catalogs > default > Tables. If the Linked Server is operatonal, all the Named Ranges, including your Shadow Ranges, and Worksheets within the linked Workbook, will be listed.

If not operational:

First, verify that source data files are closed. Source data files must be closed when the LS is being created, and when a report refreshes a connection that uses a linked server, in order to execute the SQL associated with the connection to access the source data.

Check your SQL to create it vs. SQL on this website and in the PowerOpI Framework. If the SQL looks right you may need to re-start (stop and re-start) SQL Server Services via the Computer Management Console.

You may need to re-install Microsoft.ACE.OLEDB.XX.0 and try setting up the Linked Server; you could also try both the 12.0 and the 16.0 versions.

QUERY Using MS Access stalls, error reported:

<query name>_ACE, Release MS Access,...Permission denied, 1000070...Attempt re-try..
MS Access sometimes fails to release the database, and you just wait it out, it shouldn't re-occur once Access has sorted itself out. You will see a file "ToolSource.laccdb" with a small lock highlight while MS Access is waiting for timeout to release the database. That file will delete automatically after a few minutes timeout, and then MS Access will function properly.
SQL Server error message:

cannot initialize the data source object of ole db provider "microsoft.ace.oledb.12.0"
- or -
cannot initialize the data source object of ole db provider "microsoft.ace.oledb.16.0"

for linked server or OPENROWSET

SQL Server service may have stopped. See section in this table No connections established. It may take a couple of tries to re-start.

If the eror persists after re-start, Repair or re-install microsoft.ace.oledb.XX.0 as described within this table, as Timeout when accessing Excel source data files in QUERY using Linked Server or OPENROWSET.

Timeout when accessing Excel source data files in QUERY using Linked Server or OPENROWSET. Most common problem with SQL Server.

Ensure that the Source Data files are closed. Source data files must be closed when a query executes to read them, or to use or to create a Linked Server.

Repair (or Re-install if necessary) via installer for Microsoft.ACE.OLEDB.XX.0. Install and use the intended x86 (32-bit) or x64 (64-bit) version. Try both the 12.0 and 16.0 version. Often 12.0 works when 16.0 doesn't.

When Repair / Re-install completes, you need to start the SQL Server Service > SQL Server (SQLEXPRESS) (SQLEXPRESS or whatever your instance is named), using either the Computer Management console or SQL Server Configuration Manager. The procedure for this is listed just below in item No connections established.

No connections established. Check if SQL Server Services has stopped - use PowerOpI Framework button Management Console to open Computer Management Console at C:\Windows\System32\compmgmt.msc > Services and Applications > SQL Server Configuration > SQL Server Services > SQL Server (<serverinstance>). Refresh the window to be sure of the status. Re-start SQL Server Services if it's red:

Right-click SQL Server (SQLEXPRESS) (SQLEXPRESS or whatever your instance is named) and choose Start, or click to select it and use the Start Service button above the frame. Retry the Re-start if it times-out and fails.

New connections don't work. After building the Connections, save the Excel file before executing the connections. Also check if SQL Server Services has stopped.

If building a connection that uses MS Access, make sure the .accdb file exists in the target directory.

SQL Server data source file must be in different file than a QUERY accessing it, and the data source file must not be OPEN when the QUERY executes.

You must be able to access the file over the network if it's on a fileshare server. See the article above on finding a working fileshare path. Also, the network firewall must permit the access, whether SMB or SQL Server: Configure the Windows Firewall to Allow SQL Server Access on docs.microsoft.com.

Refresh very very slow. Disable Background Query in all QUERYs. Set the parameter to FALSE, and Re-Build all QUERYs. Also, take note that PowerQuery connections are much slower than Excel Connections. The PowerOpI Framework shows the Connection or Query being updated or refreshed, and its current operation, in the Excel status bar at the bottom of the Excel window.
Asked for Login when using PowerQuery. Set Query to use Windows Login. In Excel Data Connections this is done by adding substring Integrated Security=SSPI; within the Connection String template if it's not already there, and re-building the Connection.
A Connection that is not connected to drive a Pivot Table times out. Set field SkipBuildUpdate to "Skip"; or delete theConnection and re-build it from parameters when needed.

Refreshing a Connection that is not connected to a PivotTable, Table, or PivotChart will report an error. It is annoying but benign. If a Connection is not connected to a report, best to set field SkipBuildUpdate to "Skip".

Timeout on Refresh All. Disable "Refresh All" on all QUERYs, or delete un-connected QUERYs. In general, use the REFRESH buttons instead of Refresh All.
Asked for Login on Excel Data Connection operation. Add Windows Login substring Integrated Security=SSPI; to connection string template if missing and re-build the connection from Rebuild button. If substring is already present, SQL Server Services has probably stopped. Re-start it as indicated above under "No Connections Established".
On PowerQuery execution (refresh), PowerQuery accuses me of using an Encrypted connection it cannot resolve. An encryption option has crept in un-called-for when some connection was created. In Excel go to Menu "Data" > Get Data > Launch Power Query Editor... > Home tab > Edit > Data Source Settings > Global permissions; Right-Click each Connection shown in turn, and > Edit Permissions..., to check the Security of each connection listed. For Windows Credentials, Un-Check the Encryption checkbox where it isn't intended.
Connection and QUERY Build/Update/Refresh consistently fails. Make sure all SQL Server QUERYs referencing Tables in the SAME workbook as the QUERY have SkipBuildUpdate setting in tblConnectionConfig are set to Skip. Choose the Profile for this field (DataSource, Control, Report or Test), and ensure the setting for connections referencing tables in this Workbook are set to Skip.

Check if SQL Server Services has stopped, from Computer Management Console - use PowerOpI Framework button Management Console to open C:\Windows\System32\compmgmt.msc > Services and Applications > SQL Server Configuration > SQL Server Services > SQL Server (<serverinstance>). Right-Click and refresh the window to be sure of the status. Re-start SQL Server Services if it's red. Retry the Re-start if it times-out and fails, it often takes several attempts.

You may need to re-install Microsoft.ACE.OLEDB.XX.0 and try setting up the Linked Server with the 12.0 and the 16.0 version.

Keep a SQL Server SSMS session connected to the Server Instance to prevent it from going idle.

M QUERY, #Template-based, fails to Build, or mis-functions. Most likely an issue replacing string values from tblConnectionConfig. See comments on String Limitations.

Check for correct QUERY construction by reviewing Excel Menu Data > Queries & Connections > Queries Pane > QUERY entry > Edit from the fly-out pane or right-click menu > Power Query Editor > Advanced Menu.

Errors may be avoided by rules of String Limitations, or by sourcing the QUERY in M and using Common Configuration Variables for localization, instead of using template-based substitutions.

Conflicts between open Source Data Tables in open Excel Workbooks, and QUERYs in Excel Workbooks, when using MS Access or SQL Server. MS Access: When linking Excel tables into MS Access, the Excel data file containing the tables must not be open. When executing a QUERY to MS Access from Excel, MS Access must not be open. However, tables sourced for the QUERY, and the QUERY in Excel and its output Structured Table, Pivot Report, or Pivot Chart can all co-exist within the same open Workbook; MS Access however must be closed when QUERYs in this way are executed.

SQL Server: Data Tables sourced by a QUERY in Excel, must be in a workbook that is closed when the QUERY is executed. Therefore, the Source Data Tables must be in workbooks separate from workbooks containing QUERYs that access them; and the workbook containing the Source Data Tables must be closed when any QUERY accessing them is executed from another workbook.

"Linked Server Error: Msg 7399, Level 16, State 1" when Query from remote client uses a Linked Server LS to access a file outside the SQL Server environment. A Linked Server requires a login capable of reading the remote file, and may not have sufficient privilege when logged in remotely using a SQL Server Security login. The SQL described on Remote.htm includes code to include a capable login to LS setup. Thanks to Mangal Pardeshi for figuring out that login.
On ToolClient.xlsm, ToolSource.xlsm, ToolClientRemote.xlsm, or ToolManagement.xlsm, or on a file derived from one of these, on the Variables tab, Workstation environment Variables are inexplicably incorrect and may affect other Variables on that tab. Excel may become confused about the current environment if another Excel file is open when the PowerOpI file is opened, or if another Excel file is opened while a PowerOpI file is already open. This is only relevant when the Variables are in active use, to create SQL for a Connection or when building Connections that contain SQL dependent on those Variables.

To correct the Variables, the PowerOpI workbook must re-calculate. Make an entry in a cell in the Variables table and Enter, or force re-calculation of the PowerOpI workbook from the Ribbon. You may need to close the other open Excel files and then force re-calculation of the PowerOpI file.

The SQL Server process doesn't respond to login or to QUERY execution, but the SQL Server Configuration Manager indicates the SQL Server process is running. Windows sometimes fails to refresh the state of the SQL Server process as it is shown in the SQL Server Configuration Manager. Select the SQL Server Services panel > Right-Click in the open pane and select Refresh, to force the current state of the SQL Server process to be shown.
Recovery for the most-common SQL Server reported errors:

Cannot connect to a SQL Server database instance from SQL Server Management Studio (SSMS),

or cannot execute a SQL Server QUERY that uses a Linked Server, or Linked Server doesn't operate.

In the SQL Server Configuration Manager, Stop then Start the SQL Server process by selecting and right-clicking SQL Server Services > SQL Server process > Stop then Start the process.

Repair the ACE provider Microsoft.ACE.OLEDB.XX.0 by running its Installer (Repair, not Re-Install), then Stop and then re-Start the SQL Server process: SQL Server Configuration Manager > SQL Server Services > SQL Server process > Right-Click Stop then Start.

Whenever Starting the SQL Server process, it may take one or more re-tries to re-start if the re-start operation times out.

In PowerOpI file ToolClient.xlsm (or other), buttons on tab DataSources have become mis-aligned. The buttons will be re-aligned automatically on completion of any button-initiated operation from that tab.

Paths that Work to Data Source Files on a Fileshare Server

A Path to a file used in a QUERY is a fully-qualified path, including the Server's fileshare name (like \\DBSOURCE\ or \\LOCALHOST\) if it's accessed over the local fileshare network, or the directory root (like C:\) if it's local to the server running the QUERY. Following the network root or directory root, the path through the filesystem, then followed by the data source filename. For example:
C:\Level1\Level2\filename.accdb or
\\DBSOURCE\users\username\Documents\Level1\Level2\filename.accdb or
\\DBSOURCE\Documents\Level1\Level2\filename.accdb (depending on what directory is shared to the network).

A file accessed over the network must be in a directory on the fileshare server, that has been shared such that the QUERY can access it - by user name if using Windows credentials, or by group membership, or by loging name and password. Log in to the source server if needed, before executing the QUERY.

Client and server firewalls must enable Windows File Sharing (SMB). If SQL Server is in the path, firewalls must be configured to enable that: Configure the Windows Firewall to Allow SQL Server Access on docs.microsoft.com.

The data source Server may have several directories shared. To further the above examples, directories Level1 and Level2 could have been shared.

In that case, the target data source file could be accessed as
\\DBSOURCE\Level2\filename.accdb, or as
\\DBSOURCE\Level1\Level2\filename.accdb .
Notice that the levels of the server's directory path below the directory actually shared are excluded when specifying the path to the shared directory.

Whether one of those paths in the example will work, is up to SMB filesharing and might not work consistently. I suggest trying the target address in the Windows File Explorer to verify its functionality. Paste the target address into the address box near the top of the File Explorer pane, and Enter. If the path entered contains the path but not the target filename, the directory will open for view in the File Explorer pane if the path entered is functional. If the path includes the target filename, then that file will open if the path is functional.

You may have to try several path constructions to determine which will work; then the Common Configuration Variables can be adjusted to use that path, the Connections can be re-built and you can try the connection.

SQL Server From Remote Clients

Notes on setup of SQL Server configuration and security to allow QUERYs from a client located on a computer separate from the computer hosting the SQL Server instance can be found at SQL Server Database Security.

Support Reference Links

Platform Domain Link
LinkedIn MS Access Access and VBA
LinkedIn MS Access MS Access Development Professionals
LinkedIn MS Excel Excel and VBA Users
LinkedIn MS Excel Excel Blackbelts
LinkedIn MS Excel Excel Developers
LinkedIn MS Excel Microsoft Excel Users
LinkedIn MS Office Microsoft Office
LinkedIn MS Office Microsoft Office 365
LinkedIn MS Project Microsoft Project Professionals Network
LinkedIn MS Project MPUG Global
LinkedIn MS Project MS Project - Macros, Formulas and Programming
LinkedIn MS Project MS Project All Levels
LinkedIn MS Project Project and Planner User Group
LinkedIn MS SQLServer Microsoft SQL Server
LinkedIn MS Visio Visio Enthusiasts
Twitter Experts @contextures
Twitter Experts @mrexcel
Twitter Microsoft #microsoft
Twitter Microsoft @microsoft
Twitter MS Access #microsoftaccess
Twitter MS Access #msaccess
Twitter MS Access @msaccess
Twitter MS Excel #msexcel
Twitter MS Excel @msexcel
Twitter MS Office @office
Twitter MS Office #office365
Twitter MS Office @msft365news
Twitter MS Office @office365
Twitter MS Office @officeinsider
Twitter MS Project #microsoftproject
Twitter MS Project #msproject
Twitter MS Project @project
Twitter MS SQLServer #sqlserver
Twitter MS SQLServer #sqlservercentral
Twitter MS SQLServer @sqlserver
Twitter MS SQLServer @sqlservercentrl
Twitter MS Visio #msvisio
Twitter MS Visio @msvisio
Twitter Power BI #excelandaccess
Twitter Power BI #mspowerbi
Twitter Power BI #powerpivotpro
Twitter Power BI @excelandaccess
Twitter Power BI @msaccessbi
Twitter Power BI @poweraccessdb
Twitter Power BI @powerpivotinfo
Twitter Power BI @powerpivotpro
Website Experts StackOverflow
Website Experts Microsoft Tech Community
Website Experts Power BI Community
Reddit Program Management r⁄Programmanagement⁄
Reddit Project Management r⁄projectmanagement⁄