Oracle Connection String

[Lloyd]

I have a query I want to run against our oracle database. I have written a
connection string using some examples I found on the internet and it appears
that it connects successfully, but when I go to run the query, I still get
prompted to enter a password to connect to Oracle. Below is the code if
anyone has a suggestion on what I'm doing wrong.

thanks

Dim Cn As ADODB.Connection
Dim CP As ADODB.Command
Dim rs As ADODB.Recordset
Dim conn As String
Dim QSQL As String

'Connect to Oracele server begin
conn = "DRIVER={Oracle in
OraClient10g_home};SERVER=ServerINfo;UID=Username;PWD=Password;DBQ=LB75_LIVE_RMSDATA;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;"

Set Cn = New ADODB.Connection

With Cn
.ConnectionString = conn
.CursorLocation = adUseClient
.Open
End With

If Cn.State = adStateOpen Then
MsgBox "Connection successful."
DoCmd.OpenQuery "qryName"
End If

'Connect to Oracle server end

'close connection begin
Cn.Close
Set Cn = Nothing
Set CP = Nothing
'close connection end

 

[Clifford Bass via AccessMonster.com]

Hi Lloyd,

You could provide the user name and password on the .Open line.

conn = "DRIVER={Oracle in OraClient10g_home};SERVER=ServerINfo;" & _
"DBQ=LB75_LIVE_RMSDATA;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;" & _
"FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O"

With Cn
.CursorLocation = adUseClient
.Open conn, "username", "password"
End With

However, this may not help since you are associating the user name and
password to a connection that is separate from those that Access maintains.
Are you using a linked table? That would be easiest as it will automatically
prompt for the user name and password. Or is this a pass-through query and
it has to be a pass-through query? If so, what you might do is create a
linked table to one of the tables in the Oracle database. Maybe use DUAL.
Next you will need to set the ODBC Connect Str property in the pass-through
query. Open up the newly linked table in design view and display the
properties sheet. Copy the contents of the Description property, which
should start with "ODBC:". Leave the properties sheet showing. Close the
table and open up the pass-through query in design view. Paste what you just
copied into the ODBC Connect Str property and remove any "TABLE=...." portion
of the string. Close and save. Close out of Access and go back in to clear
out the caching of all ODBC connections. Now open the query. It should now
prompt for the user name and password.

Hope that helps,

Clifford Bass