paperlined.org
apps > excel
document updated 1 year, 8 months ago, on Feb 16, 2023

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)

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:

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]

Appendix A: Pieces of the puzzle.