Embed password in VBA for ODBC connection

[mojof1]

My code executes a query from an oracle db. Everytime I try to run it, it asks for the ODBC password. I was wondering if it's possible to embed the password in the code so it doesn't ask for it. Below is my attempt from piecing together what I found through googling, but does not seem to work. any help would be appreciated, thanks.

   Dim SQLStr As String
   Dim connection As ADODB.connection
   Dim rst As ADODB.Recordset
   Dim intRow As Integer

   SQLStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Range("DBPath") & ";Persist Security Info=False;" & _
                "Jet OLEDB:Database Password='kchu_6789';"
   Set connection = New ADODB.connection
   connection.Open SQLStr
   Set rst = New ADODB.Recordset

 

[sous2817]

Nevermind...I'm an idiot.

 

[scalaria]

Hi, would you mind sharing with us how you included your credentials?

 

[Ruddles]

Is it:-

"Jet OLEDB:Database Password=kchu_6789;"

(Remove the single quotes.)

 

[scalaria]

Thanks! I'll try that.

 

[mojof1]

Is it:-

"Jet OLEDB:Database Password=kchu_6789;"

(Remove the single quotes.)

hello
thanks for the reply

accordingly, this is what i did:

SQLStr = "Provider=Microsoft.Jet.OLEDB.4.0;Database Password = kchu_6789"

but with this, I get the 'Could not find the installable ISAM' error

 

[Ruddles]

Try Provider=Microsoft.Ace.OLEDB.12.0 for 2007 and get rid of those spaces around the = symbol.

If that doesn't work, let us know what version of Office you're using and publish the code you're using in its entirety.

 

[mojof1]

Try Provider=Microsoft.Ace.OLEDB.12.0 for 2007 and get rid of those spaces around the = symbol.

If that doesn't work, let us know what version of Office you're using and publish the code you're using in its entirety.

hello, I am using Microsoft office 2003 SP3

I tried taking out the spaces, and also using OLEDB 12 but it still did not solve my problem. below is the entire code. thanks for the help!

Public Sub gef_query()

    Dim connectionString As String
    Dim connection As ADODB.connection
    Dim rst As ADODB.Recordset
    Dim introw As Integer
    
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=kchu_6789;Data Source=" & Range("DBPath") & ";" & _
        "Persist Security Info=False;"
    Set connection = New ADODB.connection
    connection.Open connectionString
    Set rst = New ADODB.Recordset

    'switch to set cumulative data from selected date to current date
    If (Range("date_switch") = "True") Then
        'selected date to current date
        eq = ">="
    Else
        'selected date only
        eq = "="
    End If

    introw = 4
    With rst
        .ActiveConnection = connection
        
        'SQL
        connectionString = "SELECT GCX.run_date AS [Run Date], ISS.issuer_name AS [Issuer Name], " & _
        "ISS.country_res AS [Country of Domicile], PDT.product_type AS [Product Type], " & _
        "IIF(GEN.description Is Null,Null,GEN.description) AS [Product Class], SUP.share_os AS [Shares OS], " & _
        "sum(IIF(GCX.transit In ('95075'),GCX.posn_unit,Null)) AS SBE, " & _
        "sum(IIf(GCX.TRANSIT In ('38885'),GCX.POSN_UNIT,Null)) AS SIL, " & _
        "sum(IIf(GCX.TRANSIT In ('00375'),GCX.POSN_UNIT,Null)) AS BNSL, " & _
        "sum(IIf(GCX.TRANSIT In ('01285','21485'),GCX.POSN_UNIT,Null)) AS SCEL, " & _
        "sum(IIf(GCX.TRANSIT Not In ('95075','38885','00375','01285','21485'),GCX.POSN_UNIT,Null)) AS Elsewhere " & _
        "FROM (((((linked_tickers AS TIC INNER JOIN sag_issuer AS ISS ON TIC.F1=ISS.BLOOMBERG_TICKER_ID) " & _
        "INNER JOIN sag_master_security_desc AS MSD ON ISS.ISSUER_ID=MSD.ISSUER_ID) INNER JOIN " & _
        "sag_gc_extract_data AS GCX ON MSD.MSD_ID=GCX.MSD_ID) INNER JOIN sag_product AS PDT " & _
        "ON MSD.PRODUCT_ID=PDT.PRODUCT_ID) INNER JOIN sag_msd_supp AS SUP ON MSD.MSD_ID=SUP.MSD_ID) " & _
        "LEFT JOIN sag_generic_table AS GEN ON SUP.CLASS_ID=GEN.PRODUCT_ID " & _
        "WHERE ((GCX.RUN_DATE)" & eq & "#" & Range("RunDate") & "#) And ((GEN.STATUS)='A') And ((GEN.TABLE_ID)='share_class') And " & _
        "((PDT.PRODUCT_ID) In (1) And ((SUP.share_os) Is Not Null)) " & _
        "GROUP BY GCX.run_date, ISS.issuer_name, ISS.country_res, PDT.product_type, GEN.description, SUP.share_os;"
       
       rst.Source = connectionString
       
       'open record and write to excel sheet
       .Open
       Do Until .EOF
           introw = introw + 1
            Sheet3.Cells(introw, 1) = .Fields("Run Date")
            Sheet3.Cells(introw, 2) = .Fields("Issuer Name")
            Sheet3.Cells(introw, 3) = .Fields("Country of Domicile")
            Sheet3.Cells(introw, 4) = .Fields("Product Type")
            Sheet3.Cells(introw, 5) = .Fields("Product Class")
            Sheet3.Cells(introw, 6) = .Fields("Shares OS")
            Sheet3.Cells(introw, 7) = .Fields("SBE")
            Sheet3.Cells(introw, 8) = .Fields("SIL")
            Sheet3.Cells(introw, 9) = .Fields("BNSL")
            Sheet3.Cells(introw, 10) = .Fields("SCEL")
            Sheet3.Cells(introw, 11) = .Fields("Elsewhere")
           .MoveNext
       Loop
       .Close
   End With
   connection.Close
   
   'go to summary sheet
   Sheets("GEF Holdings Summary").Select
   
End Sub

Public Function string_transit(ByVal busline As String)

    string_transit = WorksheetFunction.VLookup(busline, Range("Transit"), 1, False)
    
    'start of transit grid
    s_row = Range("head_transit").Row + 1
    
    'loop while Business Line column is not blank
    Do While s_row <> ""
        'loop while Transit Number for each Business Line is not blank
        Do While string_transit = busline
            
        
        Loop
        
    Loop
    
End Function

 

[RichardMGreen]

I'm doing something similar with ADODB connections and I found this works:-
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & ";Jet OLEDB:Database Password=" & db_pass & ";"

strConn would be equivalent to your connectionString. In my case, db_pass is given by a userform entry and could just be typed in between the = and the ; without any quotes.

So your line of code would be:-
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Range("DBPath") & ";Jet OLEDB:Database Password=kchu_6789;"