dbNet Programming Projects WebQueryTools MySQL ODBC DSN

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.

MySQL MySQL is commonly available, is free or cheap, and can be deployed on remote sites as well as on local networks. It can be installed on various computer types including big servers, and also desktop versions of Windows.

ODBC can be accessed from Excel using ODBC. ODBC can also be used to accessed many non-Microsoft database products and can also be used to access SQL Server. It's possible that ODBC can bypass limitations seen when using OLEDB to access SQL Server: possible, but subject to development and experimentation.

MariaDB started as a fork of MySQL so it is likely that the ODBC constructs described here for PowerOpI tools will work with it as well, possibly with small modifications.

For now, this PowerOpI tool instance using MySQL is still being developed, and is not complete. Watch for further functionality to be added.

Current Status of MySQLTool

Capabilities

MySQLTool provides function and configuration examples of ODBC support for connections to both MySQL and SQL Server.
Other database environments supporting ODBC can also be connected but have not been tested.
MySQLTool provides connection to databases from an Excel client via Excel Connection, and PowerQuery queries.
Excel Connections and PowerQuery queries both support SQL Queries, and PowerQuery also supports M language.
Support for DSN in various configurations (external, embedded, and none) is supported, and demonstrated in the examples detailed below.

Limitations

Currently, only read Queries are supported.
Table Push to MySQL is not yet supported. MySQL provides some data import facilities that may be used.
MySQLTool does not yet provide script support to create databases, to create schemas, to add user connections, or to manage permissions.
Support for Linked Servers for MySQL is not supported.
These capabilities are provided for SQL Server in SQLServerRemote.

MySQL, SQL Server, ODBC, DSN, Excel, PowerQuery

MySQL and ODBC Install
- On Server: Download and Install MySQL and Workbench
  
  MySQL download page with links to MySQL Installer for Windows, MySQL Workbench and various connectors including Connector/ODBC:
  MySQL Community Downloads
  Go to the MySQL Community Server link on that page for the Community 64-bit version of MySQL. We use the 64-bit MSI installer:
  Windows (x86, 64-bit), MSI Installer Reference article on Kinsta re: How to Set Up a MySQL Community Server on Your Machine.
  You also need MySQL Workbench, linked on the Downloads page:
  Windows (x86, 32 & 64-bit), MySQL WOrkbench Installer MSI
  Install MySQL Workbench on the Server computer, and on at least one Client computer that will be used for remote management.
- On Client: Download and Install MySQL ODBC Connector
  
  Back to MySQL Community Download page to get ODBC connector:
  MySQL Community Downloads
  For the ODBC Connector We use the 64-bit MSI installer:
  Windows (x86, 64-bit), MSI Installer
  The ODBC Connector must be installed on each Client computer.
- On Server: Using MySQL Workbench, add each User to MySQL allowed Remote Access.
  
  Permissions and roles like OpISuper; can also add users for OpIManager, OpIDataOwner, and OpIClient.
  
  Parameters:
  
  On Server, in MySQL Workbench > Navigator Pane > Administration > Users and Privileges:
  - In Users and Privileges pane > click Add Account to add a user e.g. PowerOpI Login Names OpISuper, OpIManager, OpIDataOwner, OpIClient.
  - All parameters as appropriate to PowerOpI Login Name and PowerOpI role. E.g., for Login Name OpISuper and values for role OpISuper:
    - Login Name: OpISuper; Authentication Type: caching_sha2_password; Limit to Hosts Matching: %; Password: <OpISuper password>;
    - Account Limits:Max Queries, Max Updates, Max Connections = 1000; Concurrent Connections = 40
    - Administrative Roles: DBA, MaintenanceAdmin, ProcessAdmin, UserAdmin, SecurityAdmin, DBManager, DBDesigner, ReplicationAdmin, BackupAdmin; Global Privileges: as needed, similar to SQLServerSecurity tool tblOpIRolesGRANT.
    - Schema Privileges: (for e.g., “Select “ALL””: Object Rights like CONTROL: SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW
    - DDL Rights: All, Other Rights: All
- On Client: Connect to MySQL
  
  Create connection to MySQL server.
  First, from Workbench (Local, then from separate Client)
  Second, from Excel PowerQuery
  Third, from an Excel Connection:
  - Connect from MySQL Workbench:
    - Create User
      - On Client MySQL Workbench: Navigator tab > Users and Privileges > Users and Privileges pane
      - In Users and Privileges pane > click Add Account to add a user e.g. PowerOpI Login Names OpISuper, OpIManager, OpIDataOwner, OpIClient.
      - All parameters as appropriate to PowerOpI Login Name and PowerOpI role. E.g., for Login Name OpISuper and values for role OpISuper:
        
        Login Name: OpISuper; Authentication Type: caching_sha2_password; Limit to Hosts Matching: %; Password: <OpISuper password>;
        
        Account Limits:Max Queries, Max Updates, Max Connections = 1000; Concurrent Connections = 40
        
        Administrative Roles: DBA, MaintenanceAdmin, ProcessAdmin, UserAdmin, SecurityAdmin, DBManager, DBDesigner, ReplicationAdmin, BackupAdmin; Global Privileges: as needed, similar to SQLServerSecurity tool tblOpIRolesGRANT.
        
        Schema Privileges: (for e.g., “Select “ALL””: Object Rights like CONTROL: SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW
        
        DDL Rights: All, Other Rights: All
    - Create Connection
      - on Client MySQL Workbench Home pane > Click the "Plus Sign" just to right of MySQL Connections at left-center of pane.
        
        Connection Name: like "COBALT-MySQL-OpISuper"
        
        Connection Method: Standard (TCP/IP)
        
        Parameters like
        
        Hostname: COBALT, Port 3306
        
        Username: OpISuper (use a PowerOpI username)
        
        Password: Store in Vault… password for PowerOpI username
        
        SSL: Use SSL: If available
      - Click button on MySQL Workbench Home pane, or right-click and choose Open Connection, to open the connection created.
  - Connect from Excel PowerQuery:
    - In Excel: Data tab > Get Data > From Database > From MySQL Database
      - Server: COBALT
      - Database: Use MySQL Schema name (e.g., "Sakila")
      - Advanced: SQL Query: (e.g. SELECT films.* FROM sakila.film_list AS films LIMIT 0, 1000; )
      - Login: Database > User name: OpISuper, Password OpISuper password
      - Select level to apply settings: cobalt (server name)
      - Load To: Table or Pivot etc
      - In Advanced Editor, M Code: example like
        
        let Source = MySQL.Database("COBALT", "sakila", [ReturnSingleDatabase=true, Query="SELECT films.* FROM sakila.film_list AS films LIMIT 0, 1000;", HierarchicalNavigation=true]) in Source
      - Then use the PowerQuery Editor Load To... button to drive a PowerQuery Excel pivot report from MySQL. Note that this uses the PowerQuery data function MySQL.Database() which does not use a DSN. An alternative implementation could use Odbc.DataSource() which would use a DSN, or Odbc.Query() which would reference a DSN and contain an embedded SQL QUERY.
      - Alternatively, create a connection like MySQLTool\ToolClientRemote.xlsm qMySQL-FilmBuild that embeds the M-code, to drive a pivot report from MySQL. A connection string could reference a DSN, or embed connection parameters.
- References

Connection Paths

In PowerOpI MySQLTool\ToolClientRemote.xlsm, working sample connection paths, each driving a pivot report, are illustrated. Setup includes connection strings, DSN file, SQL QUERY, Excel Connection.

MySQLTool To use MySQLTool, you must first install it, and then verify or set up its configuration variables. MySQLTool can be downloaded from here. I suggest you install the tool in a directory at ..\Documents\PowerOpI\MySQLTool. By default the tool will be installed in a directory named MySQLTool. The tool must be replicated for each project, to a directory such as ..\Documents\PowerOpI\<projectName>.

MySQLTool Configuration The configuration variables on MySQLTool\ToolClientRemote.xlsm and also ToolSource.xlsm will automatically reconfigure to reflect the installation location, but you must review and update the variables to reflect remote server locations, user names, schema names etc. on Excel tab "Variables". That page supports several profiles, chosen by the Choose Profile dropdown near cell C13. The default sets that to ProfileRemote2 to talk to a remote server in section Server2. Check and update those settings in column ProfileRemote2, and also check/update all settings in that column. The profile values are chosen into column ProfileSelect, and configure connection strings and SQL on tab DataSources.

ODBC We connect to MySQL using ODBC; and ODBC can also be used to connect to SQL Server. Configuration examples are on provided in MySQLTool\ToolClientRemore.xlsm, described in the table below and following detail. Configurations for both MySQL and SQL Server are shown. To use ODBC, you must configure a DSN for each database and user Login. The DSN configuration steps are shown below in the detail descriptions for MySQL first, followed by descriptions for SQL Server.

MySQL and SQL Server MySQLTool provides example connections for both MySQL and SQL Server database environments. You can enable or disable execution of these examples for one or the other or both databases, if you do not have both database environments installed.

To disable examples for SQL Server: in ...MySQLTool\ToolClientRemote.xlsm, Table tblConnectionConfig, set field SkipBuildUpdate to Skip for the following SQL Server connections named in field ConnectionName: qPQM_SQLSvr_ ODBC_DSN, SQLServer-ODBC-DSN, and SQLServer-ODBC-Keywords. To re-enable, set field SkipBuildUpdate to Build.

To disable examples for MySQL: in ...MySQLTool\ToolClientRemote.xlsm, Table tblConnectionConfig, set field SkipBuildUpdate to Skip for the following MySQL connections named in field ConnectionName: qMySQL-Films, qMySQL-FilmBuild, qMySQL-FilmODBC, and qMySQL-ODBC-Keywords-FilmODBC. To re-enable, set field SkipBuildUpdate to Build.

Naming, Logins and Roles The convention used in these examples names each DSN as <databaseType (MySQL or SQLServer)><serverComputerName (COBALT in the examples)><DSN Scope (User,System, File)><UserLoginName>. The security page on this site describes default Roles set up for OpISuper, OpIManager, OpIDataOwner, and OpiClient; and creates Server Logins and Database Users by the same names. The MySQLTool examples use those names for both MySQL and SQL Server defaults, but once you have this setup working you should either modify passwords, or create your own Logins and disable or delete these defaults.

List of Working Path Examples

Method	Database	Query/Conn	DSN	Tab/Pivot	Query/Conn Name	Provider	SQLName
ODBC	MySQL	PowerQuery	DSN: None	qMySQL-Films	qMySQL-Films	qMySQL-FilmsBuildPQM	(Not used in example)
ODBC	MySQL	PowerQuery	DSN: None	qMySQL-FilmBuild	qMySQL-FilmBuild	qMySQL-FilmBuild	(Not used in example)
ODBC	MySQL	Connection	DSN: External	qMySQL-FilmODBC	qMySQL-FilmODBC	MySQL-ODBC-DSN-OpISuper	qMySQL-FilmsBuildSQL
ODBC	MySQL	Connection	DSN: External	qMySQL- Keywords-FilmODBC	qMySQL-ODBC-Keywords-FilmODBC	MySQL-ODBC-DSN-OpISuper	qMySQL-FilmsBuildSQL
ODBC	SQLServer	PowerQuery	DSN: External	qPQM_SQLSvr_ ODBC_DSN	qPQM_SQLSvr_ ODBC_DSN	qPQM_SQLSvr_ ODBC_DSN	(Not used in example)
ODBC	SQLServer	Connection	DSN: External	SQLServer-ODBC-DSN	SQLServer-ODBC-DSN	SQLServer-ODBC-DSN-OpISuper	qBuildPlan DBTables
ODBC	SQLServer	Connection	DSN: Embedded	SQLServer-ODBC-Keywords	SQLServer-ODBC-Keywords	SQLServer-ODBC-Keywords-OpISuper	qBuildPlan DBTables

Working Path Details

Create a DSN for each user of MySQL.
- DSN for MySQL The DSN referenced is created in Windows tool ODBC Data Source Administrator (64-bit) (or 32-bit) from Windows Control Panel > All Control Panel Items >Administrative Tools > ODBC Data Sources (64-bit) or 32-bit, which can also be accessed at C:\Windows\System32\odbcad64.exe. You will need a DSN file set up for each MySQL Login User to be granted access to MySQL, and the DSN files must be distributed to client computers. Alternatively, you can use the ODBC Data Sources on client computers to set them up. When creating, set configuration values pertinent to the specified user, like these examples using default Login etc.:
  
  System DSN (System Tab) > Add...:
  
  Driver: MySQL 8.0 Unicode Driver
  
  Data Source Name: MySQL8U-COBALT-OpISuper-Sys
  
  Description: As you like
  
  TCP/IP Server: (choose button) COBALT Port: 3306
  
  User: OpISuper
  
  Password: abcd%12
  
  Database: sakila
  
  Connection "Details>>": if any are needed
  
  Click "Test" button and if it works, "OK" button.
The "DSN: None" configuration below using PowerQuery DSN/ODBC mashup access won't need a DSN.
ODBC - MySQL - PowerQuery - DSN: None

Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

Tab / Pivot Table: qMySQL-Films

tblConnectionConfig.ConnectionName = qMySQL-Films
(Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

tblConnectionConfig.Provider = tblConnection.qMySQL-FilmsPQM

tblConnectionConfig.LinkType = webQuery (indicates Query via PowerQuery)

tblConnection.qMySQL-FilmsPQM =
```
//  PowerQuery, M, Embedded SQL, 
// SQL is inserted from tblSQL by name, via XLOOKUP. 
let 
    Source = MySQL.Database("COBALT", "sakila", [ReturnSingleDatabase=true, 
        Query="/* Demo Query MySQL Table sakila.film_list */ 
SELECT films.* 
FROM sakila.film_list AS films 
LIMIT 0, 1000;", HierarchicalNavigation=true]) 
in 
    Source
```

ODBC - MySQL - PowerQuery - DSN: None

Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

Tab / Pivot Table: qMySQL-FilmBuild

tblConnectionConfig.ConnectionName = qMySQL-FilmBuild
(Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

tblConnectionConfig.Provider = tblConnection.qMySQL-FilmBuild

tblConnectionConfig.LinkType = webQuery (indicates Query via PowerQuery)

tblConnection.qMySQL-FilmBuild =

let 
    Source = MySQL.Database("COBALT", "sakila", [ReturnSingleDatabase=true]), 
    sakila_film = Source{[Schema="sakila",Item="film"]}[Data], 
    sakila_film_category = Source{[Schema="sakila",Item="film_category"]}[Data], 
    sakila_film_actor = Source{[Schema="sakila",Item="film_actor"]}[Data], 
    sakila_actor = Source{[Schema="sakila",Item="actor"]}[Data], 
 
    sakila_film_renamedColumns = Table.RenameColumns(sakila_film,{{"film_id", 
        "film_film_id"}, {"last_update", "film_last_update"}}),  
    sakila_film_category_renamedColumns = Table.RenameColumns(sakila_film_category,
        {{"film_id", "category_film_id"},{"last_update","category_last_update"}, 
        {"sakila.film", "category_sakila.film"}}),  
    sakila_film_actor_renamedColumns = Table.RenameColumns(sakila_film_actor,
        {{"film_id", "film_actor_film_id"},{"last_update","film_actor_last_update"}}),  
    sakila_actor_renamedColumns = Table.RenameColumns(sakila_actor,{{"actor_id", 
        "actor_actor_id"},{"last_update","actor_last_update"},{"sakila.film_actor",
        "sakila.actor_actor"}}),  
  
    film_category_join = Table.Join(sakila_film_renamedColumns,{"film_film_id"},
        sakila_film_category_renamedColumns,{"category_film_id"},JoinKind.LeftOuter),  
    film_actor_join = Table.Join(film_category_join,{"category_film_id"},
        sakila_film_actor_renamedColumns,{"film_actor_film_id"},JoinKind.LeftOuter),  
    film_actor_name_join =       Table.Join(film_actor_join,{"actor_id"},
        sakila_actor_renamedColumns,{"actor_actor_id"},JoinKind.LeftOuter)  
  
in  
    film_actor_name_join

ODBC - MySQL - Connection - DSN: External

Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

Tab / Pivot Table: qMySQL-FilmODBC

tblConnectionConfig.ConnectionName = qMySQL-FilmODBC
(Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

tblConnectionConfig.Provider = tblConnection.MySQL-ODBC-DSN-OpISuper

tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection)

tblConnectionConfig.SQLName = tblSQL.qMySQL-FilmsBuildSQL

tblConnection.MySQL-ODBC-DSN-OpISuper =
```
DSN=MySQL8U-COBALT-OpISuper-Sys;
```
tblSQL.qMySQL-FilmsBuildSQL =
```
/* Demo Query MySQL Table sakila.film_list */
SELECT films.*
FROM sakila.film_list AS films
LIMIT 0, 1000;
```
ODBC - MySQL - Connection - DSN: External

Connections are defined in PowerOpI files (ToolClientRemote.xlsm, ToolClient.xlsm, ToolSource.xlsm, ToolManagement.xlsm) tab DataSources in Table tblConnectionConfig which references entries in Tables tblConnection and tblSQL.

Tab / Pivot Table: qMySQL-Keywords-FilmODBC

tblConnectionConfig.ConnectionName = qMySQL-ODBC-Keywords-FilmODBC
(Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

tblConnectionConfig.Provider = tblConnection.MySQL-ODBC-DSN-OpISuper

tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection)

tblConnectionConfig.SQLName: tblSQL.qMySQL-FilmsBuildSQL

tblConnection.MySQL-ODBC-DSN-OpISuper =
```
DSN=MySQL8U-COBALT-OpISuper-Sys;
```
tblSQL.qMySQL-FilmsBuildSQL =
```
/* Demo Query MySQL Table sakila.film_list */
SELECT films.*
FROM sakila.film_list AS films
LIMIT 0, 1000;
```
Create a DSN for each user who accesses SQL Server via ODBC.

SQL Server First, to run the following examples of access to SQL Server via ODBC, an instance of SQL Server must be installed and working on a server computer, and a database must be configured within that server consistent with parameters in the following examples. The default settings in MySQLTool connect to [Cobalt\SQLExpress].[SQLServerRemoteDB].[dbo].<tablename>, by Server Login OpISuper with password abcd%12 as shown in MySQLTool > Tab Variables. Server Name could be expressed as IP Address e.g. 192.168.1.280. Installation and configuration instructions for SQL Server are available on the PowerOpI PowerQueryTool page and can be modified there to reflect your configurations of Server Name, Database Name, Login etc. Instruction for setting up security and logins on the PowerOpI Database Security page.

Install or update ODBC driver ODBC Driver 18 for SQL Server as needed from Microsoft ODBC Driver page.

Create DSNs for SQL Server Logins. The DSN referenced is created in Windows tool ODBC Data Source Administrator (64-bit) (or 32-bit) from Windows Control Panel > All Control Panel Items > Administrative Tools > ODBC Data Sources (64-bit) or 32-bit, which can also be accessed as C:\Windows\System32\odbcad64.exe. You will need a DSN file set up for each Login User to be granted access to SQL Server via ODBC, and the DSN files must be distributed to client computers. Alternatively, you can use the ODBC Data Sources on client computers to set it up. When creating, set configuration values pertinent to the specified user, like the following examples using defaults:
- System DSN: SQLSV18-COBALT-OpISuper-Sys, Driver: ODBC Driver 18 for SQL Server. Add, and Configure...
- Name: SQLSV18-COBALT-OpISuper-Sys
- Description: SQLSV18-COBALT-OpISuper-Sys (used in Excel connection string, in code above)
- Server: COBALT\SQLEXPRESS
- LoginID: Choose "With SQL Server authentication using a login ID and password entered by the user"
- Login ID: OpISuper
- Password: <LoginID password>
- Checkmark: Use ANSI quoted identifiers
- Checkmark: Use ANSI nulls, paddings and warnings
- Application intent: READWRITE
- Checkmark: Transparent Network IP Resolution
- Connection Encryption: Optional
- Checkmark: Trust server certificate
- Checkmark: Perform translation for character data
- Connect retry count: 1
- Connect retry interval (seconds): 10
The "DSN: Embedded" configuration won't need a DSN but will need DSN values to be set up in ToolClientRemote.xlsm > Data Sources tab > tblConnection column SQLServer-ODBC-Keywords-OpISuper, to set up embedded DSN.

ODBC - SQLServer - PowerQuery - DSN: External

Tab / Pivot Table: qPQM_SQLSvr_ODBC_DSN

tblConnectionConfig.ConnectionName = qPQM_SQLSvr_ODBC_DSN
(Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

tblConnectionConfig.Provider = tblConnection.qPQM_SQLSvr_ODBC_DSN

tblConnectionConfig.LinkType = webQuery (indicates Query via PowerQuery)

tblConnection.qPQM_SQLSvr_ODBC_DSN =

let
    Source = Odbc.DataSource("dsn=SQLSV18-COBALT-OpISuper-Sys", 
        [HierarchicalNavigation=true]),
    SQLServerRemoteDB_Database = Source{[Name="SQLServerRemoteDB",
        Kind="Database"]}[Data],
    dbo_Schema = SQLServerRemoteDB_Database{[Name="dbo",Kind="Schema"]}[Data],

    xlAlloc = dbo_Schema{[Name="xlAlloc",Kind="Table"]}[Data],
    xlBOM = dbo_Schema{[Name="xlBOM",Kind="Table"]}[Data], 
    xlDates = dbo_Schema{[Name="xlDates",Kind="Table"]}[Data], 
    xlBuilds = dbo_Schema{[Name="xlBuilds",Kind="Table"]}[Data], 
    xlElements = dbo_Schema{[Name="xlElements",Kind="Table"]}[Data], 
    xlPR = dbo_Schema{[Name="xlPR",Kind="Table"]}[Data], 

    AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},
        JoinKind.LeftOuter), 
    ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},
        JoinKind.LeftOuter), 
    ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},
        JoinKind.LeftOuter), 
    ABDBE=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},
        JoinKind.LeftOuter), 
    ABDBEP=Table.Join(ABDBE,{"akPurchReq"},xlPR,{"prPurchReq"},
        JoinKind.LeftOuter), 
 
    ABDBEPQ=Table.AddColumn(ABDBEP,"QtyExt", each [akQty] * [bmSubQty]), 
    ABDBEPQC=Table.AddColumn(ABDBEPQ,"CostExt", each [QtyExt] * [bmSubCost])
 
in 
    ABDBEPQC

ODBC - SQL Server - Connection - DSN: External

Tab / Pivot Table: SQLServer-ODBC-DSN

tblConnectionConfig.ConnectionName = SQLServer-ODBC-DSN
(Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

tblConnectionConfig.Provider = tblConnection.SQLServer-ODBC-DSN-OpISuper

tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection.)

tblConnectionConfig.SQLName = tblSQL.qBuildPlanDBTables

tblConnection.SQLServer-ODBC-DSN-OpISuper =

ODBC;
DSN=SQLSV18-COBALT-OpISuper-Sys;

tblSQL.qBuildPlanDBTables =

/* QUERY Build Plan DB Tables */
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

  SELECT [SQLServerRemoteDB].[dbo].[xlAlloc].*, [SQLServerRemoteDB].[dbo].[xlBOM].*, 
    [SQLServerRemoteDB].[dbo].[xlDates].*, [SQLServerRemoteDB].[dbo].[xlBuilds].*, 
    [SQLServerRemoteDB].[dbo].[xlElements].*, [SQLServerRemoteDB].[dbo].[xlPR].*, 
    [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] 
        AS QtyExt, 
    [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] * 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubCost] AS CostExt
  FROM [SQLServerRemoteDB].[dbo].[xlAlloc]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBOM] ON 
    [SQLServerRemoteDB].[dbo].[xlAlloc].[akAllocRevKey] = 
    [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyIn]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlDates] ON 
    [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyOut] = 
    [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBuilds] ON 
    [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey] = 
    [SQLServerRemoteDB].[dbo].[xlBuilds].[bdBuildRevKey]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlElements] ON 
    [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKey] = 
    [SQLServerRemoteDB].[dbo].[xlElements].[emElementItemKey]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlPR] ON 
    CAST([SQLServerRemoteDB].[dbo].[xlAlloc].[akPurchReq] AS VARCHAR) = 
    CAST([SQLServerRemoteDB].[dbo].[xlPR].[prPurchReq] AS VARCHAR);

ODBC - SQLServer - Connection - DSN: Embedded

Tab / Pivot Table: SQLServer-ODBC-Keywords

tblConnectionConfig.ConnectionName = SQLServer-ODBC-Keywords
(Note: Excel menu: Data > Queries & Connections lists by ConnectionName)

tblConnectionConfig.Provider = tblConnection.SQLServer-ODBC-Keywords-OpISuper

tblConnectionConfig.LinkType = pivotSource (indicates Connection via Excel Connection.)

tblConnectionConfig.SQLName = tblSQL.qBuildPlanDBTables

tblConnection.SQLServer-ODBC-Keywords-OpISuper =

ODBC;
DRIVER=ODBC Driver 18 for SQL Server;
SERVER=COBALT\SQLEXPRESS;
WSID=TRILITHIUM;
UID=OpISuper;
PWD=abcd%12;
Encrypt=Optional;
Trusted_Connection=No;
TrustServerCertificate=Yes;
APP=Microsoft® Windows® Operating System;

Values above separated to multiple lines for readability only. To use the above as connection string, concatenate all to a single string. Separators are ";" NO CRLF or space:

ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=COBALT\SQLEXPRESS;WSID=TRILITHIUM; UID=OpISuper;PWD=abcd%12;Encrypt=Optional;Trusted_Connection=No; TrustServerCertificate=Yes; APP=Microsoft® Windows® Operating System;

tblSQL.qBuildPlanDBTables =

/* QUERY Build Plan DB Tables */
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

  SELECT [SQLServerRemoteDB].[dbo].[xlAlloc].*, [SQLServerRemoteDB].[dbo].[xlBOM].*, 
    [SQLServerRemoteDB].[dbo].[xlDates].*, [SQLServerRemoteDB].[dbo].[xlBuilds].*, 
    [SQLServerRemoteDB].[dbo].[xlElements].*, [SQLServerRemoteDB].[dbo].[xlPR].*, 
    [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] 
        AS QtyExt, 
    [SQLServerRemoteDB].[dbo].[xlAlloc].[akQty] * 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubQty] * 
        [SQLServerRemoteDB].[dbo].[xlBOM].[bmSubCost] AS CostExt
  FROM [SQLServerRemoteDB].[dbo].[xlAlloc]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBOM] ON 
    [SQLServerRemoteDB].[dbo].[xlAlloc].[akAllocRevKey] = 
    [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyIn]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlDates] ON 
    [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKeyOut] = 
    [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlBuilds] ON 
    [SQLServerRemoteDB].[dbo].[xlDates].[daSchedItemRevKey] = 
    [SQLServerRemoteDB].[dbo].[xlBuilds].[bdBuildRevKey]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlElements] ON 
    [SQLServerRemoteDB].[dbo].[xlBOM].[bmBOMItemKey] = 
    [SQLServerRemoteDB].[dbo].[xlElements].[emElementItemKey]
  LEFT OUTER JOIN [SQLServerRemoteDB].[dbo].[xlPR] ON 
    CAST([SQLServerRemoteDB].[dbo].[xlAlloc].[akPurchReq] AS VARCHAR) = 
    CAST([SQLServerRemoteDB].[dbo].[xlPR].[prPurchReq] AS VARCHAR);

MySQL QUERY: SQL Differences

PowerOpI tools (ToolSource.xlsm et al.) provide an Excel function strQuoteName(<string> | <stringReference> [,<bracketChar> | <bracketCharReference>]), to replicate the equivalent SQL function, for use in writing SQL in Excel PowerOpI tools.

The argument string and character references follow all the conventions for strings and string references, including use of " characters, and " characters within quoted string values. The optional bracketChar argument specifies the quote character to be used around the string argument. If left out, the default character will be used; that character is specified on tab Variables in PowerOpI *.xlsm tools, in the cell named strDefaultQuoteChar. If that bracketChar, or bracketCharReference, is specified as any one of ()[]{}<>, its partner character is used appropriately to bracket the string being quoted.

SQL Server provides a multi-level address structure: [<serverName>].[<databaseName>].[<schemaName>].[<tableName>].[<fieldName>] where [<schemaName>] provides ability to sub-group tables within a database, typically with differing permissions on visibility, access, update, etc. In MySQL however, terms [<databaseName>] and [<schemaName>] are interchangeable: there is no sub-grouping structure within a [<databaseName>] (or equivalently, a [<schemaName>]). So MySQL name references omit that part. In the example SQL below I've made a table.field reference as SQLServerRemoteDB.xlBOM.bmSubQty because of SQLServer habits, but it could just as easily been called SQLServerRemoteSchema.xlBOM.bmSubQty. So name structure in MySQL is <schemaName>.<tableName>.<fieldName> with <schemaName> providing the functionality referred to in PowerOpI Tools as [<databaseName>].

The Sakila database in the following example is included in the MySQL Workbench, and is followed by a QUERY discussed throughout this website for PowerOpI with SQL Server, PowerQuery and MSAccess, this time translated for use with MySQL.
```
/* Demo Query MySQL Table sakila.film_list */
SELECT films.*
FROM sakila.film_list AS films
LIMIT 0, 1000;

/*
    ----------------------------------------------------------------------------
    ... or, a PM example...
*/

/* QUERY Build Plan DB Tables */
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

SELECT SQLServerRemoteDB.xlAlloc.*, SQLServerRemoteDB.xlBOM.*, 
  SQLServerRemoteDB.xlDates.*, SQLServerRemoteDB.xlBuilds.*, 
  SQLServerRemoteDB.xlElements.*, SQLServerRemoteDB.xlPR.*, 
  SQLServerRemoteDB.xlAlloc.akQty 
    * SQLServerRemoteDB.xlBOM.bmSubQty AS QtyExt,
  SQLServerRemoteDB.xlAlloc.akQty * SQLServerRemoteDB.xlBOM.bmSubQty 
    * SQLServerRemoteDB.xlBOM.bmSubCost AS CostExt
FROM SQLServerRemoteDB.xlAlloc
LEFT OUTER JOIN SQLServerRemoteDB.xlBOM 
  ON SQLServerRemoteDB.xlAlloc.akAllocRevKey 
    = SQLServerRemoteDB.xlBOM.bmBOMItemKeyIn
LEFT OUTER JOIN SQLServerRemoteDB.xlDates 
  ON SQLServerRemoteDB.xlBOM.bmBOMItemKeyOut 
    = SQLServerRemoteDB.xlDates.daSchedItemRevKey
LEFT OUTER JOIN SQLServerRemoteDB.xlBuilds 
  ON SQLServerRemoteDB.xlDates.daSchedItemRevKey 
    = SQLServerRemoteDB.xlBuilds.bdBuildRevKey
LEFT OUTER JOIN SQLServerRemoteDB.xlElements 
  ON SQLServerRemoteDB.xlBOM.bmBOMItemKey 
    = SQLServerRemoteDB.xlElements.emElementItemKey
LEFT OUTER JOIN SQLServerRemoteDB.xlPR 
  ON CAST(SQLServerRemoteDB.xlAlloc.akPurchReq AS VARCHAR) 
    = CAST(SQLServerRemoteDB.xlPR.prPurchReq AS VARCHAR);
```

ODBC to MSAccess and Excel

ODBC can be used to access files too, just as for OLEDB. Shared use of MSAccess is basically about sharing the .accdb file containing data and stored QUERYs, which can be opened in MSAccess on the Client computer to execute QUERYs from there.

ODBC uses a DSN to specify the .accdb file location and parameters. The DSN referenced can be created in Windows tool ODBC Data Source Administrator (64-bit) (or 32-bit) from Windows Control Panel > All Control Panel Items > Administrative Tools > ODBC Data Sources (64-bit) or 32-bit, which can also be accessed as C:\Windows\System32\odbcad64.exe. You will need a DSN set up for each MSAccess Login User to be granted access via ODBC, and the DSN must be distributed to client computers.

You can use the ODBC Data Source Administrator on client computers to create the client's .dsn, or you can set up a .dsn file to distribute to Client computers. It can be edited by the Windows Text Editor. The .dsn, or .dsn file, can be referenced by its name (below) in a connection string, or the DSN values can be embedded within a connection string. PowerOpI MSAccess Tool and MySQLTool illustrate these. The following example shows typical parameters, to be edited to incorporate your own specifics.

Consider naming a DSN or DSN file as MSAccess-<serverName>-<userLoginName>-sys like MSAccess-COBALT-admin-sys; that name would be referenced by the connection string as dsn=<dsnName>.

[ODBC];
DRIVER=Microsoft Access Driver (*.mdb, *.accdb);
UID= <your user name, typically "admin">;
UserCommitSync=Yes;
Threads=3;
SafeTransactions=0;
PageTimeout=5;
MaxScanRows=8;
MaxBufferSize=2048;
FIL=MS Access;
DriverId=25;
DefaultDir=\\<computer-name>\<path to shared-file dir>\Shared-file dir\;
DBQ=\\<computer-name>\<path to shared-file dir>\Shared-file dir\ToolSource.accdb;

The directory containing the .accdb file must be shared or mounted on a network drive (e.g., X-Drive etc.). The shared-file path could be something like Users\<share loginID>\Documents\PowerOpI\ as shown above, or if the file is in a shared directory, start the path at the share name: <ShareName>\<path to MSAccess .accdb file>.

Since MSAccess will run on the client computer, any data source files linked into MSAccess must be linked to it using the full network name as it will be seen from each Client computer.

Similar to reading MSAccess, ODBC can read Excel files, using a different Driver, FIl, and DriverID.:

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
UID=admin;
UserCommitSync=Yes;
Threads=3;
SafeTransactions=0;
ReadOnly=1;
PageTimeout=5;
MaxScanRows=8;
MaxBufferSize=2048;
FIL=excel 12.0;
DriverId=1046;
DefaultDir=\\<computer-name>\<path to shared-file dir>\Shared-file dir\;
DBQ=\\<computer-name>\<path to shared-file dir>\Shared-file dir\ToolSource.xlsm;

More Web Query Tools

Google Sheets with native QUERY function (altho doesn't look like a full LEFT OUTER JOIN, no record expansion on multiple key match...). Here's a YouTube channel exploring Google Sheets QUERY, and a video from that channel discussing function QUERY, and multi-part video. Web database + web client.
Google Big Query with Connected Sheets. Web database + web client?
Microsoft 365 Excel Online driving Pivots from PowerBI Data Sets: Chris Webb Twitter thread re Microsoft Ignite March 2021 demo.
PowerQuery pulling data from Google Sheets, QUERY in PowerQuery: in Excel for PowerOpI pivots; but maybe also PowerBI Desktop for graphic analysis. Web database + local client.
- Working references:
- Power Query for Google Sheets
- Power Query in Google Sheets
- YouTube demo
- Google Sheets as Data Source for Power Query
Web-based PostgreSQL or MySQL or ODBC database. Web database + unknown client.
Can WebAPI help transport data to a QUERY engine? C# WebAPI. WebAPI tutorial. Unknown database + unknown client.
Azure or AWS. Web database + unknown client.

Current Status of MySQLTool

MySQL, SQL Server, ODBC, DSN, Excel, PowerQuery

MySQL and ODBC Install

Connection Paths

MySQL QUERY: SQL Differences

ODBC to MSAccess and Excel

More Web Query Tools