?
Solved

Invalid connection string from Access to Oracle

Posted on 2008-11-05
3
Medium Priority
?
355 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:Ivette_Diaz
  • 2
3 Comments
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 750 total points
ID: 22887922
Check out www.connectionstrings.com to validate your connect string.
0
 

Author Comment

by:Ivette_Diaz
ID: 22888798
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
 
LVL 18

Accepted Solution

by:
jmoss111 earned 750 total points
ID: 22891340
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question