1

I am trying to create a Frontend for a database tool, in Excel because a) that's what is available to all prospective users in the company, and b) resulting data would otherwise be put into Excel anyway. We are doing this to sunset an existing Excel tool, that connected to a MS SQL Server, but had the logic built in VBA - hard to maintain, and the existing DB wont be available to us much longer. With the new system, all logic will be done server-side, and I only need to show the results.

I was able to do that with the MS SQL Server. But the new server is an Oracle DB, and the same tricks do not work there. (Btw, Oracle client 11.2 is installed on every machine.)

How can I do the following?

  1. Connect to and read from Oracle DB from Excel
  2. Have the DB-Connection saved in the Excel file
  3. Password is saved within the Excel file (I know the counter-arguments, but in this case it's fine)
  4. Use user-entered values as query parameters

What I already tried / What is not possible:

  • Power-Query lets me connect to Oracle, and allows for parameters. It is difficult for newcomers, and has more overhead for weak laptops, but it works. Unfortunately, Power-Query, by design, does not let you save the connection password.
  • ODBC-Connections for whatever reason do not seem to work with MS Query (Although the "Test connection" says OK). I need MS Query to utilize parameters.
  • OLEDB-connetions do work, but they do not offer parameters.

The only workaround I found to be working, is to utilize OLEDB connection to oracle, read the parameters from the user and change the query string via VBA each time to include them:

' Update table from oracle via OLEDBConnection
query = "SELECT *" & _
    " FROM SAMPLETABLE sa " & _
    " WHERE sa.KEY_1='$KEY2' " & _
    " AND sa.KEY_2='$KEY2' " & _
    " ORDER BY sa.ID"                            ' prepare the query
query = Replace(query, "$KEY1", key1)            ' Replace placeholder with value for key_1
query = Replace(query, "$KEY2", key2)            ' Replace placeholder with value for key_2
With Sheets("OracleLines").ListObjects(1).QueryTable.WorkbookConnection.OLEDBConnection
  .CommandText = query                           ' Set the query text in the OLEDBConnection
  .Refresh                                       ' Refresh the connection
End With

I'd rather not go this route, as a) I was trying to eliminate VBA entirely, b) it adds another possible point of failure, c) the SQL query would be visible. (I do not expect malicious tinkering, but I'd like to be safe).

Franz Reischl
  • 190
  • 8
  • Using parameter and avoiding VBA could be difficult. Btw, you should use [OleDbCommand.Parameters](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-5.0) – Wernfried Domscheit Jan 22 '21 at 18:24
  • This would be .NET - I only have access to VBA as a tool at my disposal. VBA only offers the OLEDBConnection object. – Franz Reischl Jan 22 '21 at 21:32
  • Yes, I was too lazy to search for it. However the Syntax is almost the same in VBA, it works in the same way. See https://stackoverflow.com/a/10353908/3027266 – Wernfried Domscheit Jan 23 '21 at 06:56

0 Answers0