Invalid connection string from Access to Oracle

Hello everyone,

I have a Sub in Access, which I want it to be a DSN-less connection, and execute a pass-through query. But gives me an error: Invalid Connection String in pass-through query.

I'd appreciate any comments or suggestions

Regards
Sub passthrough_query_test()
    Dim dbs As Database
    Dim qdfPassThrough As QueryDef
    Dim qdfTemp As QueryDef
 
    Set dbs = CurrentDb()
 
    Set qdfPassThrough = dbs.CreateQueryDef("consultaOrden")
 
    qdfPassThrough.Connect = ("Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=stsag;Pwd=prod666;")
    
    qdfPassThrough.SQL = "SELECT num_orden from AG_ORDENES where num_orden=1092385"
 
    qdfPassThrough.ReturnsRecords = True
 
    With dbs
    Set qdfTemp = .CreateQueryDef("tablaTmp", "SELECT * from consultaOrden")
    DoCmd.OpenQuery "tablaTmp"
    .QueryDefs.Delete "tablaTmp"
    End With
 
    dbs.QueryDefs.Delete "consultaOrden"
    dbs.Close
 
    End Sub

Open in new window

Ivette_DiazAsked:
Who is Participating?
 
jmoss111Commented:
The problem is most likely that the ODBC settings expect Oracle 8.0 to be used by default. So change the registry to look for Oracle 9 DLLs and DSN creation succeeds.

Need to change 2 registry entries, see HKLMSoftwareMicrosoftMSDTCMTxOCI
and change OracleXaLib from oraclient8.dll to oraclient9.dll and change OracleSqlLib from orasql8.dll to orasql9.dll (note: if OracleOciLib is oci.dll do not change it).

(As a reference see outdated Microsoft article Q264012).

0
 
jmoss111Commented:
Check out www.connectionstrings.com to validate your connect string.
0
 
Ivette_DiazAuthor Commented:
Thank you, now it's valid but gives me the folowwing error:

"Driver's SQLSetConnectAttr failed"

I changed my connection string to:

oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
           "Dbq=REPOGARA" & _
           "Uid=pepe;" & _
           "Pwd=anyone"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.