DSN-less pass-through queries with Oracle driver

Bruce Hensley

In Access 97, I have successfully created a DSN-less pass-thru query
to Oracle using the MS ODBC For Oracle driver. I used a connection
string like this:

ODBC;Driver={Microsoft ODBC For Oracle}; Server=our_tnsname;
UID=my_userid; PWD=my_password

However, our DBAs are concerned about this driver and have insisted we
use the Oracle ODBC Driver [from Oracle] instead.

I've tried a number of formats for the connection string, none of
which work. Here is an example:

ODBC;Driver={Oracle ODBC Driver}; dbq=our_tnsname; UID=my_userid;
PWD=my_password

The error I get from this is:

Reserved error (-7778); there is no message for this error.

Is there a way to make this work? If so, how?

Thanks,
Bruce

Nov 13 '05 #1

Rich P

Well, just know that the Oracle driver is more reliable than the
Microsoft Oracle ODBC driver. Once that is installed and configured
(use Net8 or 9 configuration assistant and Net8 or 9 assistant that
comes with the installation of the Oracle driver) you will have a
service upon which you will create your ODBC dsn. The best way to
access the Oracle ODBC dsn is through ADO. So make sure you also have
Mdac2.5 (which contains Jet) and Mdac2.6 loaded on the computer. Then,
in Access, you can link to the Oracle table you need (you need to do
this so that you can look at the connection string to see what the table
is named - ususally some Oracle type prefix), make a reference to
Mdac2.6 in tools/References. Then here is the code for using ADO with
Oracle from Access:

Dim conn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Dim strSql As String

conn.ConnectionString = "Data Source=ccc" & _
";User ID=Steve;Password=Tiger"
conn.Open
Rst.CursorLocation = adUseClient
Rst.Open "SELECT * FROM CCC2.TRANSFER", conn
Do While Not Rst.EOF
For i = 0 to Rst.Fields.Count - 1
Debug.Print Rst(i) & ", ";
Next
Debug.Print
Rst.MoveNext
Loop
Rst.Close
conn.Close
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2