using Excel as an SQL query front-end

There are two steps to this: 1) setting up the ODBC connection, and 2) connecting Excel to that ODBC connection, and making it super easy and super clear how users can refresh the data.

Step 1: Set up the ODBC connection

It would be nice if this could be automated, so that end-users could have easy access to Excel's ease-of-use.

odbcconf.exe can be used within a .bat file to automate this. (though note that this tool is deprecated, in favor of PowerShell's Add-OdbcDsn)

• You may be able to poke at the registry to store the password inside the DSN. [2]

Step 1(b): Setting up ODBC connections for >DB2< databases specifically.

One method of automating this is to create the ODBC connection manually, record the various bits of data that got created, and then apply those changes to other people's computers:

• HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources\
• C:\ProgramData\IBM\DB2\<driver_name>\cfg\db2cli.ini
• C:\ProgramData\IBM\DB2\<driver_name>\cfg\db2dsdriver.cfg

There seems to be conflicting information, but from what I've read, it's impossible to store the password within the ODBC connection. (Even though the DB2 UI suggests that it can? TODO — This part is confusing.) [1]

TODO — If the user needs to first install the DB2 ODBC drivers, there are a couple places that those downloads might be available from: [1]

Step 2: Connect everything up within Excel

Random links — this may be useful when connecting from Excel to ODBC, using a DSN password

TODO — [1] [2]

• .odc (Office Data Connection) files [2] — these are created by Power Query
• .dqy (Excel ODBC Query File) files

Appendix A: Pieces of the puzzle.

• How to test an ODBC connection, using PowerShell, among other methods.