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

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};;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"
    End Sub

Open in new window

Who is Participating?
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).

Check out to validate your connect string.
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;" & _
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.