DSN-Less Oracle... Missing something...

[lameid]

It's been about a year since I had to use a SQL DSN-less connection but I think I remember everything.

My recollection is set up Connection strings with Server, database and user and then connect to the database in code and finally use code to open connection with password and then Access will cache it for future connections...

Now I am trying the same with Oracle instead of SQL and I am having issues... the expected ODBC connect string errors. Comments in code. Worth noting is that the code as written executes prints the date/time as expected but does not lead to a cached password as I am assuming it is ODBC...

Interesting thing here is that the connection string with password is too long too long to save otherwise I would just embed it. Using Access 2007 with Access 2003 MDB.

Any help/insights with either problem would be awesome.

Sub DSNLessOracle()
    Dim cnn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim cmd As New ADODB.Command

    'cnn.ConnectionString = "ODBC;DRIVER={Oracle in OraClient11g_home1};SERVER=<Server>;DBQ=<Server>;UID=<User>;Pwd=<Password>;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;;"
    'Including "ODBC"  causes error opening recordset... "[Microsoft][ODBC Driver Manager] Datasource name not found and no default driver specified"
    cnn.ConnectionString = "DRIVER={Oracle in OraClient11g_home1};SERVER=<Server>;DBQ=<Server>;UID=<User>;Pwd=<Password>;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;;"
    cnn.Open
    cmd.ActiveConnection = cnn
    cmd.CommandText = "Select Sysdate as ""Now"" From Dual;" 'Get the date from Oracle... like T-SQL Select Getdate() or similar to ACE-SQL Select Top1 Now() From <AnyTableWithAtLeast1Record>
    Set RS = cmd.Execute
    
    If Not RS.EOF Then
        Debug.Print RS!Now
    End If
    
    RS.Close
    cnn.Close
    cnn.ConnectionString = ""
    

End Sub

 

[jsteph]

Not sure if this will help w/odbc, but with oledb anyway, I usually see the entire TNSNames.ora block for that dbname existing in-line in the connection string, followed by the rest of the stuff (user/pwd,driver, etc)
--Jim

 

[lameid]

Things finally slowed down enough for me to do some serious looking around. By reviewing the connection string options, I was able to eliminate enough things to have room for the password in my saved connection string which worked for my case. I never did figure out how to use a cached DSN-less password with Oracle and I suspect this is not possible.