Embed password in VBA for ODBC connection

mojof1

New Member
Joined
Jan 29, 2009
Messages
39
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.

Code:
   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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Is it:-
Code:
"Jet OLEDB:Database Password=kchu_6789;"
(Remove the single quotes.)
 
Upvote 0
Is it:-
Code:
"Jet OLEDB:Database Password=kchu_6789;"
(Remove the single quotes.)

hello
thanks for the reply

accordingly, this is what i did:

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

but with this, I get the 'Could not find the installable ISAM' error
 
Upvote 0
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.
 
Upvote 0
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!

Code:
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
 
Upvote 0
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;"
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top