Is there a way to query from an Oracle SQL database directly using VBA-CodePudding

Is there a way to query from an Oracle SQL database directly using VBA without utilizing a power query connection. We currently do this with a oracle to excel setup and every pc has to have the setup done but its becoming a real hassle setting that up on every pc in our workplace and are trying to find a better solution for our excel reports that need to query the oracle database. Any help or suggestions would be greatly appreciate. Thanks in advance!

CodePudding user response：

It sounds as though you are trying to skip the fairly onerous process of installing the Oracle client on each machine. For a machine to talk directly to an Oracle server, it must have the Oracle client installed. Once that is installed, you can query it directly from VBA.

Another approach would be to set up a machine that is configured to access Oracle that is running a web server. That machine could take requests from the local network specifying the connection credentials and then relay those requests to the oracle server. That way, your individual machines do not need to be configured to hit Oracle directly. This is how we do it where I work.

The reason that your tools that use SQL Server don't need any direct configuration is because the SQL Server client comes pre-installed on Windows.

Here's how we make the request from VBA to the server that actually makes the query to the oracle database

Sub remote_data_query()

    Const query = "select * from redcat.customer"
    Dim formdata As String
    
    formdata = "sql=" & URLEncode("select * from redcat.customer")
    formdata = formdata & "&cs=Oracle"
    formdata = formdata & "&un=redcat_user"
    formdata = formdata & "&pw=" & URLEncode("Pa55w0rd")
    formdata = formdata & "&m=2"
    formdata = formdata & "&style=ascii2"
    
    Dim xmlhttp As Object
    Dim myurl As String
    Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")
    myurl = "https://dbrelayserver.websql.org/sql.aspx"
    xmlhttp.Open "POST", myurl, False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xmlhttp.Send formdata
    
    Debug.Print xmlhttp.responseText

End Sub

The response comes back as a formatted string which we parse and write into a worksheet.