Best Approach - Writing Records to Oracle from Excel with VBA w/ Multiple End Users

Question

I have develop an application in Excel w/ VBA that has a user form that collects data from the end user and inserts that data into a table in Oracle 11g. I have to distribute this file to 10 - 15 users. I am currently connecting with my personal credentials via the code, and everything works as designed.

What kind of configuration do I need to set up so I can remove my Username and Password from the connection string and have all the end users still be able to connect? All of them connect to this data source already on a regular basis. So, they have the Oracle Client installed and have tnsnames.ora files, etc. I just don't know how to productionalize the app so it can be distributed? The working code is as follows:

    Public Sub SubmitSupplytoOracle(sYear As Integer, sWeek As Integer, sArea As String, sCrew As String, sSuper As String, _
                                    sTECOH As Integer, sTECUG As Integer, sContOH As Integer, sContUG As Integer)

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sqlInsert As String
    Dim sCurrUser As String
    Dim sConnString As String
    Dim recordsAffected As Long
    'Dim wsTest As Worksheet
    'Dim sqlSelect As String

    'Set wsTest = ActiveWorkbook.Worksheets("Test") 'have to create a new worksheet called 'Test' to use this
    sCurrUser = CreateObject("WScript.Network").UserName
    sConnString = "User ID=xxxxxx;Password=yyyyyy;Data Source=ZZZZ;Provider=OraOLEDB.Oracle"


    sqlInsert = "INSERT INTO POTW_MH_Supply (" & _
                    "MHSupplyID," & _
                    "SupplyYear," & _
                    "ISO_Week," & _
                    "Service_Area," & _
                    "CrewHQ," & _
                    "Supervisor_Last_Name," & _
                    "TEC_OH_MHs," & _
                    "TEC_UG_MHs," & _
                    "Cont_OH_MHs," & _
                    "Cont_UG_MHs," & _
                    "Insert_UserName)" & _
                    " VALUES(" & _
                        "seq_POTWMHSupply.NEXTVAL," & _
                        sYear & "," & _
                        sWeek & "," & _
                        "'" & sArea & "'," & _
                        "'" & sCrew & "'," & _
                        "'" & sSuper & "'," & _
                        sTECOH & "," & _
                        sTECUG & "," & _
                        sContOH & "," & _
                        sContUG & "," & _
                        "'" & sCurrUser & "'" & _
                        ")"


    'sqlSelect = "SELECT * FROM STJOF.POTW_MH_Supply"


    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cn.Open sConnString
    cn.CommandTimeout = 60

    cn.BeginTrans
    cn.Execute sqlInsert, recordsAffected
    cn.CommitTrans

    Debug.Print recordsAffected

    If recordsAffected = 1 Then
        MsgBox "Your submittal of Supply Man Hours was successful"
    Else
        MsgBox "Your submittal might not have been recorded succesfully for some reason. Please try again"
    End If


    'Might use this later if I need to return some values from this table.
    'rs.Open sqlSelect, cn
    'wsTest.Range("B3").CopyFromRecordset rs


    cn.Close

    'Cleanup objects
    Set cn = Nothing
    Set rs = Nothing
    sConnString = ""

    End Sub

Answer 1

Consider having each user save a YAML file to a designated, secured location that holds their database credentials and have Excel parse the configuration file into a collection of key/value pairs to complete connection string.

YAML

database_connection:
 provider : OraOLEDB.Oracle
 database : ZZZZ
 user     : xxxxxx
 password : yyyyyy

VBA

YAML Parser Function

Function ParseYAML(strFile) As Collection
    Dim myFile As String, textline As String, oneline As String
    Dim dataArray As Variant, data As Variant, key As Variant
    Dim dbColl As Collection
    Dim line As Integer, sizeArray As Integer

    Set dbColl = New Collection

    ' OPEN YAML FILE
    Open strFile For Input As #1

    ' LOOP THROUGH LINES
    Do Until EOF(1)
        Line Input #1, textline

        oneline = Replace(textline, " ", "")
        dataArray = Split(oneline, ":", 2)
        sizeArray = UBound(dataArray, 1) - LBound(dataArray, 1) + 1

        ' POPULATE KEY/VALUE PAIR
        If Not textline = "" And Not sizeArray = 0 Then
            data = dataArray(1)
            key = dataArray(0)

            If Right(key, 1) <> ":" Then
                dbColl.Add data, key
            End If
        End If
    Loop        
    Close #1

    Set ParseYAML = dbColl
End Function

Current Macro

Public Sub SubmitSupplytoOracle(...)
    Dim dbCreds As Collection
    ...

    Set dbCreds = ParseYAML("C:\Path\To\OracleConnectionDetails.yml")

    sConnString = "Provider=" & dbCreds("provider") & _
                  ";Data Source=" & dbCreds("database") & _
                  ";User ID=" & dbCreds("user") & _
                  ";Password=" & dbCreds("password") & ";"

    ...

End Sub