DSN-less switch from SQL Server to Oracle

G

Guest

I currently have an .mdb that links to a SQL Server database. That database
is being moved to Oracle and I would like to keep the DSN-less connection to
the Oracle Db.

I have found the connection string from
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForOracleFromOracle that sounds like it should work:

Driver={Oracle ODBC Driver};Dbq=myDBName;Uid=myUsername;Pwd=myPassword

I am using the Oracle ODBC Driver and am able to successfully create a DSN &
test the connection (so the tnsnames.ora appears to be correct).
Unfortunately when I run my code to set the tabledef.connect property using
the above syntax, I receive an ODBC--call failed error.

Any ideas on what might cause this?

Thank you in advance for any help!
I have tried different
 
G

Guest

Thank you for your response!

I actually do have ODBC; first and am getting the ODBC error.
 
G

Guest

When you receive the ODBC call error, you have to iterate
through the DAO errors collection to see what was the cause
of the call error.

(If you are using ADO, not DAO, you have the wrong connection
string)

dim obj
For Each obj In application.DBEngine.errors
msgbox obj.Description
next obj

(david)
 
G

Guest

Okay, I added that code and now when I refresh the tabledef after setting the
tabledef.connect = "ODBC;Driver={Oracle ODBC
Driver};Dbq=[myservicename];Uid=[myuid];Pwd=[mypwd]"

I receive the following:
1. The Oracle ODBC Driver Connect window pops up with the pre-populated
values
2. I click on okay.
3. [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve service name

Would this have anything to do with having multiple versions of the Oracle
ODBC drivers on the same machine?

I am still able to successfully test the connection using a DSN.

Thank you again for your help.
 
D

Douglas J. Steele

Is the DBQ name ([myservicename]) defined in the tnsnames.ora file, like
Carl points out is required?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MC said:
Okay, I added that code and now when I refresh the tabledef after setting
the
tabledef.connect = "ODBC;Driver={Oracle ODBC
Driver};Dbq=[myservicename];Uid=[myuid];Pwd=[mypwd]"

I receive the following:
1. The Oracle ODBC Driver Connect window pops up with the pre-populated
values
2. I click on okay.
3. [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve service name

Would this have anything to do with having multiple versions of the Oracle
ODBC drivers on the same machine?

I am still able to successfully test the connection using a DSN.

Thank you again for your help.

david@epsomdotcomdotau said:
When you receive the ODBC call error, you have to iterate
through the DAO errors collection to see what was the cause
of the call error.

(If you are using ADO, not DAO, you have the wrong connection
string)

dim obj
For Each obj In application.DBEngine.errors
msgbox obj.Description
next obj

(david)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top