Pass arguments to ODBC query in VBA or formula

While it could certainly be done with VBA, you could also export the data to Excel and then use a Vlookup to populate column B based on the data in Column A

If you need help setting it up, send me the files, referencing this thread in the covering email.

There is a factor I did not mention that caused me to exclude VLookup before I posted: there are roughly 400 fields and a million records in the source DB2 table. So the only way to make a VLookup useful would be to either:

1. Include all million records each time we want to run this process. I am quite certain the latency would kill the process.

2. Filter to the required records (based on the user's list of required primary key data) when retrieving the data in the first place. But if we do that, we would also have the query pull the additional fields, and there would be no reason for a VLookup.

Take a look at https://support.microsoft.com/en-us/office/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e

The information in the link you provide just tells how to create an MSQuery, and to quote myself from my original post: "I have built ODBC queries against DB2 databases in MSQuery (Office 2019 & 365) that call for one or more parameters to be passed to the WHERE clause of the ODBC query, but now I want to do something a bit different"

Base MSQuery functionality simply runs the provided SQL SELECT statement (including any WHERE clause elements that filter the data) and returns the data as one record per row, one field per column. But that assumes that all the filters for the WHERE clause are provided in the query itself, and it returns all data at once.

That is not what I need. I need to run the query once separately for each row to pull data for the adjacent cells--as the filter for the query is provided for that row. For example:

1. User enters invoice number 12345 in cell A1

2. When the user presses Tab, cells B1 & C1 auto-populate with the customer name and invoice amount for invoice #12345 looked up directly from within the database using a query like this: SELECT CUSTOMER_NAME, INVOICE_AMOUNT FROM INVOICE WHERE INVOICE_NUMBER = 12345. CUSTOMER_NAME goes to B1, INVOICE_AMOUNT goes to C1.
3. User enters invoice number 98765 in blank cell A2
4. Cells B2 & C2 are automatically populated with the customer name and invoice amount for invoice #98765 by running this query: SELECT CUSTOMER_NAME, INVOICE_AMOUNT FROM INVOICE WHERE INVOICE_NUMBER = [B1]

The general case is this: the user enters any invoice number in a cell in the first row, and the code at that moment runs the query, passing the value of that first cell into the query, where it is used in the WHERE clause to retrieve a single record from the database and passes the results to the adjacent cells.

And that was all a greatly over-simplified example. In reality, the underlying data will involve fields from several tables joined in the SQL, not something so simplistic as an invoice list, and we will populate not just 2, but 10 or 12 cells per row.

My question, therefore, is how, if even possible, would one use MSQuery repetitively like this to pull data for one row at a time, using a value entered in one cell on the row as part of the WHERE clause in the query to retrieve the data for the rest of the row. And that is why I asked about how to do it in VBA. I suspect this requires declaring and opening a data source, running the query against that data source to pull the values into variables, then explicitly setting the value of each resultant cell to the value of the variable. That is how I would do it in Access, but I have never done that in Excel and need to know how to do that in Excel--or of there is a simpler way not requiring VBA.